March 30, 2009 at 8:19 am
Hi All,
I have been asked to generate some code which will produce a list of employee activity by day and create a rota from it... Please bear in mind that the SQL server is 2k and not going to be upgraded...
Here is an example of my data...
DECLARE @Shifts TABLE ( SchedDate DATETIME, Employee VARCHAR(100), Interval INT, Activity VARCHAR(50) )
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 54, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 55, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 56, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 57, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 58, 'Break')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 59, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 60, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 61, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'John Doe', 62, 'Overtime')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 32, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 33, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 34, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 35, 'Break')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 36, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 37, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 38, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 39, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 40, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 41, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 42, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 43, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 44, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 45, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 46, 'Lunch')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 47, 'Lunch')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 48, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 49, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 50, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 51, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 52, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 53, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 54, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 55, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 56, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 57, 'Time Between Shifts')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 58, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 59, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 60, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 61, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 62, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 63, 'Phone')
INSERT INTO @Shifts VALUES ('01/01/2009', 'Joe Bloggs', 64, 'Phone')
I then use the following code to get the shift patterns of each employee.
SELECT SchedDate, Employee, Activity, MIN(Interval), MAX(Interval)
FROM @Shifts
WHERE Activity NOT IN('Break', 'Lunch')
GROUP BY SchedDate, Employee, Activity
Returns:
2009-01-01 00:00:00.000Joe Bloggs Phone32 64
2009-01-01 00:00:00.000Joe Bloggs Time Between Shifts 50 57
2009-01-01 00:00:00.000John DoeOvertime54 62
Now this works fine (I convert the Interval to Datetime later) except when Time Between Shifts is in the table, if you look at joe Bloggs what I really need to do is say:
Joe Bloggs Phone 32 49
Joe Bloggs Time Between Shifts 50 57
Joe Bloggs Phone 58 64.
I know its because its grouping the one instance of Phone, but I need some clever way of getting around this... in 2k!!!
Any ideas?
Thanks in advance,
Dave
March 30, 2009 at 10:33 am
No idea if SS 2k is any different, however, I have solved a similar issue in the past using an identity column. Just grouping on a unique column prior to activity should solve this issue.
March 30, 2009 at 10:45 am
justanewone (3/30/2009)
Hi All,I have been asked to generate some code which will produce a list of employee activity by day and create a rota from it... Please bear in mind that the SQL server is 2k and not going to be upgraded...
Then this should probably have been posted in the SQL Server 2000 forums.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply