condition within grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

condition within grouping

Author

Topic

mprb
Starting Member

2 Posts

Posted - 2010-04-07 : 11:59:01

I have a table like this
Individual ID Start Date End Date Service Date
1 1/1/2008 6/15/2008 1/1/2008
1 1/1/2008 6/15/2008 2/1/2008
1 1/1/2008 6/15/2008 3/1/2008
1 1/1/2008 6/15/2008 6/1/2008
1 7/1/2008 12/25/2008 8/1/2008
1 7/1/2008 12/25/2008 9/1/2008
1 7/1/2008 12/25/2008 10/1/2008
1 7/1/2008 12/25/2008 11/1/2008
1 7/1/2008 12/25/2008 12/1/2008
1 1/5/2009 9/18/2009 1/1/2009
1 1/5/2009 9/18/2009 2/1/2009
1 1/5/2009 9/18/2009 3/1/2009
1 1/5/2009 9/18/2009 4/1/2009
1 1/5/2009 9/18/2009 5/1/2009
1 1/5/2009 9/18/2009 6/1/2009
1 1/5/2009 9/18/2009 7/1/2009
1 1/5/2009 9/18/2009 8/1/2009
1 1/5/2009 9/18/2009 9/1/2009

I want to group by Individual ID and get the min(start date) and max(end date) and 12th service date

result would be
Individual ID min(Start Date) max(End Date) Service Date(12th month)
1----- 1/1/2008----- 9/18/2009---- 3/1/2009

group by on Invidual ID gives me start and end dates but i am not able to get the last column, Service Date on (12th month)

any suggestion would help
TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 12:59:22

[code]SELECT IndividualID,
MIN(StartDate) AS MinStartDate,
MAX(EndDate) AS MaxEndDate,
MAX(CASE WHEN Seq=12 THEN ServiceDate ELSE NULL END) AS [12thmnthServicedate]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY IndividualID ORDER BY ServiceDate) AS Seq,IndividualID,StartDate,EndDate,ServiceDate
FROM Table
)t
GROUP BY IndividualID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mprb
Starting Member

2 Posts

Posted - 2010-04-07 : 13:55:04

Thats exactly what i wanted, Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:58:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources