Getting cumulative values when certain dates have no records

Transact-SQL
1

Hello,

I've asked something similar in the past which was solved by using OVER and ORDER BY - I don't have that luxury for this, the SQL Server version is 10.50.6560.0 and so the syntax isn't recognised.

So I've had to do it the following old school way:

DECLARE @test AS TABLE
(
el4 NVARCHAR (50),
Yr INT,
Period INT,
Volume INT
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '4', '75'
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '5', '75'
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '6', '75'
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '7', '75'
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '8', '75'
)
INSERT INTO @test
VALUES 
(
'WP0001', '2019', '9', '75'
)
INSERT INTO @test
VALUES 
(
'WP0002', '2019', '4', '95'
)
INSERT INTO @test
VALUES 
(
'WP0002', '2019', '5', '95'
)
INSERT INTO @test
VALUES 
(
'WP0002', '2019', '7', '95'
)
INSERT INTO @test
VALUES 
(
'WP0002', '2019', '8', '95'
)
INSERT INTO @test
VALUES 
(
'WP0002', '2019', '9', '95'
)

SELECT 
t1.el4,
t1.yr,
t1.period,
t1.volume,
SUM (t2.volume) RT
from @test t1
INNER JOIN @test t2
ON t1.yr = t2.yr
AND t1.el4 = t2.el4 
AND t1.period >= t2.period
GROUP BY t1.yr, t1.period, t1.volume, t1.el4
ORDER BY el4

Here's the output with my issue:

|el4|yr|period|volume|RT|
|WP0001|2019|4|75|75|
|WP0001|2019|5|75|150|
|WP0001|2019|6|75|225|
|WP0001|2019|7|75|300|
|WP0001|2019|8|75|375|
|WP0001|2019|9|75|450|
|WP0002|2019|4|95|95|
|WP0002|2019|5|95|190|
                                      <---- there is no entry for WP0002 this month
|WP0002|2019|7|95|285|
|WP0002|2019|8|95|380|
|WP0002|2019|9|95|475|

How would I get an entry in here, it would look like

|WP0002|2019|6|0|190|

Any thoughts appreciated - remember, I have to use old school techniques to crack this :slight_smile:

2

Here is one way:

   With allPeriods
     As (
 Select y.el4
      , y.Yr
      , p.Period
   From (Select Distinct t.el4, t.Yr From @test t) As y
  Cross Apply (Values (4), (5), (6), (7), (8), (9)) As p(Period)
        )
 Select p.el4
      , p.Yr
      , p.Period
      , t3.Volume
   From allPeriods               p

  Outer Apply (Select Top 1
                      *
                 From @test     t2
                Where t2.el4 = p.el4
                  And t2.Yr = p.Yr
                  And t2.Period <= p.Period
                Order By
                      t2.el4
                    , t2.Yr
                    , t2.Period desc
             )                  t3;
1 Like
3

How many periods are there? Is period really Month?

how about a cte?
Here's the tally function

Create FUNCTION [dbo].[Tally]
    (
     @pMin BIGINT
    ,@pMax BIGINT
    )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    
    WITH T1(F) AS 
    (
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1
    ),
    T2(F) AS 
        (SELECT 1 FROM T1 A, T1 B),
    T3(F) AS 
        (SELECT 1 FROM T2 A, T2 B),
    T4(F) AS 
        (SELECT 1 FROM T3 A, T3 B),
    cteTally(N) AS 
        (
        SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
            + ((((ABS(@pMin)+@pMin)/2))-1)
        FROM T4
        )
SELECT 
    N
FROM 
    cteTally T
;

Here's the CTE

;with cte as 
(
select t.el4, t.yr, N as Period
  from (select Distinct el4, yr 
		  from #test) t
	 Cross Apply (select N from dbo.Tally (1,12)) N
)   

SELECT 
c.el4,
c.yr,
c.period,
IsNull(t1.volume,0) as Volume
,SUM (t2.volume) RT
from cte c
	left join #Test t1
		on c.el4 = t1.el4
		and c.yr = t1.yr
		and c.Period = t1.Period
	left JOIN #Test t2
		ON t1.yr = t2.yr
		AND t1.el4 = t2.el4 
		AND c.period >= t2.period
GROUP BY c.el4,
c.yr,
c.period,
IsNull(t1.volume,0) 

ORDER BY c.el4, c.yr, c.Period
1 Like