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.
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.
what is starttime and endtime column datatype?
ASKER
the STARTTIME and ENDTIME fields are actually DATETIME fields and contain the date
what is qry that gives above result
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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