SQL Server Running total result set doesn't include rows for records that don't have transactions for a particular month

If i have a result set like this where i want the running total to be present in every ppid. for example plan_id 00-peb17A doesn't have any transactions for ppid 388, so the record is null. I want to be able to find a way to have the previous rt amount carry over even if there were no transactions for that ppid.

Right now I'm placing a group by query in a cte and computing a running total using the over clause.

WITH cte AS
(
  SELECT plan_id, ppid,
    SUM(amount) AS Assets
  FROM #assets
  GROUP BY plan_id, ppid
  --ORDER BY plan_id, ppid ASC
)
SELECT *,
  SUM(cte.Assets) OVER(PARTITION BY cte.plan_id ORDER BY cte.ppid) AS rt
FROM cte

I'm thinking I could join the table with a numbers table to do some sort of cartesian product and attach a record to that table for every transaction. But I'm really not sure how else to explain it. Here is the original table I want to create the cumulative sum for.

| plan_id   |   ppid | amount |
| -------   |   ---- | ------ |
| 00-PEB17A |   386  |   20   | 
| 00-PEB17A |   386  |   15   | 
| 00-PEB17A |   387  |   5    | 
| 00-PEB17A |   387  |   10   | 
| 00-PEB17A |   389  |   20   | 
| 01-ARS20B |   386  |   5    | 
| 01-ARS20B |   386  |   10   | 
| 01-ARS20B |   386  |   50   | 
| 01-ARS20B |   387  |   30   | 
| 01-ARS20B |   388  |   5    | 

This is the result set I am getting
|  plan_id  | ppid   | Assets |  rt |
| --------- | ------ | ------ | --- |
| 00-PEB17A |  386   |   35   | 35  |
| 00-PEB17A |  387   |   15   | 50  |
| 00-PEB17A |  389   |   20   | 70  |
| 01-ARS20B |  386   |   65   | 65  |
| 01-ARS20B |  387   |   30   | 95  |
| 01-ARS20B |  388   |   5    | 100 |

I would like to get this result set where since ppid 388 is missing from plan id 00-PEB17A, the running total carries through. So if it is null, the put 0 but still continue the total.

|  plan_id  | ppid   | Assets |  rt |
| --------- | ------ | ------ | --- |
| 00-PEB17A |  386   |   35   | 35  |
| 00-PEB17A |  387   |   15   | 50  |
| 00-PEB17A |  388   |   0    | 50  |
| 00-PEB17A |  389   |   20   | 70  |
| 01-ARS20B |  386   |   65   | 65  |
| 01-ARS20B |  387   |   30   | 95  |
| 01-ARS20B |  388   |   5    | 100 |

Still haven't tried @ValNik recursive cte solution, but arrived at my own solution using a join with the numbers table and multiple cte's to get to the final running total. Here it is, would appreciate some feedback.

WITH a AS 
(
SELECT *
FROM #assets
CROSS JOIN #numbers
),
combine AS
(
    SELECT a.plan_id, a.n
    FROM a
    RIGHT OUTER JOIN #numbers AS n
        ON n.n = a.ppid
),
dist AS
(
    SELECT DISTINCT combine.plan_id, combine.n
    FROM combine
),
new_asset_list AS
(
    SELECT dist.plan_id, dist.n, ISNULL(ass.amount, 0) AS assets
    FROM dist
        LEFT OUTER JOIN #assets AS ass
            ON ass.plan_id = dist.plan_id
                AND ass.ppid = dist.n
),
grouped AS
(
    SELECT nal.plan_id, nal.n, SUM(nal.assets) AS Assets
    FROM new_asset_list AS nal
    GROUP BY nal.plan_id, nal.n
)
SELECT *,
    SUM(g.Assets) OVER(PARTITION BY g.plan_id ORDER BY g.n) AS rt
FROM grouped AS g

Numbers table as requested

CREATE TABLE #numbers
(
    n INT
)
INSERT INTO #numbers
(
    n
)
VALUES ( 386 ), ( 387 ), ( 388 ), ( 389 )

See example with recusive CTE

WITH rcte AS
(
  SELECT plan_id, ppid
    ,SUM(amount) AS Assets
    ,lead(ppid)over(partition by plan_id order by ppid) next_ppid
  FROM #assets
  GROUP BY plan_id, ppid
union all
  SELECT plan_id, ppid+1 as ppid
    ,0 AS Assets
    ,next_ppid
  FROM rcte
  where (ppid+1)<next_ppid
)
SELECT *,
  SUM(rcte.Assets) OVER(PARTITION BY rcte.plan_id ORDER BY rcte.ppid) AS rt
FROM rcte
option(maxrecursion 100)

maxrecursion=100 is default value. You can set this option to value enough for max missing range of ppid in table.

simple short query

WITH rcte AS
(
  SELECT plan_id, ppid
    ,SUM(amount) AS Assets
    ,SUM(SUM(amount)) OVER(PARTITION BY plan_id ORDER BY ppid) AS rt
    ,lead(ppid)over(partition by plan_id order by ppid) next_ppid
  FROM #assets
  GROUP BY plan_id, ppid
union all
  SELECT plan_id, ppid+1 as ppid
    ,0 AS Assets,rt
    ,next_ppid
  FROM rcte
  where (ppid+1)<next_ppid
)
SELECT *
FROM rcte

Example with numbers table

WITH  numbers as (
  select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))t(n)
)
,cte AS
(
  SELECT plan_id, ppid
    ,SUM(amount) AS Assets
    ,SUM(SUM(amount)) OVER(PARTITION BY plan_id ORDER BY ppid) AS rt
    ,lead(ppid)over(partition by plan_id order by ppid) next_ppid
  FROM #assets
  GROUP BY plan_id, ppid
)

SELECT plan_id,ppid+coalesce(n,0) ppid
  ,Assets
  ,rt
FROM cte
left join numbers on (ppid+n)<next_ppid

Numbers table mast contain enough numbers for max missing range in table.

SQL Server Running total result set doesn't include rows for records that don't have transactions for a particular month

1条答案

相关问题

热门标签

最新问答