SQL Server : cumulative sum, missing dates

Question

I am working with Microsoft SQL Server 2012.

I have a table with Item transactions:

artcode transdate     Qty       transactionvalue
------------------------------------------------
M100    2010-11-24    6.00      179.40
M100    2010-11-24    -6.00     -179.4
M100    2010-11-25    100.00    2900.00
M100    2010-11-26    -1.00     -29
M100    2010-11-26    -5.00     -145
M100    2010-11-26    -1.00     -29
M100    2010-11-29    -5.00     -145
M100    2010-11-29    -3.00     -87
M100    2010-11-29    -1.00     -29

With this query I have managed to get the cumulative values in running order:

SELECT 
    TransDate, ArtCode, CumulativeQuantity, CumulativeValue 
FROM 
    (SELECT
         ArtCode, 
         SUM(CAST(REPLACE(REPLACE(NULLIF(Qty, ''), ',', '.'), ' ', '') AS float)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeQuantity,
         SUM(CAST(REPLACE(REPLACE(NULLIF(TotCostPrice, ''), ',', '.'), ' ', '') AS FLOAT)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeValue,
         TransDate
     FROM
         stage_itemhistory 
     WHERE
         artcode = 'm100' AND stockaffect = 1) S 
GROUP BY
    TransDate, ArtCode, CumulativeQuantity, CumulativeValue

This returns:

TransDate   ArtCode CumulativeQuantity  CumulativeValue
--------------------------------------------------------
2010-11-24  M100    0                   0
2010-11-25  M100    100                 2900
2010-11-26  M100    93                  2697
2010-11-29  M100    84                  2436

Which is quite close to what I am after, the only thing missing is the dates between, that would have the previous dates cumulative values. So it would look like this:

TransDate   ArtCode CumulativeQuantity  CumulativeValue
--------------------------------------------------------
2010-11-24  M100    0                   0
2010-11-25  M100    100                 2900
2010-11-26  M100    93                  2697
2010-11-27  M100    93                  2697
2010-11-28  M100    93                  2697
2010-11-29  M100    84                  2436

Any and all help would be greatly appreciated! Thank you in advance.

Left join with a pre-populated calendar table (or a cte). This question have been asked and answered many times before. — Zohar Peled, Mar 13 '18 at 12:28
On a different note, that fact that you're doing `CAST(REPLACE(REPLACE(NULLIF(Qty, ''), ',', '.'), ' ', '') AS float)` implies you're storing your numerical values as a `(n)varchar`. **WHY!?** This is an awful idea; store numbers as numbers (and use the relevant numeric data type, such as `decimal`, `int`, `tinyint`, etc), date(time)s as date(time)s, xml as xml, etc,etc. Storing them as a `(n)varchar` is going to cause you nothing but trouble and problems. — Thom A, Mar 13 '18 at 12:36
And don't convert to float. It is an approximate datatype, use numeric instead so you can get an accurate calculation. — Sean Lange, Mar 13 '18 at 13:39
This is NOT a duplicate. It is much more complex than a simple date range. It needs a data range for each ArtCode and must also fill in the ArtCode in the missing dates. By closing you denied me posting a valid answer. I encourage a reopen. — paparazzo, Mar 13 '18 at 15:00
@ZoharPeled It is more complex than a simple date range. Please see my comment above. — paparazzo, Mar 13 '18 at 15:01
@Larnu It is more complex than a simple date range. Please see my comment above. — paparazzo, Mar 13 '18 at 15:01
@Paparazzi how isn't it? A simple date table will still solve the issue, even if it means performing a `CROSS JOIN` on the date table and a distinct dataset of `ArtCode`. Once your have the dates dataset, the problem solves itself. I disagree that this needs a reopen. — Thom A, Mar 13 '18 at 15:11
@Larnu I feel like I explained above. Pretty sure OP is not looking for each ArtCode to have the date range of all ArtCodes. Two ArtCodes might not overlap date range at all. Maybe, just maybe (rep of 33K) I know what I am talking about. Especially since I have a solution. — paparazzo, Mar 13 '18 at 15:18
@Paparazzi perhaps I'm missing something, but to me it seems like a duplicate question. filling in the values for missing dates can be done with a simple subquery. Sorry, but I'm not convinced. — Zohar Peled, Mar 13 '18 at 15:18
@ZoharPeled If you would let me post maybe you could be convinced. I encourage you to add in another ArtCode with a different date range and see if you can do it with a simple subquery. — paparazzo, Mar 13 '18 at 15:20
@Paparazzi not convinced yet, but since you are so sure it's not a duplicate, I will not stop you. Go ahead and post your answer. — Zohar Peled, Mar 13 '18 at 15:23
Not to seem rude, @paparazzo, but are you implying that I don't know what I'm doing because I have less reputation than you? Just because I've been an active user for 4 months on SO, while you have been for many more does not make my input any less worthy than yours. Although I'm sure it wasn't your intent, your comment could be seen as degrading. Back onto the discussion, you can still put limitations on the dates returned for each ArtCode; `CROSS JOIN` was just the simplest example. — Thom A, Mar 13 '18 at 15:23
@Larnu Not my intention to argue with you. You can use your votes as you chose. — paparazzo, Mar 13 '18 at 15:36
Thanks for the reopen! I posted my solution for the problem. I figured it out, it was a bit more difficult since I did not find an exact match here as @paparazzo pointed out. — Sam, Mar 15 '18 at 14:43

paparazzo · Accepted Answer · 2018-03-13T15:43:49.533

This was harder than I thought it would be. Someone may have a simpler solution. Need to fill in the artcode and also consider different ranges on different artcodes.

declare @T table (artcode varchar(10), transdate date, Qty smallmoney, transactionvalue smallmoney);
insert into @T values 
       ('M100', '2010-11-24', 6.00, 179.40)
     , ('M100', '2010-11-24', -6.00, -179.4)
     , ('M100', '2010-11-25', 100.00, 2900.00)
     , ('M100', '2010-11-26', -1.00, -29)
     , ('M100', '2010-11-26', -5.00, -145)
     , ('M100', '2010-11-26', -1.00, -29)
     , ('M100', '2010-11-29', -5.00, -145)
     , ('M100', '2010-11-29', -3.00, -87)
     , ('M100', '2010-11-29', -1.00, -29)
     , ('M101', '2010-11-23', 6.00, 179.40)
     , ('M101', '2010-11-25', 100.00, 2900.00)
     , ('M101', '2010-11-26', -1.00, -29)
     , ('M101', '2010-11-26', -5.00, -145)
     , ('M101', '2010-11-26', -1.00, -29)
     , ('M101', '2010-11-30', -5.00, -145)
     , ('M101', '2010-11-30', -3.00, -87)
     , ('M101', '2010-11-30', -1.00, -29);
with limits as 
( select t.artcode, min(t.transdate) as startDate, max(t.transdate) as endtDate 
  from @T t 
  group by t.artcode
)
, dts as 
( select l.artcode, l.startDate as dt, l.startDate, l.endtDate
    from limits l 
  union all 
  select l.artcode, dateadd(day, 1, l.dt), l.startDate, l.endtDate
  from dts l 
  where dateadd(day, 1, l.dt) <= l.endtDate
)
select distinct dts.artcode, dts.dt
     , sum(isnull(t.Qty, 0))              over (partition by dts.artcode order by dts.dt) as Qty
     , sum(isnull(t.transactionvalue, 0)) over (partition by dts.artcode order by dts.dt) as transactionvalue
from dts 
left join @T t 
  on t.transdate = dts.dt 
 and t.artcode = dts.artcode
order by dts.artcode, dts.dt;

artcode    dt         Qty                   transactionvalue
---------- ---------- --------------------- ---------------------
M100       2010-11-24 0.00                  0.00
M100       2010-11-25 100.00                2900.00
M100       2010-11-26 93.00                 2697.00
M100       2010-11-27 93.00                 2697.00
M100       2010-11-28 93.00                 2697.00
M100       2010-11-29 84.00                 2436.00
M101       2010-11-23 6.00                  179.40
M101       2010-11-24 6.00                  179.40
M101       2010-11-25 106.00                3079.40
M101       2010-11-26 99.00                 2876.40
M101       2010-11-27 99.00                 2876.40
M101       2010-11-28 99.00                 2876.40
M101       2010-11-29 99.00                 2876.40
M101       2010-11-30 90.00                 2615.40

I think this kind of covered what we were discussing in the comments; however, rather than using a Calendar Table youv'e used a rCTE. Like i said in the comments, you can `CROSS JOIN` the dates to the `DISTINCT` artcodes or filter them; as you have effectively done here. I still think that this, therefore, is a duplicate, however, have also voted up; as this does offer the OP their solution. — Thom A, Mar 13 '18 at 16:23
@Larnu Don't want to argue but cross join to distinct artcodes fails on get dates for that artcode. — paparazzo, Mar 13 '18 at 16:29
Think you've missed my point (again). Either way, yes, this works. — Thom A, Mar 13 '18 at 16:30

Michał Turczyn · Answer 2 · 2018-03-13T15:58:38.920

The way to solve this, is to use CTE with all days of a year (or required time span) and then left join appropriately, like this:

with Calendar as (
    select CAST('2010-01-01' as date) [transdate], 0 [Qty], 0 [transactionvalue]
    union all
    select DATEADD(DAY, 1, [transdate]), 0, 0 from Calendar
    where [transdate] < '2010-12-31'
), with Calendar2 ([artcode], [transdate], [Qty], [transactionvalue]) as (
    select [T].artcode, [C].*  from Calendar [C]
    cross join (select distinct artcode from  MY_TABLE) [T]
)

select [a].[artcode], [a].[transdate], isnull([b].[Qty],[a].[Qty]),isnull([b].[transactionvalue],[a].[transactionvalue]) from Calendar2 [a]
left join MY_TABLE [b] on ([a].transdate = [b].transdate and [a].[artcode] = [b].[artocde])
option(maxrecursion 0)

Creating a date list isn't a "work around." It's the solution. :) — Eric Brandt, Mar 13 '18 at 13:29

John Cappelletti · Answer 3 · 2018-03-13T14:10:10.250

If you don't have a Calendar or Tally Table, you can use an ad-hoc tally table

Also, assuming partitioned by ArdCode

Example

;with cte as (
        SELECT 
            TransDate, ArtCode, CumulativeQuantity, CumulativeValue 
        FROM 
            (SELECT
                 ArtCode, 
                 SUM(CAST(REPLACE(REPLACE(NULLIF(Qty, ''), ',', '.'), ' ', '') AS float)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeQuantity,
                 SUM(CAST(REPLACE(REPLACE(NULLIF(TotCostPrice, ''), ',', '.'), ' ', '') AS FLOAT)) OVER (PARTITION BY artcode ORDER BY transdate) AS CumulativeValue,
                 TransDate
             FROM
                 stage_itemhistory 
             WHERE
                 artcode = 'm100' AND stockaffect = 1) S 
        GROUP BY
            TransDate, ArtCode, CumulativeQuantity, CumulativeValue
)
Select TransDate = B.D
      ,A.ArtCode
      ,A.CumulativeQuantity  
      ,A.CumulativeValue
 From ( 
        Select *
              ,NextDate = lead([TransDate],1,dateadd(day,1,[TransDate])) over (partition by ArtCode order by [TransDate]) 
         From  cte
      )  A
 Cross Apply (
                Select Top (DateDiff(DAY,[TransDate],NextDate)) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[TransDate]) From  master..spt_values n1,master..spt_values n2
             ) B

Returns

TransDate   ArtCode CumulativeQuantity  CumulativeValue
2010-11-24  M100    0                   0
2010-11-25  M100    100                 2900
2010-11-26  M100    93                  2697
2010-11-27  M100    93                  2697
2010-11-28  M100    93                  2697
2010-11-29  M100    84                  2436

score -1 · Answer 4 · answered Mar 15 '18 at 14:40

I found a solution that works for me:

--Create TempTable
CREATE TABLE #TempTable(
 TransDate Date,
 ArtCode varchar(150),
 CumulativeQuantity float(53),
 CumulativeValue float(53))

--Insert Cumulative data, still missing dates
insert into #TempTable (TransDate, ArtCode, CumulativeQuantity, CumulativeValue)
SELECT TransDate, ArtCode, CumulativeQuantity, CumulativeValue FROM (
        select  ArtCode, 
                SUM(Qty) OVER (partition by artcode order by transdate) AS CumulativeQuantity,
                SUM(TotCostPrice) OVER (partition by artcode order by transdate) AS CumulativeValue,
                TransDate
        from stage_itemhistory where stockaffect=1
        ) S
    group by TransDate, ArtCode, CumulativeQuantity, CumulativeValue;

--Select all with Missing dates with previous dates data
WITH CTE as (
    SELECT TransDate, ArtCode, CumulativeQuantity, CumulativeValue FROM #TempTable
    UNION ALL
    select DATEADD(Day,1,TransDate), ArtCode, CumulativeQuantity, CumulativeValue FROM CTE e
    Where Not Exists (select * from #TempTable e2 where e2.TransDate = DATEADD(DAY,1,e.TransDate) and e.ArtCode=e2.ArtCode)
    and TransDate < GETDATE()
)
select  TransDate, 
        ArtCode,
        CumulativeQuantity,
        ROUND(CumulativeValue,2)
from CTE where TransDate > '2016-01-01'
Order by ArtCode, TransDate
OPTION (MAXRECURSION 0)

I get the correct answer from my code from what you posted as data and desired output. This is a less efficient version of my approach. You don't start at a proper anchor. Look at the number of duplicate rows produced. Not exist is less efficient than just getting the max once. Then you give yourself the check. Sam I will remember you. — paparazzo, Mar 15 '18 at 16:34
All right. No need to get all worked up. Just tried your code and works great. Thank you for you time. Gave you the check. — Sam, Mar 27 '18 at 13:11

SQL Server : cumulative sum, missing dates

4 Answers4