consecutive work / sick days

[antiskeptic]

I am using SQL Server 2000. I am creating a stored procedure of which the output will be used in a Crystal report. But I need help getting the output right. I am making a payroll audit report to see how many consecutive days are taken off during a pay period. I have tried several different avenues but nothing really works.

The closest I seem to get is this temp table that stored all the worked and off days, and the dates. The output from this table is like this:
(PTO = Paid Time Off / WT = Work Time)

Name TimeCode ItemDate
George Jetson PTO 2004-08-09
George Jetson WT 2004-08-10
George Jetson WT 2004-08-11
George Jetson WT 2004-08-12
George Jetson WT 2004-08-13
George Jetson PTO 2004-08-18
George Jetson PTO 2004-08-19
George Jetson PTO 2004-08-20
Fred Flintstone WT 2004-08-11
Fred Flintstone PTO 2004-08-12
Fred Flintstone PTO 2004-08-13
Fred Flintstone PTO 2004-08-16
Fred Flintstone WT 2004-08-17
Fred Flintstone WT 2004-08-18
Fred Flintstone WT 2004-08-19
Fred Flintstone WT 2004-08-20
Johnny Quest WT 2004-08-11
Johnny Quest PTO 2004-08-12
Johnny Quest PTO 2004-08-13
Johnny Quest WT 2004-08-16
Johnny Quest PTO 2004-08-17
Johnny Quest PTO 2004-08-18
Johnny Quest WT 2004-08-19
Johnny Quest WT 2004-08-20

Now George has the 18, 19, & 20th as paid off so I would want to see him on the report for those days.

Fred would also appear on the report even though the dates are not exactly consecutive. The skip in dates from the 13th to the 16th is over a weekend, so for the purposes of this data output, this is considered consecutive.

And Johnny would not show up at all because he only takes off 2 days at a time.

So I need to figure out how to count (like a running total) the codes when they switch from WorkTime or PaidTimeOff. And be able to take into consideration skipped over days. The only way the PTO "breaks" is if a WT timecode interrupts the "flow". (If that makes any sense) ANd I don't care about any of the PTO if it's not 3 days or more in a row.

Can anyone see of a way I can do this?

My final out put needs to be this layout below:

Name TimeCode Min Date Max Date
George Jetson PTO 2004-08-18 2004-08-20
Fred Flintstone PTO 2004-08-12 2004-08-16

Thanks in advance for any tips, pointers, or shoves in the right direction. THank you!

SIncerely,
Antiskeptic

 

[vongrunt]

OK, tip:

set datefirst 1
select *, datediff( ww, 0, ItemDate ) * 5 + datepart( dw, ItemDate) as cc
from myTable
-- where TimeCode = 'PTO'
order by Name, ItemDate

Column "cc" contains consecutive values you may find useful (Saturdays and Sundays ignored) but... what about holidays?

 

[donutman]

antiskeptic,
Rather than finding the best way to proceed from a temporary table that you have created, it might be better to rethink the entire problem from the original source of the data. What do you have available to you? I'm really curious about why the 3 consecutive PTO's count but not the double pair for Johnny Quest. I understand the rule, but not the reason!
Vongrunt, I'm guessing he's already taken out holidays by not having a WT row for a holiday. If that's true then the cc approach doesn't help (as far as I understand your hint).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

actually anyday that is skipped is needing to be taken into consideration. I was just using a weekend as an example...

...so if I have:

8/3/04 PTO
8/5/04 PTO
8/7/04 PTO
8/10/04 WT
8/11/04 WT
8/13/04 WT
8/14/04 WT

Or

8/3/04 PTO
8/4/04 PTO
8/5/04 PTO
8/6/04 WT
8/7/04 WT
8/8/04 WT
8/9/04 WT

Both would show up with the same amount of consecutive days.
In my temp table, as it stands now, I have it sorted by the name, date, then by the timecode (PTO or WT) so even if this instance were to occur it would still pick it out the 3 consecutive days the 5th thru the 7th.

8/3/04 PTO
8/4/04 WT
8/5/04 PTO
8/6/04 PTO
8/7/04 PTO
8/8/04 WT
8/9/04 WT

I tried doing two separate temp tables. One for the WT and one for the PTO...and then checking if the WT date was BETWEEN the Minimum date and Maximum date of the PTO days. But the reason that doesn't work in this case, is that the MIN date (8/3) and the MAX date (8/7) are not consecutive...there is a break in there where the person worked for a day. Does this make any sense? I may be only confusing you worse.
However, your solution would work very well, if I was only dealing with holidays or weekends...but I'm dealing with days that were scheduled in a row, rather that true consecutive days. Sometimes they can just be off midweek as thier day off.

Thank you,
Antiskeptic

 

[antiskeptic]

Donutman...
You must have responded as I was writing my last reply...

did I answer your question at all, or do you still need more explaination?

And you are correct about the holidays...if it's a holiday or they simply didn't have a timecode for that day then it just does not come into play, and the next days worked, or paid for time off is the next consecutive day in the eyes of this data.

Antiskeptic

 

[Creto]

I think this may work for you, if I'm understanding what you're wanting...

strCurrEmp		=	""
intTotConsec	=	0
intDayCount		=	0
strLastDay		=	""

While not rsTimeCards.EOF
	If strCurrEmp <> rsTimeCards("Name") Then
		If strCurrEmp = "" Then
			strCurrEmp = rsTimeCards("Name")
		Else
			If intDayCount > 2 Then
				intTotConsec = intTotConsec + 1
			End If
			Response.Write strCurrEmp & ": " & intTotConsec & " times of 3 or more days taken off consecutively."
		End If
		intTotConsec = 0
		intDayCount = 0
		strLastDay = ""
	End If
	If strLastDay = "PTO" AND rsTimeCards("TimeCode") = "PTO" Then
		intDayCount = intDayCount + 1
	ElseIf strLastDay = "PTO" AND intDayCount > 2 AND rsTimeCards("TimeCode") <> "PTO" Then
		intTotConsec = intTotConsec + 1
		intDayCount = 0
	ElseIf strLastDay = "" AND rsTimeCards("TimeCode") = "PTO" Then
		intDayCount = 1
	ElseIf strLastDay = "PTO AND rsTimeCards("TimeCode") <> "PTO" Then
		intDayCount = 0
	End If
	strLastDay	=	rsTimeCards("TimeCode")
	rsTimeCards.MoveNext
Wend

 

[Creto]

Ah crap, disregard my post, I was thinking in terms of ASP... *bonk self*.

 

[donutman]

You can do this by adding an identity field and a GroupFld (default it to 0) to the temp table as long as you can sort the data prior to entering it into the temp table (containing both WT and PTO entries). For small tables you can use an identity field and rely on the results but for very large tables it is possible that the sort will NOT be completed prior to the insert! This problem will be fixed when Yukon gives us a Rank() function.
With that caveat, here is the code:

   DECLARE @Grp as int
   SET @Grp=0
   UPDATE T2
      SET @Grp=Case When T1.TimeCode=T2.TimeCode 
          Then @Grp Else @Grp+1 end,
          GroupFld=@Grp
      FROM #TempTable T1 INNER JOIN #TempTable T2
      ON T1.ID=T2.ID-1

Now you can group the data on GroupFld and look for group counts greater than 2:

   SELECT Count(*), MIN(ItemDate), MAX(ItemDate), Name, TimeCode
      FROM #TempTable 
      WHERE TimeCode='PTO'
      Group By Name, TimeCode
      Having Count(*)>2

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oops, forgot to add the GroupFld to the Group By clause.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

that's okay...thank you for the attempt anyway. *ha*
I wasn't sure I was understanding it...so I'm glad you wrote back .*haha* Any way to do this other than ASP?

To all,
My theory is this could be done with a loop. I just need to figure out how to assign the fields needing to be counted to variables, so that it counts the days, until it encounters the other timecode, then restart the count evertime it changes. And "spit out" all records associated with a count of 3 or more on the PTO timecode. You know what I mean? Can this be done? Anybody?

Sincerely,
Antiskeptic

 

[antiskeptic]

Donutman...
we did it again...*ha* I will give this a shot and see where it puts me.
Thank you so much for your input so far.

Thank you,
Antiskeptic

 

[antiskeptic]

Okay..., maybe I just don't really get how this works.
I'm getting GrpFld numbers of 999 or 345. Not exactly what I was looking for, so I'm wondering how this is supposed to be working. What number should be updating into the GrpFld?


and would my theory of the Looping count work?

Sincerely,
Antiskeptic

 

[donutman]

Antiskeptic,
It worked with a test sample that I made, but I don't like the solution anyway. It relies on not only the identity field being sequential AFTER the sort (that is what I bet you didn't do) AND the UPDATE has to be done in the same order. I don't think it would be wise to rely on that. There's no way that I know of to guarantee that the UPDATE is done in the "proper" order.
This is a situation where is would be reasonable to use a cursor (as you suggest). With a cursor you don't need the identity field, just the GroupFld and you need to FETCH each row in the proper order.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

And this is where I fail miserably...I've never done a cursor, and don't have a clue how to even begin one.
I was thinking of a LOOP without a cursor...but a couple of the SQL guys around here say the same as you, that a cursor would be my best bet. But none of them know how either. We are a pathetic lot, we are. *ha*

I found some code online that might help, but I could use any resource you might happen to know of. I'm having to learn something new to get this one done.

Thank you,
Brenda

 

[donutman]

I haven't used a cursor for so long that I don't remember how to. I'd have to do what you will have to do...read about them in BOL and I don't have the time right now, sorry.
Maybe, vongrunt will come to the rescue. He may even know of a reliable way to do it without a cursor.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

thank you very much for all your help...and beleive me I understand not having time to look this up, I was only inquiring if you happened to already know someplace to read up on it. I've already found something...so I think I'm on the right track.

Thank you everyone for your help, and if anyone knows how to do a cursor...please chime in. I'm reading up, but still not sure if I'm on the right track. *haha*

thank you, thank you,
Antiskeptic