Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Finding patterns in rows (date ordered) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 04, 2013 9:36 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, April 09, 2013 2:06 AM Points: 299, Visits: 183
 Dear All,Here's the background - I have a table of staffids and the shifts/durations worked - with usually 1 shift worked per day. A simple version of the table looks like this:staffid (int), shiftdate (datetime), shiftdesc (varchar), shifthours (float).An example pattern of shiftdescs for a staffid could be:Sick, Sick, Nothing, Sick, Working, Working, Sick.So I need to hunt through the shifts in date order, and for each staffid to find:A count of the sickness episodes (unbroken by any other working shift, but we can include "nothing" shifts) - in this case there are 2.A sum of the hours counted for each episode (sum of shiftlength).The number of days in each episode - in this case the fiurst eipsode has 4 days, the second is just 1.The output can be 1 row per episode, which I can then sum up/work out the episode duration etc.staffid, episodestart, episodeend, totalshifthours.I am trying to avoid using cursors, and thinking about how to do something column-based. I could do with a few clues on the techniques side.Regards, Greg.
Post #1415378
 Posted Monday, February 04, 2013 9:45 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 8:49 AM Points: 2,239, Visits: 6,545
Post #1415381
 Posted Monday, February 04, 2013 10:29 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 3:11 PM Points: 8,938, Visits: 8,512
 greg.bull (2/4/2013)I am trying to avoid using cursors, and thinking about how to do something column-based. I could do with a few clues on the techniques side.Regards, Greg.Not to diminish Cadavre's valid point but since you said you want some clues, it sounds like you want to figure it out yourself but don't know where to start. Try reading this article, it explains how to accomplish exactly what you are trying to do. http://www.sqlservercentral.com/articles/T-SQL/71550/ _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1415399
 Posted Monday, February 04, 2013 10:58 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, April 09, 2013 2:06 AM Points: 299, Visits: 183
 Well, thanks. I wish I'd been able to find this earlier. As you cleverly noted - I really want to do this myself - so am grateful for the steer.Regards, Greg.
Post #1415410
 Posted Monday, February 04, 2013 5:34 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:58 PM Points: 2,368, Visits: 3,243
 greg.bull (2/4/2013)Well, thanks. I wish I'd been able to find this earlier. As you cleverly noted - I really want to do this myself - so am grateful for the steer.Regards, Greg.*** SPOILER ALERT!!! ***In case you're still challenged to make that article work for you (I assume it's Jeff Moden's Grouping Islands of Contiguous Dates article), this should put you over the top.`DECLARE @Shifts TABLE (staffid int, shiftdesc varchar(20), shiftdate datetime, shifthours float)INSERT INTO @ShiftsSELECT 1, 'Sick', '2012-02-01', 8 UNION ALL SELECT 1, 'Sick', '2012-02-02', 8UNION ALL SELECT 1, 'Nothing', '2012-02-03', 8UNION ALL SELECT 1, 'Sick', '2012-02-04', 8 UNION ALL SELECT 1, 'Working', '2012-02-05', 8UNION ALL SELECT 1, 'Working', '2012-02-06', 8 UNION ALL SELECT 1, 'Sick', '2012-02-07', 8SELECT staffid, periodstart=MIN(shiftdate), periodend=MAX(shiftdate) ,shiftdesc=MAX(shiftdesc) ,shifthours=SUM(shifthours) FROM ( SELECT staffid, shiftdesc, shiftdate, shifthours ,n=shiftdate-ROW_NUMBER() OVER ( PARTITION BY staffid, CASE shiftdesc WHEN 'Working' THEN 0 ELSE 1 END ORDER BY shiftdate) FROM @Shifts) aGROUP BY staffid, nORDER BY staffid, periodstart` No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1415538
 Posted Tuesday, February 05, 2013 2:33 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, April 09, 2013 2:06 AM Points: 299, Visits: 183
 Golly, thanks for this - saved me a lot of time. I read Jeff's article and was just beginning to understand bits of it when my daughter cam home from school. Which effectively ended my working day. I've modded the code you posted a bit to suit - I didn't want to count hours for all of the shifts. Sooo, a big thank you for taking the time and trouble to gen test data (yes, I should have done this) and for providing a solution that works. Just got to plug it into my main DB, and I suspect it'll run quickly. Regards, Greg.
Post #1415657
 Posted Tuesday, February 05, 2013 4:13 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:58 PM Points: 2,368, Visits: 3,243
 greg.bull (2/5/2013)Golly, thanks for this - saved me a lot of time. I read Jeff's article and was just beginning to understand bits of it when my daughter cam home from school. Which effectively ended my working day. I've modded the code you posted a bit to suit - I didn't want to count hours for all of the shifts. Sooo, a big thank you for taking the time and trouble to gen test data (yes, I should have done this) and for providing a solution that works. Just got to plug it into my main DB, and I suspect it'll run quickly. Regards, Greg.You're welcome!Actually, I'll confess that I had some issues understanding what was going on in the technique Jeff used in that article at first. Mucked around with it quite a bit before understanding finally dawned. So I like to practice to make sure I haven't forgotten it. No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1415716
 Posted Tuesday, February 05, 2013 6:17 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, April 09, 2013 2:06 AM Points: 299, Visits: 183
 I loved your "Hoo-uh" bit - was just watching Black Hawk Down again last night...
Post #1415788
 Posted Wednesday, February 06, 2013 2:36 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, April 09, 2013 2:06 AM Points: 299, Visits: 183
 Oh. Got a bit stuck - when there are two shifts for the same person on a day. This forces a new period, as in the test data/code below. have played around with this, but can't see how to accomodate it. The period for staffid 1 should be 01-04. Any ideas ?DECLARE @Shifts TABLE (staffid int, shiftdesc varchar(20), shiftdate datetime, shifthours float)INSERT INTO @Shifts SELECT 1, 'Sick', '2012-02-01', 8 UNION ALL SELECT 1, 'SickOff', '2012-02-02', 8UNION ALL SELECT 1, 'Sick', '2012-02-02', 8 -- Two shifts in a dayUNION ALL SELECT 1, 'Sick', '2012-02-03', 8UNION ALL SELECT 1, 'SickOff', '2012-02-04', 8UNION ALL SELECT 2, 'Sick', '2012-02-04', 8 UNION ALL SELECT 3, 'Working', '2012-02-05', 8UNION ALL SELECT 1, 'Working', '2012-02-06', 8 UNION ALL SELECT 1, 'Sick', '2012-02-07', 8SELECT staffid, periodstart=MIN(shiftdate), periodend=MAX(shiftdate) ,shiftdesc=MAX(shiftdesc) ,shifthours=SUM(shifthours) FROM ( SELECT staffid, shiftdesc, shiftdate, shifthours ,n=shiftdate-ROW_NUMBER() OVER ( PARTITION BY staffid, CASE shiftdesc WHEN 'Working' THEN 0 ELSE 1 END ORDER BY shiftdate) FROM @Shifts) aGROUP BY staffid, nORDER BY staffid, periodstart
Post #1416340
 Posted Wednesday, February 06, 2013 3:16 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:16 AM Points: 5,693, Visits: 11,117
 `DECLARE @Shifts TABLE (staffid int, shiftdesc varchar(20), shiftdate datetime, shifthours float)INSERT INTO @Shifts SELECT 1, 'Sick', '2012-02-01', 8 UNION ALL SELECT 1, 'SickOff', '2012-02-02', 8UNION ALL SELECT 1, 'Sick', '2012-02-02', 8 -- Two shifts in a dayUNION ALL SELECT 1, 'Sick', '2012-02-03', 8UNION ALL SELECT 1, 'SickOff', '2012-02-04', 8UNION ALL SELECT 1, 'Working', '2012-02-06', 8 UNION ALL SELECT 1, 'Sick', '2012-02-07', 8UNION ALL SELECT 2, 'Sick', '2012-02-04', 8 UNION ALL SELECT 3, 'Working', '2012-02-05', 8SELECT staffid, periodstart = MIN(shiftdate), periodend = MAX(shiftdate), shiftdesc = MAX(shiftdesc), shifthours = SUM(shifthours) FROM ( SELECT staffid, shiftdesc, shiftdate, shifthours, x.Working, n = shiftdate - DENSE_RANK() OVER (PARTITION BY staffid ORDER BY shiftdate, x.Working) FROM @Shifts CROSS APPLY (SELECT Working = CASE shiftdesc WHEN 'Working' THEN 1 ELSE 0 END) x) aGROUP BY staffid, n, WorkingORDER BY staffid, periodstart` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1416359

 Permissions