|
|
Posted by Roy Harvey on 12/02/06 01:35
>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.
It is technically correct to say that all times are after midnight, it
is just a matter of by how much. So the question is, just what is the
window for which StartTime always means shift 2 for the previous day?
Obviously it starts at 00:00:00 hours, but when does it end? 05:59:59?
Some other time? Without such a time I can not see any way to do
this.
My tendency would be to write the first WHEN clause of the CASE to
trap the after-midnight second-shift of yesterday cases based on
StartTime falling within that window. Then deal with the other cases
in the second and following WHEN clauses. Of course the CASE will
have to be written using correct syntax, but that is another issue
altogether.
Roy Harvey
Beacon Falls, CT
On 02 Dec 2006 01:10:05 GMT, Amy Smith <amoura00@hotmail.com> wrote:
>
>
>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 thats 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 ***
[Back to original message]
|