Help - Date Range Problem

Question

Help - Date Range Problem

Carl Caputo

SSC Veteran

Points: 229
More actions
June 20, 2015 at 8:18 am

#299354

I have attribute history of items at week level and need to derive the range of start and end dates of each attribute change over time to insert into a dimension.
See image below of raw data on left, and desired output in green on the right:
http://pasteboard.co/1mGkPJ2Q.jpg
I have tried a lead function to detect when season code changes and take the end date but can't seem to group output correctly. Row number on season code does not restart the ordering for season codes that repeat over time for example "WA12" so hard to find the correct start date.
Can anyone help? I would appreciate it. Thanks very much.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Carl Caputo (6/20/2015)
I have attribute history of items at week level and need to derive the range of start and end dates of each attribute change over time to insert into a dimension.
See image below of raw data on left, and desired output in green on the right:
http://pasteboard.co/1mGkPJ2Q.jpg
I have tried a lead function to detect when season code changes and take the end date but can't seem to group output correctly. Row number on season code does not restart the ordering for season codes that repeat over time for example "WA12" so hard to find the correct start date.
Can anyone help? I would appreciate it. Thanks very much.

I think you'e making it more difficult than it needs to be... Try this...

SELECT

mt.ItemNo,

mt.Colour,

mt.Season,

MIN(mt.PeriodStart) AS PeriodStart,

MAX(mt.PeriodEnd) AS PeriodEnd

FROM

dbo.MyTable mt

GROUP BY

mt.ItemNo,

mt.Colour,

mt.Season

HTH,

Jason

Carl Caputo SSC Veteran Points: 229 More actions · Answer 2

Thanks, I tried that first but doesn't work because the group by takes the unique season code, therefore the MIN and MAX aggregates don't take into account season codes that appear more than once over time, such as "WA12" in my example. The start and end dates need to make sense sequentially and can't overlap.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Perhaps something like this?

First your sample data:

SET DATEFORMAT dmy

DECLARE @Seasons TABLE (ItemNo int, Colour char(10), Season char(4),PeriodStart date, PeriodEnd date)

INSERT INTO @Seasons

SELECT 225441, 'MELON','HE12','29/04/2012','05/05/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','06/05/2012','12/05/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','13/05/2012','19/05/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','20/05/2012','26/05/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','27/05/2012','02/06/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','03/06/2012','09/06/2012'

UNION ALL

SELECT 225441, 'MELON','HE12','10/06/2012','16/06/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','17/06/2012','23/06/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','24/06/2012','30/06/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','01/07/2012','07/07/2012'

UNION ALL

SELECT 225441, 'MELON','HE13','08/07/2012','14/07/2012'

UNION ALL

SELECT 225441, 'MELON','HE13','15/07/2012','21/07/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','22/07/2012','28/07/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','29/07/2012','04/08/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','05/08/2012','11/08/2012'

UNION ALL

SELECT 225441, 'MELON','WA12','12/08/2012','18/08/2012';

And then in the same batch this query:

WITH CTE AS (

SELECT

AbsoluteRN=ROW_NUMBER() OVER (ORDER BY PeriodStart asc),

GroupRN=ROW_NUMBER() OVER (PARTITION BY Season ORDER BY PeriodStart asc),

ItemNo,

Season,

PeriodStart,

PeriodEnd

FROM @Seasons

)

SELECT ItemNo,

Season,

PeriodStart=MIN(PeriodStart),

PeriodEnd=MAX(PeriodEnd)

FROM CTE

GROUP BY ItemNo,Season,AbsoluteRN-GroupRN

ORDER BY PeriodStart asc

Cheers!

Carl Caputo SSC Veteran Points: 229 More actions · Answer 4

Carl Caputo

SSC Veteran

Points: 229

June 20, 2015 at 11:57 am

#1806882

Thank you very much. Clever!