Help with query 5

[shannanl]

I have a table that has two fiels, Time_in and Time_out. I need to figure out some information about the entries in those fields. They would look like this:

Time_in Time_out
7/13/2005 8:00:00 AM 7/13/2005 5:00:00 PM 7/14/2005 8:00:00 AM 7/14/2005 5:00:00 PM 7/15/2005 8:00:00 AM 7/15/2005 5:00:00 PM 7/16/2005 8:00:00 AM 7/16/2005 5:00:00 PM 7/17/2005 8:00:00 AM 7/17/2005 5:00:00 PM 7/20/2005 8:00:00 AM 7/20/2005 5:00:00 PM 7/21/2005 8:00:00 AM 7/21/2005 5:00:00 PM 7/22/2005 8:00:00 AM 7/22/2005 5:00:00 PM 7/23/2005 8:00:00 AM 7/23/2005 5:00:00 PM

Lets take the first entry. I need to know how many of the total minutes between Time_in and Time_out were worked between the hours of 7:01 am and 5:00 pm, 5:01 pm and 11:00 pm and 11:01 pm and 7:00 am.

I used this to get the minutes elapsed between the two:

SELECT Time_in, Time_out, DATEDIFF([minute], Time_in, Time_out) AS MINUTES, DATEDIFF([Minute], Time_in, '5:00:00 PM 7 / 13 / 2005 ') AS FIRSTSHIFT
FROM Timeattendance_T

This does return the total minutes worked and the total elapsed between Time_in and 5:00 pm, but if the day is not 7/13/2005 it messes it up and it does not give me the minutes from 7-5, only Time_in to 5:00. I guess I need to replace the 7/13/2005 with a date part of Time_out? Anyway, I am rambling. I would like the output something like this:

Time_in Time_out 1st Shift 2nd Shift 3rd Shift
08:00 AM 8:00 PM 9 3 0
06:00 AM 3:00 PM 8 0 1

I am at a loss as to how to do this. ANY HELP would be greatly appreciated.

Thanks,

Shannan

 

[johpje]

shannanl,

Using some plain old numbers ans some basic matematics, you can come to a quite simple solution:

CREATE PROCEDURE CalculateShifts
@Offset_in int, @Offset_out int, @Shift1 int OUTPUT ,@Shift2 int OUTPUT , @Shift3 int OUTPUT 
AS
DECLARE @Shift1_End as int
DECLARE @Shift2_End as int
DECLARE @Shift3_End as int

BEGIN

  SET @Shift1_End = 600
  SET @Shift2_End = 960
  SET @Shift3_End = 1440  
  IF (@Offset_out <= @Offset_in)
  BEGIN
    SET @Offset_out = @Offset_out + @Shift3_End 
  END

  IF (@Offset_in< @Shift1_End)
  BEGIN
    IF @Offset_out < @Shift1_End
    BEGIN
      SET @Shift1 = @Shift1 + @Offset_out - @Offset_in
      GOTO ExitProc
    END
    ELSE
    BEGIN
      SET @Shift1 = @Shift1 + @Shift1_End - @Offset_in
      SET @Offset_in = @Shift1_End      
    END
  END

  IF (@Offset_in< @Shift2_End)
  BEGIN
    IF @Offset_out < @Shift2_End
    BEGIN
      SET @Shift2 = @Shift2 + @Offset_out - @Offset_in
      GOTO ExitProc
    END
    ELSE
    BEGIN
      SET @Shift2 = @Shift2 + @Shift2_End - @Offset_in
      SET @Offset_in = @Shift2_End      
    END
  END


  IF (@Offset_in< @Shift3_End)
  BEGIN
    IF @Offset_out < @Shift3_End
    BEGIN
      SET @Shift3 = @Shift3 + @Offset_out - @Offset_in
      GOTO ExitProc
    END
    ELSE
    BEGIN
      SET @Shift3 = @Shift3 + @Shift3_End - @Offset_in
      SET @Offset_in = @Shift3_End      
    END
  END
ExitProc:
END
GO

CREATE PROCEDURE GetShiftTimes 
@time_in smalldatetime, @time_out smalldatetime
AS
DECLARE @basedate as smalldatetime
DECLARE @Offset_in as int
DECLARE @Offset_out as int
DECLARE @Shift1 as int
DECLARE @Shift2 as int
DECLARE @Shift3 as int

DECLARE @Shift1_End as int
DECLARE @Shift2_End as int
DECLARE @Shift3_End as int

BEGIN

  SET @Shift1_End = 600
  SET @Shift2_End = 960
  SET @Shift3_End = 1440
  SET @basedate =  convert (smalldatetime, convert (varchar(10), dateadd(dd, -1, @time_in), 21) + ' 07:00:00', 21)
  SET @Offset_in = datediff(mi,@basedate,@time_in) % @Shift3_End 
  SET @Offset_out = datediff(mi,@basedate,@time_out) % @Shift3_End 

  SET @Shift1 = 0
  SET @Shift2 = 0
  SET @Shift3 = 0

  IF (@Offset_out <= @Offset_in)
  BEGIN
    EXEC CalculateShifts 0, @Offset_out, @Shift1 OUTPUT, @Shift2 OUTPUT, @Shift3 OUTPUT
    EXEC CalculateShifts @Offset_in, @Shift3_End, @Shift1 OUTPUT, @Shift2 OUTPUT, @Shift3 OUTPUT
  END
  ELSE
  BEGIN
    EXEC CalculateShifts @Offset_in, @Offset_out, @Shift1 OUTPUT, @Shift2 OUTPUT, @Shift3 OUTPUT
  END
    
  SELECT @Shift1, @Shift2, @Shift3
END

you just call following SP:

EXEC getshifttimes '2005-11-07 10:00:00', '2005-11-07 23:00:00'

returns: 480, 360, 0

also shifts that happen overnight and connecting to the next day's shift are handled:

EXEC getshifttimes '2005-11-07 22:00:00', '2005-11-07 08:00:00'

returns: 60, 60, 480

(off course; the 60 minutes of the first shift are actualy of the next day's first shift)

Only time registers that last longer than 24 hours are handled incorrectly

ex:
'2005-11-07 20:00:00', '2005-11-08 21:00:00' = 25 hours
is handled as
'2005-11-07 20:00:00', '2005-11-08 21:00:00' = 1 hour

But you can adjust the calculations to cover this problem...


I hope this helps,
Regards,
Johpje.

 

[shannanl]

Johpje,

That seems to work perfectly. I appreciate the help. Also, Maswien I really appreciate the help and persistance. I am sure it is something I am doing wrong. I will keep working on your query. Thanks guys.

Shannan

 

[shannanl]

Johpje,

I have one last question. I want to set 1st shift at 7:00:01 am to 3:00:00 pm, 2nd as 3:00:01 pm to 11:00:00 pm and 3rd as 11:00:01 pm to 7:00:00 am. What values would I change in the SP and to what? I belive my original query was 1st from 7-5 and 2nd as 5-11.

Thanks in advance for all of your help.

Shannan

 

[johpje]

shannanl,

if you change following lines in both procedures:
SET @Shift1_End = 601
SET @Shift2_End = 961
SET @Shift3_End = 1441

that should do the trick.

Sorry for the late reply!

 

[shannanl]

Johpje,

Thank you for the reply. Sorry to just get back with you. I have been out of town for a week.

Thanks,

Shannan