October 24, 2013 at 12:48 pm
CREATE TABLE #DATA
(ID INT,
Val INT,
STARTDATE DATE,
ENDDATE DATE)
INSERT INTO #DATA
VALUES
(51376, 1, '12/30/2012', '1/5/2013'),
(51376, 1, '1/6/2013', '1/6/2013'),
(51376, 2, '1/7/2013', '1/12/2013'),
(51376, 1, '1/13/2013', '1/13/2013'),
(51376, 2, '1/14/2013', '1/20/2013')
SELECT * FROM #DATA
IDValSTARTDATEENDDATE
5137612012-12-30 2013-01-05
5137612013-01-06 2013-01-06
5137622013-01-07 2013-01-12
5137612013-01-13 2013-01-13
5137622013-01-14 2013-01-20
What I want is for the first two rows have their dates combined. I want to say that for ID 51376 the value was 1 from 12/30/2012 until 01/06/2013. If I use min/max, I'll also get the date from 1/13/2013.
How do I get to this result set?
ID ValueSTARTDATE ENDDATE
51376112/30/2012 1/6/2013
5137621/7/2013 1/12/2013
5137611/13/2013 1/13/2013
5137621/14/2013 1/20/2013
October 24, 2013 at 1:58 pm
This thread might be helpful: Query for Continuous Period.
October 24, 2013 at 1:59 pm
Hi
This should get the result that you want, with a caveat. It assumes that you do not have gaps in your date ranges.
WITH cteGroup AS (
SELECT ID, VAL, STARTDATE, ENDDATE,
dateGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY STARTDATE) -
ROW_NUMBER() OVER (PARTITION BY ID, VAL ORDER BY STARTDATE)
FROM #DATA
)
SELECT ID, VAL, MIN(STARTDATE) STARTDATE, MAX(ENDDATE) ENDDATE
FROM cteGroup
GROUP BY ID, VAL, dateGroup
ORDER BY ID, MIN(STARTDATE);
Jeff Moden has done a excellent article[/url] on grouping islands of contiguous dates that may also help you.
Edit: Fixed link
October 24, 2013 at 2:22 pm
that seems to have done the trick.
thanks
October 24, 2013 at 2:23 pm
October 24, 2013 at 2:49 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply