TSQL Running total with start and end date

Question

I have two tables,

Table_A

+---------+----+------------+------------+-----------+
| SurrKey | ID | StartDate  |  EndDate   | Allotment |
+---------+----+------------+------------+-----------+
|       1 |  1 | 2015-01-01 | 2015-01-31 |      1000 |
|       2 |  1 | 2015-01-15 | 2015-02-15 |      1500 |
|       3 |  2 | 2015-01-01 | 2015-01-31 |      1200 |
|       4 |  2 | 2015-02-10 | 2015-03-10 |      1000 |
|       5 |  3 | 2015-01-01 | 2015-01-31 |      1000 |
|       6 |  3 | 2015-01-15 | 2015-02-14 |      1500 |
+---------+----+------------+------------+-----------+

Table_B

+----+------------+------+
| ID |    Date    | Used |
+----+------------+------+
|  1 | 2015-01-01 |  800 |
|  1 | 2015-01-14 |  300 |
|  1 | 2015-01-15 |  100 |
|  1 | 2015-01-18 |  200 |
|  2 | 2015-01-01 |  700 |
|  2 | 2015-01-14 |  300 |
|  2 | 2015-01-15 |  150 |
|  2 | 2015-02-05 |   90 |
|  2 | 2015-02-11 |  100 |
|  3 | 2015-01-01 |  900 |
|  3 | 2015-01-15 |  150 |
+----+------------+------+

Generate an SQL query to produce the following output.

+------------+----+------------+------+---------------+--------------------+
| Row_number | ID | Date       | Used | Running Total | RemainingAllotment |
+------------+----+------------+------+---------------+--------------------+
| 1          | 1  | 2015-01-01 | 800  | 800           | 200                |
| 2          | 1  | 2015-01-14 | 300  | 1100          | -100               |
| 3          | 1  | 2015-01-15 | 100  | 100           | 1400               |
| 4          | 1  | 2015-01-18 | 200  | 300           | 1200               |
| 5          | 2  | 2015-01-01 | 700  | 700           | 500                |
| 6          | 2  | 2015-01-14 | 300  | 1000          | 200                |
| 7          | 2  | 2015-01-15 | 150  | 1150          | 50                 |
| 8          | 2  | 2015-02-05 | 90   | 90            | -90                |
| 9          | 2  | 2015-02-11 | 100  | 100           | 900                |
| 10         | 3  | 2015-01-01 | 900  | 900           | 100                |
| 11         | 3  | 2015-01-15 | 100  | 1000          | 0                  |
| 12         | 3  | 2015-01-15 | 50   | 50            | 1450               |
+------------+----+------------+------+---------------+--------------------+

Some details for the desired output:

On Row_number 2, it is negative 100, since he used up all hist allotment
On Row_number 3, he has new allotment from the start of this date, running total should be reset on this ID
On Row_number 8, its negative 90, reset running total for this ID, since Surrkey 3 was expired, and startdate will only be in 2015-02-10
On Row_number 9, new usage for Surrkey 4 on table A

Additional requirements. I edited the sample table for this

On Row_Number 11 and 12, There are same date and same ID, since you have to use all remaining allotment before using the next one, in this case, the Surrkey 6.

Take a look at this[http://www.dotnet-tricks.com/Tutorial/sqlserver/b4I8120313-Calculate-Running-Total,-Total-of-a-Column-and-Row.html] to calculate `Running Total` — Mahesh, Mar 04 '15 at 12:43
Not that it is important, but your `Remaining Allotment` and `Running Total` for ID 2 does not make sense on 2015-01-14, because 700 + 300 = 1000, - 1200 = 200. But it is only symantecs — Jaques, Mar 04 '15 at 12:47

Giorgi Nakeuri · Accepted Answer · 2015-03-04T14:29:53.283

If you can use windowing functions then you can do like this:

DECLARE @A TABLE
    (
      SurrKey INT ,
      ID INT ,
      StartDate DATE ,
      EndDate DATE ,
      Allotment MONEY
    )
DECLARE @B TABLE
    (
      ID INT ,
      Date DATE ,
      Used MONEY
    )


INSERT  INTO @A
VALUES  ( 1, 1, '20150101', '20150131', 1000 ),
        ( 2, 1, '20150115', '20150215', 1500 ),
        ( 3, 2, '20150101', '20150131', 1200 ),
        ( 4, 2, '20150210', '20150310', 1000 )


INSERT  INTO @B
VALUES  ( 1, '20150101', 800 ),
        ( 1, '20150114', 300 ),
        ( 1, '20150115', 100 ),
        ( 1, '20150118', 200 ),
        ( 2, '20150101', 700 ),
        ( 2, '20150114', 300 ),
        ( 2, '20150115', 150 ),
        ( 2, '20150205', 90 ),
        ( 2, '20150211', 100 );
WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN ,
                        b.ID ,
                        b.Date ,
                        b.Used ,
                        SUM(b.Used) OVER ( PARTITION BY b.ID, o.SurrKey ORDER BY b.Date ) AS RTotal ,
                        -SUM(b.Used) OVER ( PARTITION BY b.ID, o.SurrKey ORDER BY b.Date ) + o.Allotment AS Remaining
               FROM     @B b
                        OUTER APPLY ( SELECT TOP 1 *
                                      FROM      @A a
                                      WHERE     a.ID = b.ID
                                                AND b.Date >= a.StartDate
                                      ORDER BY  a.StartDate DESC
                                    ) o
             )
    SELECT  * FROM    cte

Output:

RN  ID  Date        Used    RTotal   Remaining
1   1   2015-01-01  800.00  800.00   200.00
2   1   2015-01-14  300.00  1100.00  -100.00
3   1   2015-01-15  100.00  100.00   1400.00
4   1   2015-01-18  200.00  300.00   1200.00
5   2   2015-01-01  700.00  700.00   500.00
6   2   2015-01-14  300.00  1000.00  200.00
7   2   2015-01-15  150.00  1150.00  50.00
8   2   2015-02-05  90.00   1240.00  -40.00
9   2   2015-02-11  100.00  100.00   900.00

EDIT:

For SQL 2008 you can use:

WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN ,
                        b.ID ,
                        b.Date ,
                        b.Used ,
                        o.SurrKey ,
                        o.Allotment
               FROM     @B b
                        OUTER APPLY ( SELECT TOP 1 *
                                      FROM      @A a
                                      WHERE     a.ID = b.ID
                                                AND b.Date >= a.StartDate
                                      ORDER BY  a.StartDate DESC
                                    ) o
             ),
        cte1
          AS ( SELECT   cte.RN ,
                        cte.ID ,
                        cte.Date ,
                        cte.Used ,
                        ( SELECT    SUM(Used)
                          FROM      cte i
                          WHERE     i.ID = cte.ID
                                    AND i.SurrKey = cte.SurrKey
                                    AND i.Date <= cte.Date
                        ) AS RToTal ,
                        cte.Allotment
                        - ( SELECT  SUM(Used)
                            FROM    cte i
                            WHERE   i.ID = cte.ID
                                    AND i.SurrKey = cte.SurrKey
                                    AND i.Date <= cte.Date
                          ) AS Remaining
               FROM     cte
             )
    SELECT  * FROM    cte1

im not able to test this right now, but will this work on SQL Server 2008R2 ? — ggarcia, Mar 04 '15 at 14:13
@ggarcia, no this will not work on 2008. If you have 2008 I will try to rewrite — Giorgi Nakeuri, Mar 04 '15 at 14:16
thank you very very much Giorgi Nakeuri, this is exactly what Im looking for. — ggarcia, Mar 04 '15 at 14:38
Hi @giorgi, there are some change of requirements regarding the query output, I'm wondering if it is still possible to use windowing function, I tried updating it but I can't seem to get the desired output. — ggarcia, Mar 12 '15 at 20:44
@ggarcia, I cant understand you. 1. In TableA surrkey 5, 6 have ID = 2. Shouldn't be there 3 as ID? 2. Where is `3 | 2015-01-15 | 150` in the output? 3. `11 | 3 | 2015-01-15 | 100` where did you get 100 from? — Giorgi Nakeuri, Mar 12 '15 at 21:20
Row_number 11 and 12 actually comes from 3 | 2015-01-15 | 150.. They are distributed since it is required to use all the allotment first before using a new one — ggarcia, Mar 12 '15 at 21:49
Then why `2 | 1 | 2015-01-15 | 2015-02-15 | 1500` is not distributed in your result? — Giorgi Nakeuri, Mar 13 '15 at 06:12
because the allocation in SurrKey 1 has been used up before the StartDate of Surrykey 2 — ggarcia, Mar 13 '15 at 08:48

TSQL Running total with start and end date

1 Answers1