Cómo escribir una consulta de cantidad de suma múltiple en una declaración de selección usando SQL para un rendimiento rápido

He escrito una consulta a continuación que funciona bien para mi informe

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
 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
 

Pero, ¿es esta la forma correcta de escribir en la línea de abajo en la consulta porque tengo otros recuentos de la misma cantidad de 3 sumas?

(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

u otra forma de escribir la línea anterior usando la combinación izquierda o la subconsulta para acelerar el rendimiento del informe y evitar el problema del tiempo de espera de la consulta.

Solución del problema

PARECE que está tratando de obtener las comisiones totales (y/u otras cantidades que está tratando de agregar) en función de cada persona determinada (instalador). Desea ver si hay una forma de optimizar en lugar de escribir 3 consultas separadas para las 3 columnas respectivas que desea agregar, pero todas parecen provenir de la misma tabla de pago de comisiones (lo cual tiene sentido). Hacer consultas condicionales repetidas por columna puede requerir muchos recursos, especialmente con datos más grandes.

Lo que probablemente debería hacer es un preagregado de la comisión en función de la persona dada Y del artículo en función de sus criterios calificados. Esto se hace una vez para toda la consulta, LUEGO aplica una combinación basada en que ambas partes coincidan con el contenido de la tabla ProductRequestALL anterior.

La consulta externa puede aplicar una SUM() de esos montos consultados previamente, ya que el total externo es por persona individual, no se basa en la persona y cada entrada "DoID" que tenían en sus registros de comisiones subyacentes.

Para la consulta más externa, como estoy haciendo un grupo por persona, pero quieres ver el nombre, estoy aplicando un MAX() del nombre de la persona. Dado que eso nunca cambiará según el ID, ID = 1 para "Steve" siempre será "Steve", por lo que aplicar un MAX() elimina la necesidad de agregar la parte del nombre a la cláusula GROUP BY.

Algo como a continuación, y obviamente, no conozco las otras columnas que desea agregar, pero creo que puede entender el concepto.

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

Ahora, también tuvo una consideración IFNULL(), como que alguien no tiene ninguna comisión. Si ese es el caso, ¿aún necesita ver a esa persona incluso si nunca ganó una comisión? Si es así, entonces una combinación izquierda PODRÍA ser aplicable.

Esta consulta también elimina la construcción "CON CTE".