HELP!!!! Problem with finding overlapping timeframes ...

Question

Post reply

HELP!!!! Problem with finding overlapping timeframes ...

kbrown@pccsinc.org

Old Hand

Points: 366
More actions
July 19, 2004 at 10:48 am

#102115

Hey All,

I'm pretty new to SQL Server and, for some reason, I decided I could put together a database to handle our client services. The only problem is that these people want the thing to actually work. Go figure!

I have a need to check for overlapping time periods. So, if a Staffperson has these 5 records:

StaffID            ClientID          Date                TimeIn            TimeOut

1                      69                    6/24/2004      8:30                13:00

1                      74                    6/24/2004      13:00              15:00

1                      86                    6/24/2004      8:30                15:00

1                      78                    6/24/2004      8:30                15:00

1                      50                    6/24/2004      8:30                15:00

Which can be represented like this:

0830-----------1300---1500

|------------------------|

|------------------------|

|------------------------|

|----------------|

                 |-------|

How can I group the records in a select statement so that I know all these records are overlapping?

I have a SP that is similar to this it takes the records and groups into a Temp Table:

INSERT #Temp(IDENT, GUID)

SELECT MAX(CAST(ServiceProvisionGUID AS VARCHAR(36)))

FROM dbo.ServiceProvision

GROUP BY StaffID, [Date], TimeIn, TimeOut, NumberClients

HAVING NumberClients <> 99999 --some records need ignored

AND [Date] BETWEEN @BDate AND @EDate

Then the SP loops through each record of the temp table. It takes the GUID from the temp table and uses it to pull information from the ServiceProvision Table into several variables. Each variable associated with the current record of the temp table starts with @NC_. The following code looks at the currentrecord of the temp table and attempts to count all the records that have an overlapping time frame:

SELECT @Count = Count(*)

   FROM dbo.ServiceProvision SP

   WHERE StaffID = @NC_STFID

  AND [Date] = @NC_DATE

  AND (

    (TimeIn > @NC_TIN AND TimeIn < @NC_TOUT)

   OR (TimeOut > @NC_TIN AND TimeOut < @NC_TOUT)

   OR (TimeIn = @NC_TIN)

   OR (TimeOut = @NC_TOUT))

  AND NumberClients <> 99999

The value of @Count is then compared with the value of NumberClients in each overlapping record. If the numbers don't match, the record in ServiceProvision is updated with an exception code:

IF @Count <> @NC

   BEGIN

  UPDATE dbo.ServiceProvision

    SET Exception = ISNULL(Exception, 0) + 99, ExceptionReason = '*CRITICAL: NumberClients Mismatch; '

    WHERE StaffID = @NC_STFID

  AND [Date] = @NC_DATE

   AND (

     (TimeIn > @NC_TIN AND TimeIn < @NC_TOUT)

    OR (TimeOut > @NC_TIN AND TimeOut < @NC_TOUT)

    OR (TimeIn = @NC_TIN)

    OR (TimeOut = @NC_TOUT))

   AND Approved IS NULL

   AND NumberClients <> 99999

   END

The records from the above timeframes all have NumberClients = 5 ... as their should be. The SP correctly matches the NumberClients with 5 records when the variables from the Temp table are @NC_TIN = '6/24/2004 08:30' and @NC_TOUT = '6/24/2004 15:00'. When the variables are 0830 and 1300 or 1300 and 1500, then the program will only match an overlapping timeframe of 0830-1500. So...

When the temp record for 0830-1500 is compared, it finds these overlaps

0830-----------1300---1500

|------------------------|

|------------------------|

|------------------------|

|----------------|

                 |-------|

When the temp record for 1300-1500 is compared, it finds these overlaps

0830-----------1300---1500

|------------------------|

|------------------------|

|------------------------|

                 |-------|

When the temp record for 0830-1300 is compared, it finds these overlaps

0830-----------1300---1500

|------------------------|

|------------------------|

|------------------------|

|----------------|

Does anyone know how to group these records with overlapping timeframes in a better way or how to compare the overlaps more accurately or a different algorithm for finding overlapping timeframes?!? Please help.

Best regards,

Kyle Brown

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 1

I have probably misunderstood what you are trying to calculate, but the following SQL tells you how many overlaps each staff member has per day. You can change it as you want, to show anything.

create table #temp (guid uniqueidentifier, staffid integer, clientid integer, [date] datetime, timein datetime, timeout datetime)

insert into #temp values (newid(), 1, 69, '20040624', '08:30', '13:00')

insert into #temp values (newid(), 1, 74, '20040624', '13:00', '15:00')

insert into #temp values (newid(), 1, 85, '20040624', '08:30', '15:00')

insert into #temp values (newid(), 1, 78, '20040624', '08:30', '15:00')

insert into #temp values (newid(), 1, 50, '20040624', '08:30', '15:00')

select * from #temp

select staffid, [date], max(overlaps) as overlaps

from

(select t1.guid, t1.staffid, t1.[date], t1.clientid, count(*) as overlaps

from #temp t1

left join #temp t2 on t1.staffid = t2.staffid and t1.date = t2.date and t1.guid <> t2.guid

where (t2.timein >= t1.timein and t2.timein < t1.timeout) or (t2.timeout > t1.timein and t2.timeout <= t1.timeout)

group by t1.guid, t1.staffid, t1.[date], t1.clientid

) t3

group by staffid, [date]

I wasn't sure where NumberClients came from, so I ignored it

Hope this gives you an idea of how it can be done.

Peter

kbrown@pccsinc.org Old Hand Points: 366 More actions · Answer 2

Peter,

Thank you very much for your quick reply. Your solution is close I think, but I will need to do some testing on it to see if it can be adapted. Your solution gives the number of overlaps per day. I need to find the number of overlaps for any given time period during that day.

The numberclients is probably not important in determining the number of overlapping services, but it is used in the next step, which is to compare the number of overlaps with the NumberClients field to see if they are the same.

Basically, each service has a limit to the number of consumers that can legally be served at one time. For example, we cannot serve more than 4 clients when performing habilitation services in a community setting. So, this SP needs to verify that staff put the right number of clients on the paper documentation and that they do not serve more than the maximum numbers of clients. So, the NumberClients field in EACH record of a period where there are overlapping entries must match the number of overlapping entries for that period. Ideally, this should match with the number of distinct ClientID's, but I'm willing to settle for one step at a time.

I've been racking my brain over this forever. It's beginning to look like we need to find a different approach to group services.

Thanks again,

Kyle.