Running totals for previous X days

Question

Running totals for previous X days

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

kiril.lazarov.77 (5/14/2015)
Thank you so much for your help.
I'd appreciate if you could show me how to overcome the issue when there are +2 missing records. So if there is no change in the inventory for a given Id/Category, just display the previous known inventory.
I agree that my data is no fun to work with 🙁
Thank again!!

Still waiting for answers to this questions:

How do you want to handle the null date values?

What would the results look like if you only wanted the from 2015-05-12 forward?

In your sample data is the "positioning" of the data with null dates significant to the results?

Another question that needs answering, is there any other column or sets of columns that ensures the proper order of the data?

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

kiril.lazarov.77 (5/14/2015)
Thank you so much for your help.
I'd appreciate if you could show me how to overcome the issue when there are +2 missing records. So if there is no change in the inventory for a given Id/Category, just display the previous known inventory.
I agree that my data is no fun to work with 🙁
Thank again!!

Sure! I added one row to the test data to highlight the case (commented).

DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)

INSERT INTO @T

SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL

SELECT 1, 'A', NULL UNION ALL

SELECT 1, 'A', '2015-5-13' UNION ALL

SELECT 1, 'A', NULL UNION ALL

SELECT 1, 'A', '2015-5-13' UNION ALL

SELECT 1, 'A', '2015-5-12' UNION ALL

SELECT 1, 'A', NULL UNION ALL

SELECT 1, 'A', '2015-5-12' UNION ALL

SELECT 1, 'A', '2015-5-11' UNION ALL

SELECT 2, 'X', '2015-5-12' UNION ALL

SELECT 2, 'X', NULL UNION ALL

SELECT 2, 'X', '2015-5-12' UNION ALL

SELECT 2, 'X', NULL UNION ALL

SELECT 2, 'X', '2015-5-11' UNION ALL

SELECT 2, 'Y', '2015-5-13' UNION ALL

SELECT 2, 'Y', NULL UNION ALL

SELECT 2, 'Y', '2015-5-13' UNION ALL

SELECT 2, 'Y', '2015-5-12' UNION ALL

SELECT 2, 'Y', NULL UNION ALL

SELECT 2, 'Z', '2015-5-13' UNION ALL

SELECT 2, 'Z', NULL UNION ALL

SELECT 2, 'Z', '2015-5-11' UNION ALL

SELECT 2, 'Z', '2015-5-10'; -- Added to test new query

WITH DistinctDates AS

(

SELECT [Date]

FROM @T

WHERE [Date] IS NOT NULL

GROUP BY [Date]

),

GrandTotals AS

(

SELECT ID, Category, grand_total=COUNT(*)

FROM @T

GROUP BY ID, Category

),

RunningTotals AS

(

SELECT a.ID, a.Category

,grand_total=MAX(a.grand_total)

,running_total=MAX(a.running_total)

,a.[Date]

FROM

(

SELECT a.ID, a.Category, a.grand_total, b.[Date]

,running_total=COUNT(c.[Date]) OVER

(

PARTITION BY a.ID, a.Category

ORDER BY b.[Date]

ROWS UNBOUNDED PRECEDING

)

FROM GrandTotals a

CROSS JOIN DistinctDates b

LEFT JOIN @T c

ON a.ID = c.ID AND a.Category = c.Category AND b.[Date] = c.[Date]

) a

GROUP BY a.ID, a.Category, a.[Date]

)

SELECT ID, Category, grand_total

,running_total = CASE a.running_total

WHEN 0 THEN b.running_total

ELSE a.running_total

END

,[Date]

FROM RunningTotals a

OUTER APPLY

(

SELECT TOP 1 b.running_total

FROM RunningTotals b

WHERE a.ID = b.ID AND a.Category = b.Category AND

a.[Date] < b.[Date] AND

b.running_total <> 0

ORDER BY b.[Date]

) b (running_total)

ORDER BY a.ID, a.Category, a.[Date] DESC;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I was wondering, why would you have a value before an event happened? Shouldn't you have zeros until the first real occurrence?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Luis Cazares (5/14/2015)
I was wondering, why would you have a value before an event happened? Shouldn't you have zeros until the first real occurrence?

I confess to wondering the same thing.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St