February 24, 2014 at 10:09 pm
Something like this might also work:
WITH Tally(n) AS
(
SELECT 0 UNION ALL
SELECT TOP (SELECT MAX(DATEDIFF(day, FROM_DATE, TO_DATE)) FROM #dates)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
)
SELECT store, product, FROM_DATE=MIN(d), TO_DATE=MAX(CASE WHEN rn2 IS NULL THEN d END), stock=MAX(stock)
FROM
(
SELECT store, product, d, stock, FROM_DATE, TO_DATE
,rn1=(d-FROM_DATE)-ROW_NUMBER() OVER (PARTITION BY store, product, stock ORDER BY d)
,rn2=CASE WHEN TO_DATE IS NULL THEN ROW_NUMBER() OVER (PARTITION BY store, product, stock ORDER BY d) END
FROM
(
SELECT store, product, FROM_DATE=MIN(FROM_DATE), TO_DATE=MIN(TO_DATE)
, d, stock=SUM(stock)
FROM #dates
OUTER APPLY
(
SELECT d=DATEADD(day, n, FROM_DATE)
FROM Tally
WHERE n < 1+DATEDIFF(day, FROM_DATE, ISNULL(TO_DATE, FROM_DATE+30)) -- May need to change 30 to something larger
) b
GROUP BY store, product, d
) a
) a
WHERE ISNULL(rn2, 1) = 1
GROUP BY store, product, rn1
ORDER BY store, product, FROM_DATE
However I suspect that the article referenced below (actually a series of 4) by Itzik Ben-Gan would probably offer a more efficient means to the solution than expanding with a Tally table like Luis and I did. Assuming of course if you can figure out how to replace his counts with your sum of inventory.
I confess to trying but I couldn't get it to work in the limited time I had available.
Edit: Forgot to mention. Most of IBG's solutions use some SQL 2012 features like LEAD. This complicates employing them in SQL 2008 (assuming you're posting to this forum because you're using SQL 2008) but there are alternatives there that can be used as a replacement.
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
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply