Challenging Group By - 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)

Challenging Group By

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2011-06-25 : 19:00:13

create table EMP_INFO
(EMP_NUMBER varchar(13),
COMPANY varchar(10),
AGREE_START_DT datetime,
PROCESS_TYPE varchar(10),
STATUS_CODE varchar(10)
)

INSERT INTO EMP_INFO
SELECT '899','Y2C' ,'2/15/2010','MP','SA' UNION ALL
SELECT '899','Y2C' ,'7/27/2010','MP','FE' UNION ALL
SELECT '65421','YELLO' ,'2/15/2010','CP','SA' UNION ALL
SELECT '65421','YELLO' ,'4/30/2010','CP','TT' UNION ALL
SELECT '65421','YELLO' ,'5/30/2010','CP','TD' UNION ALL
SELECT '65421','YELLO' ,'6/19/2010','MP','MA' UNION ALL
SELECT '65421','YELLO' ,'6/21/2010','CP','FE' UNION ALL
SELECT '65421','YELLO' ,'6/28/2010','PF','MA' UNION ALL
SELECT '65421','YELLO' ,'11/20/2010','CP','FE'

-------------------------------------------------------
Tried grouping to get a result like

'899','Y2C' ,'2/15/2010','MP','SA'
'65421','YELLO' ,'2/15/2010','CP','SA'
'65421','YELLO' ,'6/19/2010','MP','MA'
'65421','YELLO' ,'6/21/2010','CP','FE'
'65421','YELLO' ,'6/28/2010','PF','MA'
'65421','YELLO' ,'11/20/2010','CP','FE'

Grouping logic is like :

group by EMP_NUMBER and COMPANY and want the minimum value of AGREE_START_DT based on the PROCESS_TYPE

The challenge is when we group we should get the consecutive row values with same EMP_NUMBER and COMPANY based on the date and if the consecutive rows have the same PROCESS_TYPE then we need to get only the minimum row with the AGREE_START_DT but if the consecutive rows differs in the PROCESS_TYPE then you can't group them.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-27 : 05:43:17

I don't know for certain if these will work in 2005 as I've only been using them myself since moving to 2008, but check out the RANK and DENSE_RANK commands.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
http://www.hollowtreewebdesign.co.uk - a web design company in its infancy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources