A grouping problem - 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)

A grouping problem

Author

Topic

Eggbeard
Starting Member

3 Posts

Posted - 2009-01-09 : 07:26:52

Hello All,

Firstly please forgive me if this is in the wrong place. A friend asked me a question yesterday. He is trying to group a set of data that is used for monitoring time taken off as sick for employees. The aim of the exercise is to present each employee, the reason they were sick, and the consecutive dates that they took off for that reason. It is driving me mad, and I cannot see the wood for the trees and was wondering if anyone here might be able to help. This is in SQL Server 2005.

The sample data is:
Employee,Date,Reason
--------------------
dave,01/01/08,sick1
dave,02/01/08,sick1
dave,03/01/08,sick2
dave,04/01/08,sick1
dave,05/01/08,sick1
bob,01/01/08,sick1
bob,02/01/08,sick1
bob,03/01/08,sick3
bob,04/01/08,sick3
bob,05/01/08,sick3

The desired output would look like:
Employee,From,to,reason
dave, 01/01/08 ,02/01/08, sick1
dave, 03/01/08 ,03/01/08, sick2
dave, 04/01/08 ,05/01/08, sick1
bob, 01/01/08 ,02/01/08, sick1
bob, 03/01/08 ,05/01/08, sick3

I have tried all sorts of grouping methods, where exists and I still cant seem to get anywhere with it...please help!

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-01-09 : 07:34:19

Would is not be better to change the design of the table to hold the start and end date of the sickness instead of repeating data

Eggbeard
Starting Member

3 Posts

Posted - 2009-01-09 : 07:39:18

Hell NeilG,

I think they are trying to do that, but they have a lot of data in the original format, and they are trying to chuck into the desired output format, but aare having trouble getting a query to display the data with the groupings they need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 08:53:31

[code]
SELECT t.Employee,
MIN(t.Date) AS From,
MAX(t.Date) AS To,
t.Reason
FROM YourTable t
OUTER APPLY(SELECT TOP 1 Date
FROM YourTable
WHERE Employee=t.Employee
AND Date >t.Date
AND Reason <> t.Reason
ORDER BY Date)tmp
GROUP BY t.Employee,t.Reason,tmp.Date
[/code]

Eggbeard
Starting Member

3 Posts

Posted - 2009-01-09 : 09:48:51

Hello visakh16,

Many thanks, worked perfectly and now I can get on with my own work, and my friend will leave me alone! I am very grateful

Best Regards

Ian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources