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