Link to home
Start Free TrialLog in
Avatar of Colin Hart
Colin Hart

asked on

8 hour Continual coverage based on Time in and Time Out

I have a table that looks like this

FacID    EmployeeID       WorkDate           StartTime         EndTime
1101            150                    04/01/2017          8:00                   13:00
1101            155                    04/01/2017          11:00                 19:00
1101            150                    04/02/2017          8:00                   10:00
1101            155                    04/02/2017          9:30                   15:00

I need to write a query that would highlight days where there is NOT 8 hours of continual coverage.  So in the example above, 04/01/2017 has coverage between the two employees from 8:00 until 19:00 which would be 11 hours so that is good.  For 04/02/2017 the coverage between the two employees was from 8:00 until 15:00 which is only 7 hours so it should be flagged.    

One last thing is that my work day starts from 7am until 6:59:59 the following day.
Avatar of HainKurt
HainKurt
Flag of Canada image

what is starttime and endtime column datatype?
Avatar of Colin Hart
Colin Hart

ASKER

the STARTTIME and ENDTIME fields are actually DATETIME fields and contain the date
what is qry that gives above result
I am looking to have a query that would just tell me the date when there are no 8 hour blocks of coverage.
something like this

with ts as (
select l.*, row_number() over (partition by workdate order by starttime) rn from logs l
),
te as (
select l.*, row_number() over (partition by workdate order by endtime desc) rn from logs l
)
select ts.*, te.* from ts inner join te on ts.workdate=te.workdate and ts.rn=1 and te.rn=1
where datediff(hour, te.workdate, ts.workdate)<11

Open in new window

I can't seem to get that work work
here is a screenshot of what it is returningUser generated image
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
works perfect, thanks for the help
may not be perfect...

you may need left join in case there is no end record
something like this

with ts as (
select l.*, row_number() over (partition by workdate order by starttime) rn from logs l
),
te as (
select l.*, row_number() over (partition by workdate order by endtime desc) rn from logs l
)
select ts.*, te.* from ts left join te on ts.workdate=te.workdate and ts.rn=1 and te.rn=1
where te.rn is null or datediff(hour, ts.starttime, te.endtime)<11

Open in new window