SQLTeam.com | Weblogs | Forums

Query for records that cross a certain time in a date range


#1

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


#2

If you're looking for a working solution, DDL & test data would be helpful.


#3

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
;

#4

hi bitsmed:

thanks for the input -- working through your solution into mine.