SELECT DISTINCT C.BusinessArea, D.Circle, E.Region, (CASE C.BusinessArea WHEN 'North' THEN 1 WHEN 'Central' THEN 2 WHEN 'East' THEN 3 WHEN 'Karachi' THEN 4  WHEN 'South' THEN 5 END) AS BusinessAreaSeq, SetBranchGrpMap.BranchCode, SetBranch.BranchName, SetBranch.BranchIP, StuBranch.CounterOpened, B.TotalCounter, A.* FROM SetBranchGrpMap LEFT JOIN (SELECT StuService.BranchCode as StuBranchCode, TgtWtSec, TgtStSec, max(CASE WHEN TIME_TO_SEC(TIMEDIFF(NOW(), StuService.CurrTicketIssueTime)) > TgtWtSec THEN 1 END) as IsExceededCurrWt, max(CASE WHEN AvgWtSec > TgtWtSec THEN 1 END) as IsExceededAvgWt, max(CASE WHEN AvgStSec > TgtStSec THEN 1 END) as IsExceededAvgSt, MIN(StuService.CurrTicketIssueTime) as CurrTicketIssueTime, MAX(StuService.UpdateTimeStamp) as UpdateTimeStamp, MAX(StuService.LastStatusTime) as LastStatusTime, SUM(StuService.PhysicalTicketIssued + StuService.TransferTicketIssued) as TotalTicketIssued, SUM((StuService.PhysicalTicketIssued + StuService.TransferTicketIssued) - (StuService.PhysicalTicketCalled + StuService.TransferTicketCalled) - (StuService.PhysicalTicketRenegaded + StuService.TransferTicketRenegaded)) as TotalTicketWaiting, SUM(StuService.TransferTicketIssued) as TransferTicketIssued, (SUM(StuService.TotalWtSec) / NULLIF(SUM(StuService.PhysicalTicketCalled + StuService.TransferTicketCalled), 0)) as AvgWtSec, (SUM(StuService.TotalStSec) / NULLIF(SUM(StuService.PhysicalTicketServed + StuService.TransferTicketServed), 0)) as AvgStSec, (SUM(StuService.TotalWtSec + StuService.TotalStSec) / NULLIF(SUM(StuService.PhysicalTicketServed + StuService.TransferTicketServed), 0)) as AvgTtSec, (SUM(StuService.TotalWithinTgtWt) / NULLIF(SUM(StuService.PhysicalTicketCalled + StuService.TransferTicketCalled), 0) * 100) as TotalWithinTgtWtPer, (SUM(StuService.TotalWithinTgtSt) / NULLIF(SUM(StuService.PhysicalTicketServed + StuService.TransferTicketServed), 0) * 100) as TotalWithinTgtStPer, (SUM(StuService.TotalWithinTgtTt) / NULLIF(SUM(StuService.PhysicalTicketServed + StuService.TransferTicketServed), 0) * 100) as TotalWithinTgtTtPer FROM StuService left join SetService on StuService.ServiceNo = SetService.ServiceNo GROUP BY StuService.BranchCode) A  ON SetBranchGrpMap.BranchCode = A.StuBranchCode LEFT JOIN SetBranch ON SetBranchGrpMap.BranchCode = SetBranch.BranchCode LEFT JOIN StuBranch ON SetBranchGrpMap.BranchCode = StuBranch.BranchCode LEFT JOIN (SELECT BranchCode As BrCode, COUNT(CounterNo) As TotalCounter  FROM StuCounter GROUP BY StuCounter.BranchCode) as B ON SetBranchGrpMap.BranchCode = B.BrCode left join SetBranchGrp on SetBranchGrpMap.BranchGrpId = SetBranchGrp.BranchGrpId left join ( select distinct setbranch.branchcode, setbranchgrp.BranchGrpTypId, branchgrpname as BusinessArea, setbranchgrp.branchgrpid from setbranchgrpTyp left join setbranchgrp on setbranchgrpTyp.BranchGrpTypId = setbranchgrp.BranchGrpTypId left join setbranchgrpmap on setbranchgrpmap.branchgrpid = setbranchgrp.BranchGrpId left join setbranch on setbranch.branchcode = setbranchgrpmap.Branchcode left join setusergrpbranchgrp f on f.branchgrpid = SetBranchGrp.branchgrpid left join setusergrpmap g on g.usergrpid = f.usergrpid left join setuser h on h.userid = g.userid where branchgrptypname = 'Business Area' and h.staffId= 'gmsukkur') as C on SetBranch.BranchCode = C.BranchCode left join ( select distinct setbranch.branchcode, setbranchgrp.BranchGrpTypId, branchgrpname as Circle, setbranchgrp.branchgrpid from setbranchgrpTyp left join setbranchgrp on setbranchgrpTyp.BranchGrpTypId = setbranchgrp.BranchGrpTypId left join setbranchgrpmap on setbranchgrpmap.branchgrpid = setbranchgrp.BranchGrpId left join setbranch on setbranch.branchcode = setbranchgrpmap.Branchcode left join setusergrpbranchgrp f on f.branchgrpid = SetBranchGrp.branchgrpid left join setusergrpmap g on g.usergrpid = f.usergrpid left join setuser h on h.userid = g.userid where branchgrptypname = 'Circle' and h.staffId= 'gmsukkur' ) as D on SetBranch.BranchCode = D.BranchCode left join ( select distinct setbranch.branchcode, setbranchgrp.BranchGrpTypId, branchgrpname as Region, setbranchgrp.branchgrpid from setbranchgrpTyp left join setbranchgrp on setbranchgrpTyp.BranchGrpTypId = setbranchgrp.BranchGrpTypId left join setbranchgrpmap on setbranchgrpmap.branchgrpid = setbranchgrp.BranchGrpId left join setbranch on setbranch.branchcode = setbranchgrpmap.Branchcode left join setusergrpbranchgrp f on f.branchgrpid = SetBranchGrp.branchgrpid left join setusergrpmap g on g.usergrpid = f.usergrpid left join setuser h on h.userid = g.userid where branchgrptypname = 'Region' and h.staffId= 'gmsukkur' ) as E on SetBranch.BranchCode = E.BranchCode where BusinessArea is not null and Circle is not null and Region is not null order by BusinessAreaSeq, Circle, Region, BranchCode