DECLARE @test AS TABLE
(
Date DATE,
Volume INT
)
INSERT INTO @test
VALUES
(
'01/12/2019', '7'
)
INSERT INTO @test
VALUES
(
'02/12/2019', '7'
)
INSERT INTO @test
VALUES
(
'04/12/2019', '7'
)
INSERT INTO @test
VALUES
(
'05/12/2019', '7'
)
INSERT INTO @test
VALUES
(
'06/12/2019', '7'
)
INSERT INTO @test
VALUES
(
'08/12/2019', '7'
)
SELECT * FROM @test
So I'd like the values to be cumulative but the problem I have is there are dates without any volumes.
My desired output would be
2019-12-01 7
2019-12-02 14
2019-12-03 14 <---there is no 2019-12-03 record in the table
2019-12-04 21
2019-12-05 28
2019-12-06 35
2019-12-07 35 <---there is no 2019-12-07 record in the table
2019-12-08 42
I think what @harishgg1 is asking, and what I am not clear about is whether your dates are January 12, 2019, February 12, 2019 and so on, or are they December 1, 2019, December 2, 2019 etc.
This confusion comes about because of different regional date formats. In the US, where I am , 02/12/2019 is interpreted as Feb 12, 2019. In UK, it is interpreted as December 2, 2019.
To avoid ambiguity, the recommended practice is to use "YYYYMMDD" format when working with SQL Server.
; with tally_cte as
(
SELECT top 100 N=number FROM master..spt_values WHERE type = 'P'
), cte_N as
(
select a.N from tally_cte a where a.N < ( select max(datepart(dd,date)) from #test )
) , cte_start_date as
(
select min(date) as mindate from #test
) , cte_all as
(
select dateadd(dd,N,mindate) as ok from cte_N , cte_start_date
)
select 'SQL OutPut',a.ok,b.Volume,sum(b.volume) over(ORDER BY ok ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from cte_all a left join #test b on a.ok = b.Date
