July 20, 2004 at 5:57 am
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
July 20, 2004 at 8:52 am
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.
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply