length - Obtener registro logrando la cuota usando SQL

sql max group by

Question

Tener el registro a continuación con Achieving Quota es 30:

ContractNos Sale SaleDate Agent
1 10 01/01/16 A
2 20 01/10/16 A
3 20 01/10/16 A
4 10 01/11/16 A
5 40 01/20/16 B
6 20 01/21/16 C
7 30 01/22/16 C
8 10 01/23/16 C

Cómo obtener el registro por agente donde se logra su venta la cuota determinada que es de 30 pedidos por SaleDate Ascending. Los resultados ideales deben ser así:

ContractNos Sale SaleDate Agent
1 10 01/01/16 A -> ADD THIS
2 20 01/10/16 A -> RECORD = 30 achieved the quota
3 20 01/10/16 A
4 10 01/11/16 A
5 40 01/20/16 B -> Quota is achieved which is 30
6 20 01/21/16 C -> ADD THIS
7 30 01/22/16 C -> RECORD = 50 achieved the quota
8 10 01/23/16 C

Resultados finales

ContractNos Sale SaleDate Agent
1 10 01/01/16 A
2 20 01/10/16 A
5 40 01/20/16 B
6 20 01/21/16 C
7 30 01/22/16 C

Answer 1

Desafortunadamente Sql Server 2008 no es compatible con la función de ventana Sum() Over(order by) para calcular el total acumulado.

Utilice el Correlated sub-query method para calcular el total acumulado

Prueba esto

SELECT ContractNos,
Sale,
SaleDate,
Agent
FROM (SELECT (SELECT Sum([Sale])
FROM yourtable b
WHERE a.[Agent] = b.[Agent]
AND a.[ContractNos] >= b.[ContractNos]) run_sum,*
FROM yourtable a) c
WHERE run_sum - sale < 30 

o use Cross Apply

SELECT ContractNos,
Sale,
SaleDate,
Agent
FROM (SELECT *
FROM yourtable a
CROSS apply (SELECT Sum([Sale])
FROM yourtable b
WHERE a.[Agent] = b.[Agent]
AND a.[ContractNos] >= b.[ContractNos]) c(run_sum)) a
WHERE run_sum - sale < 30 

Para Sql Server 2012+ use esto

SELECT ContractNos,
Sale,
SaleDate,
Agent
FROM (SELECT Sum([Sale])OVER(partition BY [Agent] ORDER BY [ContractNos]) run_sum,*
FROM yourtable) a
WHERE run_sum - sale < 30 

Answer 2

Otra posible solución es calcular el total acumulado con un join:

select s1.ContractNos, s1.Sale, s1.SaleDate, s1.Agent
from sales s1
left join sales s2
on s1.ContractNos >= s2.ContractNos and s1.Agent = s2.Agent
group by s1.ContractNos, s1.Sale, s1.SaleDate, s1.Agent
having sum(s2.sale) - s1.sale < 30
order by s1.ContractNos