Need Help Getting The Day Shift After Midnight

Hello there,
I am having a small problem which been challenging me for few days and
need help or advice.

I am trying to calculate the day-shift for employees based on the time
they started and finish working, I will only have 2 shifts 1 or 2 .
Shift one changes based on the location however any thing else is shift
2. The problem I am having is when someone signed in after midnight; I
need to report his time under shift 2 for the previous day date. So if
he signs at 12:30 AM on 12-12-2006, I need to report that as shift 2 on
12-11-2006 and that’s where my problem is. Is there a way to subtract
the date by 1. I am using SQL Server and here is a simplified tables I
am working with:


Employee table
EmployeID LocationID StartTime EndTime
123 555 11:00:00 AM 3:00:00 PM
183 559 7:00:00 AM 11:00:00 AM
…



Shift table
ShiftNumber LocationID StartTime EndTime
1 555 7:00:00AM 2:00:00PM
2 555 2:00:00PM 12:00:00AM
1 559 6:00:00AM 4:00:00PM
…..


So I am trying something like

CASE WHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationID
AND
(TR.StartTime>=StartTime
AND
TR.EndTime<= EndTime)
) =1 THEN 1
ELSE
WHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationID
AND
(TR.StartTime>=StartTime
AND
TR.EndTime<= EndTime)
) =2 THEN 2
ELSE
?????????????? NEED HELP HERE WHEN EMPLOYEE SIGN AFTER MIDNIGHT
END
)
FROM

Employee TR

INNER JOIN Shift ON LocationID = TR.LocationID








*** Sent via Developersdex http://www.developersdex.com ***