Total the hours worked, then filter out 30mins where the segment per datekey contains lunch

user_15087637 0
Viewed 61

I have a table called Shifts, below is a sample of the data

ID Key  DateKey  Start                End                  Hours     Segment 
1  1001 20210101 2021-01-01 09:00:00  2021-01-01 09:00:00  4.000000  On-Call
2  1001 20210101 2021-01-01 11:00:00  2021-01-01 11:15:00  0.250000  Break
3  1001 20210102 2021-01-02 13:00:00  2021-01-01 19:00:00  6.000000  On-Call
4  1001 20210102 2021-01-02 15:00:00  2021-01-01 15:15:00  0.250000  Break
5  1001 20210102 2021-01-02 17:00:00  2021-01-01 17:30:00  0.500000  Lunch
6  1001 20210103 2021-01-03 09:00:00  2021-01-03 16:00:00  7.000000  On-Call
7  1001 20210103 2021-01-03 11:00:00  2021-01-03 11:15:00  0.250000  Break
8  1001 20210103 2021-01-03 13:00:00  2021-01-03 13:30:00  0.500000  Lunch
9  1002 20210104 2021-01-04 09:00:00  2021-01-04 09:00:00  4.000000  On-Call
10 1002 20210104 2021-01-04 11:00:00  2021-01-04 11:15:00  0.250000  Break
11 1002 20210105 2021-01-05 07:00:00  2021-01-05 14:00:00  7.000000  On-Call
12 1002 20210105 2021-01-05 09:00:00  2021-01-05 09:15:00  0.250000  Break
13 1002 20210105 2021-01-05 11:00:00  2021-01-05 11:30:00  0.500000  Lunch

I'm trying to:

  1. Total the hours per DateKey e.g DateKey 20210101 and ID 1 and 2 total hours worked is 4.250000.

  2. After totalling the hours worked for that day subtract 30mins from the total work hours if the segment for that datekey contains lunch e.g 20210102 total hours worked ( ID 3,4,5 ) is 6.750000 subtract 30mins.

sql-server sql
2 Answers
--Group data on datekey, then sum it without lunch 
select datekey,sum(hours)[total]
from newtbl
where segment!='lunch'
group by datekey
user_155124180

to find total hours per columns, I would use something like this:

Select Key,sum(hours) from Shifts where DateKey == 20210101;

to exclude the lunchtime from total work:


SELECT  key,sum(hours) FROM Shifts where  seg <> "lunch" GROUP by key
user_134203340
Related