Generating date buckets based on next and previous record

[bpschoon]

Hi, 

I'm a relatively new user and I haven't seen a topic that quite matches what I am looking for. I have a data set that has products by plant location and usuage rates. When these usuage rates change a new record is created and date stamped. I create a date range for each record in SQL Server that has a beggining date and end (code below). I want to duplicate this process in Alteryx so I can join it to some sales data, but I can't for life of me figure out how to do it. Help?

WITH T1 AS
(SELECT *, Row_Number() OVER (PARTITION BY Location, ProductCode, ProductSubCode ORDER BY [Historical Date]) N
FROM #temp1
WHERE [Historical Date] > '05/31/2011')
SELECT *,
CASE WHEN N%2=1 THEN MAX(CASE WHEN N%2=0 THEN [Historical Date] END) OVER (Partition BY Location,productcode,
ProductSubcode, (N+1)/2) ELSE MAX(CASE WHEN N%2=1 THEN [Historical Date] END) OVER (Partition BY Location,productcode,
ProductSubcode, N/2) END NextDate,
CASE WHEN N%2=1 THEN MAX(CASE WHEN N%2=0 THEN [Historical Date] END) OVER (Partition BY Location,productcode,
ProductSubcode, N/2) ELSE MAX(CASE WHEN N%2=1 THEN [Historical Date] END) OVER (Partition BY Location, productcode,
ProductSubcode, (N+1)/2) END PriorDate
INTO #Temp2
FROM T1
ORDER BY Location,productcode,ProductSubcode,[Historical Date]
GO

[JohnJPS · Accepted solution]

You could, if desired, paste that entire query into your Input Tool's SQL Editor, and have it run server-side as part of your data pull.

Otherwise I would recommend the MultiRow Formula tool, which will allow you to look at the previous or next row in order to set values in the current row... you can apply a grouping in the tool as well, to group it on Location, Product Code, and ProductSubCode; then just look at dates in order to determine your NextDate and PriorDate. (See attached example using dummy random data).  Hope that helps!

[bpschoon]

The multi-row formula tool worked. I had to join some tables to get to the point where I would have had to apply my code. Thank you for the help.