user1852837 - 1 year ago 68
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
``````

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download