Running sum with missing dates

Question

I have a table of data like this:

PeriodYearMonth Reg      PartNo             ComponentRemovals   RunningRemovals
2019 10         G-NHVP   109-0740V01-137    1                    1
2019 11         G-NHVP   109-0740V01-137    1                    2  
2019 12         G-NHVP   109-0740V01-137    1                    3
2020 01         G-NHVP   109-0740V01-137    1                    4
2019 10         OO-NSF   11-13354P          1                    1
2019 09         G-NHVR   11-13354P          2                    2 
2019 10         OY-HMV   11-13354P          1                    1

In the last column I calculated the 7-month running sum of removals per PartNo and per Reg. To do this I made the following code:

/****** Find Running Component Removals ******/
SELECT  [PeriodYearMonth]
      ,[Reg]
      ,[PartNo]
      ,[ComponentRemovals]
      ,sum(sum(ComponentRemovals)) over (Partition by [Reg], PartNo Order By PeriodYearMonth, PeriodYearMonth rows between 6 preceding and current row) as RunningRemovals
      ,[ConfirmedFailures]
      , sum(sum(ConfirmedFailures)) over (Partition by [Reg], PartNo Order By PeriodYearMonth, PeriodYearMonth rows between 6 preceding and current row) as RunningFailures

  FROM [RALNHVTST].[dbo].[vtRelRepComponentsRemovalsByPartNo]

  Group by Reg, PartNo, ComponentRemovals, ConfirmedFailures, PeriodYearMonth
  Order by PartNo

However, since not all months are included in the PeriodYearMonth column the results are incorrect. I've seen solutions online for simple cases, but the tricky part for me is, that I need an entry for every PartNo, every Reg and every month.

Any help would be greatly appreciated.

Regards

Build and populate a table of all the year-months. Left outer join it to your query. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 21, 2020 at 12:28

Nick · Accepted Answer · 2020-02-22 01:18:59Z

To solve this problem you need to create separate derived tables of all the PeriodYearMonth values (generated using a recursive CTE) and the distinct Reg and PartNo pairs, and CROSS JOIN them to each other to get all combinations of the columns. This combined table can then be LEFT JOINed to the original table to get the ComponentRemovals for each Reg, PartNo and PeriodYearMonth, and these can then be summed using a window function:

WITH CTE AS (
  SELECT CONVERT(DATE, CONCAT(REPLACE(MIN(PeriodYearMonth), ' ', '-'), '-01'), 23)  AS date, 
         CONVERT(DATE, CONCAT(REPLACE(MAX(PeriodYearMonth), ' ', '-'), '-01'), 23)  AS max_date
  FROM vtRelRepComponentsRemovalsByPartNo
  UNION ALL
  SELECT DATEADD(MONTH, 1, date), max_date
  FROM CTE
  WHERE date < max_date
)
SELECT FORMAT(CTE.date, 'yyyy MM') AS PeriodYearMonth
     , rp.Reg
     , rp.PartNo
     , COALESCE(v.ComponentRemovals, 0) AS ComponentRemovals
     , SUM(COALESCE(v.ComponentRemovals, 0)) OVER (PARTITION BY rp.Reg, rp.PartNo ORDER BY FORMAT(CTE.date, 'yyyy MM')
                                                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RunningRemovals
FROM CTE
CROSS JOIN (SELECT DISTINCT Reg, PartNo FROM vtRelRepComponentsRemovalsByPartNo) rp
LEFT JOIN vtRelRepComponentsRemovalsByPartNo v
       ON v.PeriodYearMonth = FORMAT(CTE.date, 'yyyy MM')
      AND v.Reg = rp.Reg
      AND v.PartNo = rp.PartNo
ORDER BY rp.Reg, rp.PartNo, CTE.date

Output

PeriodYearMonth     Reg     PartNo              ComponentRemovals   RunningRemovals
2019 09             G-NHVP  109-0740V01-137     0                   0
2019 10             G-NHVP  109-0740V01-137     1                   1
2019 11             G-NHVP  109-0740V01-137     1                   2
2019 12             G-NHVP  109-0740V01-137     1                   3
2020 01             G-NHVP  109-0740V01-137     1                   4
2019 09             G-NHVR  11-13354P           2                   2
2019 10             G-NHVR  11-13354P           0                   2
2019 11             G-NHVR  11-13354P           0                   2
2019 12             G-NHVR  11-13354P           0                   2
2020 01             G-NHVR  11-13354P           0                   2
2019 09             OO-NSF  11-13354P           0                   0
2019 10             OO-NSF  11-13354P           1                   1
2019 11             OO-NSF  11-13354P           0                   1
2019 12             OO-NSF  11-13354P           0                   1
2020 01             OO-NSF  11-13354P           0                   1
2019 09             OY-HMV  11-13354P           0                   0
2019 10             OY-HMV  11-13354P           1                   1
2019 11             OY-HMV  11-13354P           0                   1
2019 12             OY-HMV  11-13354P           0                   1
2020 01             OY-HMV  11-13354P           0                   1

Demo on SQLFiddle

Note that if all PeriodYearMonth values of interest are present in vtRelRepComponentsRemovalsByPartNo you can simply use a SELECT DISTINCT ... subquery to get those values rather than the recursive CTE e.g.

SELECT d.PeriodYearMonth
     , rp.Reg
     , rp.PartNo
     , COALESCE(v.ComponentRemovals, 0) AS ComponentRemovals
     , SUM(COALESCE(v.ComponentRemovals, 0)) OVER (PARTITION BY rp.Reg, rp.PartNo ORDER BY d.PeriodYearMonth
                                                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RunningRemovals
FROM (SELECT DISTINCT PeriodYearMonth FROM vtRelRepComponentsRemovalsByPartNo) d
CROSS JOIN (SELECT DISTINCT Reg, PartNo FROM vtRelRepComponentsRemovalsByPartNo) rp
LEFT JOIN vtRelRepComponentsRemovalsByPartNo v
       ON v.PeriodYearMonth = d.PeriodYearMonth
      AND v.Reg = rp.Reg
      AND v.PartNo = rp.PartNo
ORDER BY rp.Reg, rp.PartNo, d.PeriodYearMonth

Demo on SQLFiddle

This is great thanks! I'm getting the error: 'Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the concat operation.' I've had this before but I don't see how to resolve it here. I Only see one colum (PeriodYearMonth) in the concat operation. Any idea what other variable I need to change to resolve this?
You can ignore that. I solved it by Collating in the exoression.
@CathalClavie sorry I wasn't around to answer your comment but I'm glad you figured it out yourself and that this gave you the results you wanted.

Gordon Linoff · Accepted Answer · 2020-02-21 12:29:26Z

0

In the last column I calculated the running sum of removals per PartNo and per Reg over the past 7 months.

Removing the windowing clause and filter:

select [PeriodYearMonth], [Reg], [PartNo], [ComponentRemovals],
        sum(sum(ComponentRemovals)) over (Partition by [Reg], PartNo Order By PeriodYearMonth, PeriodYearMonth) as RunningRemovals
        [ConfirmedFailures],
        sum(sum(ConfirmedFailures)) over (Partition by [Reg], PartNo Order By PeriodYearMonth, PeriodYearMont) as RunningFailures
from [RALNHVTST].[dbo].[vtRelRepComponentsRemovalsByPartNo]
where PeriodYearMonth >= format(dateadd(month, -7, getdate()), 'yyyy MM')
group by Reg, PartNo, ComponentRemovals, ConfirmedFailures, PeriodYearMonth
Order by PartNo

answered Feb 21, 2020 at 12:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Cathal Clavie Over a year ago

Thank you for the reply but this doesn't solve my issue. I should've said 7-month running sum rather than 'over the past 7 months.'

Gordon Linoff Over a year ago

@CathalClavie . . . That was rather ambiguous. The original phrasing really sounded like the last seven months.

Collectives™ on Stack Overflow

Running sum with missing dates

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related