MSSQL 2000 Stored Procedure to Split Shift Times
- by JClaspill
I am being asked to alter a system to include the ability to have pay differentials based on hours worked. The old method included a stored procedure (MSSQL2000 db) that did the basics, but simply knows the start and end of every shift.
So, this is the information I start with:
EMPLOYEE | TYPE | HOURS | INSTAMP | OUTSTAMP
Dave | Hourly | 8.643055 | 2011-01-08 07:57:35.557 | 2011-01-08 16:36:10.120
And I need to turn that into something like:
EMPLOYEE | TYPE | HOURS | INSTAMP | OUTSTAMP
Dave | Hourly | 4.00 | 2011-01-08 08:00:00.000 | 2011-01-08 12:00:00.000
Dave | ShiftDiff1 | 4.50 | 2011-01-08 12:00:00.000 | 2011-01-08 16:30:00.000
The ShiftDiff's range from hours to certain days, to a combo of both.
Should I try to make the SQL2000 SP do this or pass the info back to my ASP.NET(C#) app and let it handle it, then send back?
