SQL Server - Calculate time between dates with work shifts

Question

Problem: Calculate the amount of time between two dates with only using the shift dates. The shift dates are the only allowed time to count time. So in the data example below, I'd want to see the time difference for each of the #Rejections records independently. Ideally, I'd like to also take into account the lunch break but I don't if that makes this even harder to handle.

Data:

CREATE TABLE #Shifts
(
  ShiftId INT
  ,StartTime DECIMAL(6,2)
  ,EndTime DECIMAL(6,2)
  ,LunchStart DECIMAL(6,2)
  ,LunchEnd DECIMAL(6,2)
);

INSERT INTO #Shifts VALUES (1, 6.00, 16.75, 11.75, 12.50) /*6am to 4:45pm*/
INSERT INTO #Shifts VALUES (2, 17.00, 3.75, 23.00, 23.75) /*5pm to 3:45am (next day)*/
INSERT INTO #Shifts VALUES (3, 5.00, 17.75, 12.00, 12.75) /*5am to 5:45pm*/

CREATE TABLE #Rejections
(
  JobId INT
  ,WeldId INT
  ,IndicationNum INT
  ,FirstRejectedDate DATETIME
  ,LastAcceptedDate DATETIME
);

INSERT INTO #Rejections VALUES (500, 700, 2, '2017-01-03 22:35:31.000', '2017-01-04 01:38:16.000')
INSERT INTO #Rejections VALUES (500, 701, 3, '2017-01-04 01:48:55.000', '2017-01-06 09:21:11.000')

I am seeking some assistance on how to solve this problem. I am a novice at SQL Server and this problem has me totally stumped. I don't even know where to begin. I am using SQL Server 2008 R2 if that helps for available commands. Can someone please help me figure out how to achieve this?

Can you explain and provide an example of your desired output? — SqlZim, Jan 18 '17 at 22:53
Are you actually storing start/end times as a decimal? If you store them as `datetime`, you could at least do a `datediff` on the values. After that, you can do `datediff` to see how long lunch was, and subtract that from the overall time spent. You're making this harder by doing it the way you have shown above, *especially* when your shift crosses over more than one day. — R. Richards, Jan 18 '17 at 23:20
The ERP system I am using has them stored a decimal. I disagree with their decision. I assume I'd have to convert it to a datetime first. — Travyguy9, Jan 19 '17 at 14:09

score 0 · Answer 1 · edited May 23 '17 at 12:01

0

It's really better to use datetime2 (not datetime as suggested in comments) if possible - though I understand that in your situation that it's not. If you do then the answer is simple & easy to read...

SELECT DATEDIFF(mi, StartTime, EndTime) - DATEDIFF(mi, LunchStart, LunchEnd) 
FROM #shifts

I know it's not applicable to you, but I wanted to show it for others, in comparison to the monstrous code that follows. It's pretty ugly but possible to do these calculations in decimal format. This code will give you length of lunch break (minutes), total minutes of shift, and then that total broken down into hours & minutes, so you can use whatever you want.

SELECT  (CASE WHEN LunchEnd < LunchStart THEN 24 ELSE 0 END +
        LunchEnd - LunchStart) * 60 AS LunchMins
    , (CASE WHEN EndTime < StartTime THEN 24 ELSE 0 END +
        EndTime 
        - StartTime 
        - (CASE WHEN LunchEnd < LunchStart THEN 24 ELSE 0 END + LunchEnd - LunchStart))
        * 60 AS TotalShiftMins
    , ROUND((CASE WHEN EndTime < StartTime THEN 24 ELSE 0 END +
        EndTime 
        - StartTime 
        - (CASE WHEN LunchEnd < LunchStart THEN 24 ELSE 0 END + LunchEnd - LunchStart))
        ,0) AS TotalShiftHoursOnly
    , ((CASE WHEN EndTime < StartTime THEN 24 ELSE 0 END +
        EndTime 
        - StartTime 
        - (CASE WHEN LunchEnd < LunchStart THEN 24 ELSE 0 END + LunchEnd - LunchStart))
        * 60) % 60 AS TotalShiftMinsOnly
    , *
FROM #Shifts

PS I'm presuming you meant the #Shifts table and not the #Rejections table? The data in the #Rejections table doesn't appear to match with what you're asking.

edited May 23 '17 at 12:01

Community

1
1

answered Jan 19 '17 at 00:21

reedstonefood

131
2
15

Like I said above in the comments, I am stuck using decimals for the shift times because of the ERP system I am using. I am not a fan of how it is setup. – Travyguy9 Jan 19 '17 at 14:10
OK, so is this answer helpful? I could do one that returns hours & minutes across dates & includes lunch hours if you'd like? Is the output I'm proposing in the right format for you or do you want it another way? – reedstonefood Jan 19 '17 at 16:58
I think hours & minutes is what I am needing. Yours you have above is just telling me how long each shift was and it isn't handling the shift that crosses midnight (#2) – Travyguy9 Jan 19 '17 at 17:28
1

I've changed the answer in response to your comments. Hope it helps. – reedstonefood Jan 19 '17 at 19:58
I see that. It does for getting the shift times. Thank you for that. I think I may have asked this question bad though. I am trying to calculate the total time between the FirstRejectedDate and LastAcceptedDate in the #Rejections table. I'd want them calculated independently of each other. In other words, treat each row as their own. If I could, I would just do a simple DATEDIFF on them and be good but I need to know the amount of time was worked with the shifts given above. Basically, how much time between the dates that overlap with the shift times given above. – Travyguy9 Jan 19 '17 at 20:02
I think it's best you ask a new question (and link to this one) & be a bit clearer in what you're asking. I think my answer could still be useful for some people so I'd rather not edit it again. It might also help if you explain what real life entity a Rejection represents, it's not immediately clear. – reedstonefood Jan 19 '17 at 20:11
In my defense, I did say this -- So in the data example below, I'd want to see the time difference for each of the #Rejections records independently – Travyguy9 Jan 19 '17 at 20:12

SQL Server - Calculate time between dates with work shifts

1 Answers1