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

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:

Total the hours per DateKey e.g 20210101 total hours worked is 4.250000.
filter out the unpaid lunch time ( 30mins ) from the total work hours if the segment per datekey contains lunch e.g 20210102 total hours worked is 6.750000.

CodePudding user response：

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

CodePudding user response：

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