HI:
I have a table along the line of
EntryTime
ExitTime
PersonID
Im trying to query over a date range ie 1/1/2017 - 1/31/2017 and find if the count exceeds a certain number for any 24 period. .ie when a person checks, i need to get the number of people currently checked in that havent exited for 24 hours prior to the most recent checkin time. I need to run this as a report over a date range.
basically only so many people can be onsite for any rolling 24 hour period or an overage is flagged
any suggestions would be appreciated
If you're looking for a working solution, DDL & test data would be helpful.
This might get you started:
select punch_type
,punch_time
,personid
,person_time
,person_count-case when punch_type='In' then 1 else 0 end as from_person_count
,person_count-case when punch_type='Out' then 1 else 0 end as to_person_count
from (select b.punch_type
,case when b.punch_type='In' then a.entrytime else a.exittime end as punch_time
,a.personid
,dateadd(second,datediff(second,a.entrytime,a.exittime),cast('00:00:00' as time)) as person_time
,sum(case
when b.punch_type='In'
and c.entrytime<=a.entrytime
and c.exittime>=a.entrytime
then 1
when b.punch_type='Out'
and c.entrytime<=a.exittime
and c.exittime>=a.exittime
then 1
else 0
end
)
as person_count
from yourtable as a
cross apply (select * from (values('In'),('Out')) as b(punch_type)) as b
left outer join yourtable as c
on c.entrytime<=a.exittime
and c.exittime>=a.entrytime
where a.entrytime>=cast('2017-01-01' as datetime)
and a.entrytime<cast('2017-02-01' as datetime)
group by b.punch_type
,a.entrytime
,a.exittime
,a.personid
) as a
where person_time>=cast('16:00:00' as time)
or person_count>10
order by a.punch_time
;
hi bitsmed:
thanks for the input -- working through your solution into mine.