Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can You Get Min/Max Out of This? Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 143, Visits: 415


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
Post #1508224
Posted Thursday, October 24, 2013 1:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
This thread might be helpful: Query for Continuous Period.
Post #1508253
Posted Thursday, October 24, 2013 1:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 PM
Points: 1,057, Visits: 3,127
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
Post #1508255
Posted Thursday, October 24, 2013 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 143, Visits: 415
that seems to have done the trick.

thanks
Post #1508267
Posted Thursday, October 24, 2013 2:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 593, Visits: 929
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
Post #1508268
Posted Thursday, October 24, 2013 2:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 PM
Points: 1,057, Visits: 3,127
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
Post #1508277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse