SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can You Get Min/Max Out of This?


Can You Get Min/Max Out of This?

Author
Message
adams.squared
adams.squared
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 441

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

ID Val STARTDATE ENDDATE
51376 1 2012-12-30 2013-01-05
51376 1 2013-01-06 2013-01-06
51376 2 2013-01-07 2013-01-12
51376 1 2013-01-13 2013-01-13
51376 2 2013-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 Value STARTDATE ENDDATE
51376 1 12/30/2012 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
kl25
kl25
Say Hey Kid
Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)

Group: General Forum Members
Points: 690 Visits: 1875
This thread might be helpful: Query for Continuous Period.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 3317
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 on grouping islands of contiguous dates that may also help you.

Edit: Fixed link
adams.squared
adams.squared
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 441
that seems to have done the trick.

thanks
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 979
Mickey your link to Jeff's article is actually pointing back to this post. I think you meant to post to this article



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 3317
Keith Tate (10/24/2013)
Mickey your link to Jeff's article is actually pointing back to this post. I think you meant to post to this article

Whoops ... Gets me a lot that one. Fixed now I hope :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search