user1852837 user1852837 - 5 months ago 16
SQL Question

Get record achieving the quota using SQL

Having the record below with Achieving Quota is 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


How to get the record per agent where their sale is achieved the certain quota which is 30 order by SaleDate Ascending. Ideal Results must be like this:

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


Final Results

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

Unfortunately Sql Server 2008 does not support Sum() Over(order by) window function to calculate running total.

Use Correlated sub-query method to calculate running total

Try this

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 

or 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 

For Sql Server 2012+ use this

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