How to write Multiple Sum amount query in select statement using SQL for fast performance

Question

I have writen a query below which is working fine for my report

declare @orgId int=3,
@Year int=2022

;with cte as (
select  t.requestNo,year(timeStamp) dispensedInYear from txnhistory(NOLOCK) t  where t.categoryNo=411 and t.statusNO=76 and  t.companyId=@orgId and 
   (@Year=0 or (year(t.Timestamp)=@Year ))
 )

 select distinct p.personid as personId,p.firstName+' '+p.lastname Fitter,
 
 (SELECT  ISNULL( ROUND(SUM(amountPaid),3),0) PaidAmount FROM commissionPaid where commissionType='fitter' and personid=cp.personid and statusNo=452 and (@Year=0 or (year(dispensedSignDate)=@Year ))) as total,

(select ISNULL( ROUND(SUM(plt.allowedPrice),3),0) from commissionPaid cp1
 inner join ProfitOrLossTable plt on cp1.doNumber=plt.doid where 
cp1.personId=cp.personId)BillableAmount,

  from  cte as r  
 join  productrequestall pr  on r.requestNo=pr.requestId --and cp.paymentType='Sales Commission'
 join commissionPaid cp on cp.doNumber=pr.doId and cp.commissionType='fitter' and cp.statusNo=452
 join commissionratepaid crp on crp.doNumber=cp.doNumber and crp.commissionType=cp.commissionType
 join employeecheck ec on ec.employeeCheckId=cp.checkNumber
  join person p on p.personId=cp.personId
 join fitterCommissionRates fr(NOLOCK) on fr.fitterId=cp.personId and fr.organizationId=@orgId
 

But is this correct way to write in below line in query because I have other same 3 sum amount counts

(SELECT  ISNULL( ROUND(SUM(amountPaid),3),0) PaidAmount FROM commissionPaid where commissionType='fitter' and personid=cp.personid and statusNo=452 and (@Year=0 or (year(dispensedSignDate)=@Year ))) as total

or other way to write above line using left join or sub query to fast report performance and avoid to query timeout issue.

I have modified your query then it is working.

declare
    @orgId int = 3,
    @Year int = 2021;

select
        p.personid,
        max( p.firstName +' '+ p.lastname ) Fitter,
        coalesce( sum( cp.PaidAmount ), 0 ) PaidAmount
           from  
        txnhistory r
            join productrequestall pr  
                on r.requestNo = pr.requestId
                -- and cp.paymentType='Sales Commission'
                join
                (
                    SELECT
                            personid,
                            doNumber,
                            ROUND( SUM( amountPaid ), 3) PaidAmount
                        FROM
                            commissionPaid
                        where
                                commissionType = 'fitter'
                            and statusNo = 452
                            and @Year in ( 0, year(dispensedSignDate))
                        group by
                            personId,
                            doNumber,commissionType ) cp
                    on pr.doId = cp.doNumber
                    AND pr.doId = cp.doNumber
                    join commissionratepaid crp
                        on cp.doNumber = crp.doNumber
                        and crp.commissionType='fitter'
                    join person p
                        on cp.personId = p.personId
                    join fitterCommissionRates fr
                        on cp.personId = fr.fitterId
                        and fr.organizationId = @orgId
    where
            r.categoryNo = 411
        and r.statusNO = 76
        and r.companyId = @orgId
        and @Year in ( 0, year(r.Timestamp))
    group by
        p.personid

Answer 1

It APPEARS you are trying to get total commissions (and/or other amounts you are trying to aggregate) based on each given person (fitter). You want to see if there is a way to optimize vs writing 3 separate queries for the respective 3 columns you want aggregated, but all appear to be coming from the same commission paid table (which makes sense). By doing repeated conditional querying per column can be very resource intense, especially with larger data.

What you should probably do is a pre-aggregate of the commission based on the given person AND item based on its qualified criteria. This is done once for the whole query, THEN apply a join based on both parts matching the preceeding ProductRequestALl table content.

The outer query can apply a SUM() of those pre-queried amounts as the outer total is per single person, not based on the person and each "DoID" entry they had in their underlying commission records.

For the outermost query, since I am doing a group by person, but you want to see the name, I am applying a MAX() of the person's name. Since that will never change based on the ID, ID = 1 for "Steve" will always be "Steve", so applying a MAX() eliminates the need of adding the name portion to the GROUP BY clause.

Something like below, and obviously, I dont know your other columns you want aggregated, but think you can get the concept of it.

declare 
    @orgId int = 3,
    @Year int = 2022;
    
select 
        p.personid,
        max( p.firstName +' '+ p.lastname ) Fitter, 
        coalesce( sum( cp.PaidAmount ), 0 ) PaidAmount,
        coalesce( sum( cp.SecondAmount ), 0 ) SecondAmount,
        coalesce( sum( cp.ThirdAmount ), 0 ) ThirdAmount
    from  
        txnhistory r
            join productrequestall pr  
                on r.requestNo = pr.requestId 
                -- and cp.paymentType='Sales Commission'
                join 
                (
                    SELECT
                            personid,
                            doNumber,
                            ROUND( SUM( amountPaid ), 3) PaidAmount,
                            ROUND( SUM( OtherAmount2 ), 3) SecondAmount,
                            ROUND( SUM( ThirdAmoundColumn ), 3) ThirdAmount
                        FROM 
                            commissionPaid 
                        where 
                                commissionType = 'fitter' 
                            and statusNo = 452 
                            and @Year in ( 0, year(dispensedSignDate)) 
                        group by
                            personId,
                            doNumber ) cp 
                    on pr.personid = cp.personid 
                    AND pr.doId = cp.doNumber
                    join commissionratepaid crp 
                        on cp.doNumber = crp.doNumber 
                        and cp.commissionType = crp.commissionType
                    join employeecheck ec 
                        on cp.checkNumber = ec.employeeCheckId
                    join person p 
                        on cp.personId = p.personId
                    join fitterCommissionRates fr
                        on cp.personId = fr.fitterId 
                        and fr.organizationId = @orgId
    where 
            r.categoryNo = 411 
        and r.statusNO = 76 
        and r.companyId = @orgId 
        and @Year in ( 0, year(r.Timestamp))
    group by
        p.personid

Now, you also had an IFNULL() consideration, such as someone does not have any commission. If that is the case, do you still need to see that person even if they never earned a commission? If so, then a left-join MIGHT be applicable.

This query also does away with the "WITH CTE" construct.