December 10, 2012 at 11:55 am
Hello! I am trying to determine the amount of direct service time *staff* spent during a given work day. Table looks as follows:
PATIENT_ID, STAFF_ID, SERVICE_DATE, BEGIN_TIME, END_TIME, ELAPSED_MINUTES
The reason I can't add the elapsed minutes per day is because sometimes they work with multiple patients at a time. I am looking to add up the elapsed minutes where there is no overlap in the begin/end times. Does this make sense? I would greatly appreciate any help. I am using SQL Server 2008 R2. Thank you!
December 10, 2012 at 12:15 pm
Question: If a staff member works with 2 patients at the same time, for the last 15 minutes of the work with patient1, and those 15 minutes are also the first 15 minutes of working with patient2, how much total time needs to be logged for this staff member ? I would think that wherever there's overlap, it would be from the beginning of the overall time window to the end thereof. However, you asked for data only for when there is no overlap. Is that indeed what you want? If so, try this and see if that gets you what you're looking for:
SELECT SD.STAFF_ID, SD.SERVICE_DATE, SUM(SD.ELAPSED_MINUTES) AS TTL_TIME
FROM STAFF_DATA AS SD
GROUP BY SD.STAFF_ID, SD.SERVICE_DATE
WHERE NOT EXISTS
(SELECT 1 FROM STAFF DATA AS S2
WHERE S2.STAFF_ID = SD.STAFF_ID
AND S2.SERVICE_DATE = SD.SERVICE_DATE
AND S2.BEGIN_TIME < SD.END_TIME
AND S2.END_TIME >= SD.END_TIME
)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 12:57 pm
Thanks for the reply! Maybe an example will help. I'm trying to find the overall staff minutes per day rather than the patient's minutes if you will...
STAFF, SERVICE_DATE, PATIENT, START_TIME, END_TIME, ELAPSED_MINUTES (PATIENT_MINS), STAFF_MINS
SUZY STAFF, 06/16/11, JOHNNY, 1000, 1030, 30--for staff_mins would count this as 30
SUZY STAFF, 06/16/11, DAVID, 1030, 1100, 30--for staff_mins would count this as 30
SUZY STAFF, 06/16/11, JACKSON, 1100, 1200, 60--overlap
SUZY STAFF, 06/16/11, MADDY, 1100, 1200, 60--overlap
SUZY STAFF, 06/16/11, KEVIN, 1115, 1200, 45--overlap
SUZY STAFF, 06/16/11, SARAH, 1100, 1200, 60--overlap
SUZY STAFF, 06/16/11, JUDY, 1300, 1330, 30--for staff_mins would count this as 30
The overall staff minutes that I would want to see for this staff on this day would be 150. Does that help?
December 10, 2012 at 2:07 pm
That's what I thought you might want. Now the next question. Is there any chance that a single record will span dates, such as a 3rd shift person NEIL working with patient GEORGE from 2330 to 0030 ? Or can we assume that all service is within a single calendar day? If the business expands, might the system need to handle data generated in different time zones ? Also, are the start and end times always on 15 minute boundaries (xx00, xx15, xx30, xx45) ?
Finally, give some thought to whether or not a staff can be acting on two (or more) activities with a given patient, and say, overlapping a monitoring process with a therapy process, logging both times for some overlap period for the same patient. These kinds of things can be critical in making the right assumptions about the logging data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 2:30 pm
Oh wow, great questions!
-Yes, there is a SERVICE_THRU_DATE. The time spans midnight when that field is not null.
-No need for different time zones.
-The start and end times can be at any time (not just on the 15 min mark).
-The staff can't bill for the same patient for 2 services at one time, however there are times one staff might work with a parent and one staff might work with a child, both billable for overlapping time for the same patient.
December 10, 2012 at 3:16 pm
Figures, that you'd need to span dates. I have some code that works for data that's date unique, but perhaps you can expand the time table involved once you see what I was up to. Take a look at the following:
CREATE TABLE #TIMES (
TOD time PRIMARY KEY CLUSTERED
)
;WITH HRS AS (
SELECT TOP (24) RIGHT('0' + CAST(ROW_NUMBER() OVER(ORDER BY number) - 1 AS varchar(2)), 2) AS HR
FROM master.dbo.spt_values
),
MINS AS (
SELECT TOP (60) RIGHT('0' + CAST(ROW_NUMBER() OVER(ORDER BY number) - 1 AS varchar(2)), 2) AS MINS
FROM master.dbo.spt_values
)
INSERT INTO #TIMES
SELECT H.HR + ':' + M.MINS
FROM HRS AS H
CROSS APPLY MINS AS M
CREATE TABLE #LOGGING_DATA (
STAFF_MBR varchar(15),
SERVICE_DATE date,
PATIENT varchar(15),
START_TIME time,
END_TIME time,
PATIENT_MINUTES int
)
INSERT INTO #LOGGING_DATA
SELECT 'SUZY STAFF', '06/16/11', 'JOHNNY', '10:00', '10:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'DAVID', '10:30', '11:00', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JACKSON', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'MADDY', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'KEVIN', '11:15', '12:00', 45 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'SARAH', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'JUDY', '13:00', '13:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JOSEPH', '13:15', '13:45', 15--overlap AND non-overlap counts as 15
;WITH MAX_DATA AS (
SELECT STAFF_MBR, SERVICE_DATE, START_TIME, MAX(END_TIME) AS MAX_END, MAX(PATIENT_MINUTES) AS MAX_MINS
FROM #LOGGING_DATA
GROUP BY STAFF_MBR, SERVICE_DATE, START_TIME
)
SELECT DISTINCT MD.STAFF_MBR, MD.SERVICE_DATE, T.TOD
INTO #INTERVALS
FROM MAX_DATA AS MD
LEFT OUTER JOIN #TIMES AS T
ON MD.START_TIME <= T.TOD
AND MD.MAX_END >= T.TOD
SELECT I1.STAFF_MBR, I1.SERVICE_DATE, COUNT(I1.TOD) / 2 AS PATIENT_MINS
FROM #INTERVALS AS I1
INNER JOIN #INTERVALS AS I2
ON I1.STAFF_MBR = I2.STAFF_MBR
AND I1.SERVICE_DATE = I2.SERVICE_DATE
AND I1.TOD <> I2.TOD
AND ABS(DATEDIFF(ms, I1.TOD, I2.TOD)) = 60000
GROUP BY I1.STAFF_MBR, I1.SERVICE_DATE
ORDER BY I1.STAFF_MBR, I1.SERVICE_DATE
DROP TABLE #LOGGING_DATA
DROP TABLE #TIMES
DROP TABLE #INTERVALS
It uses the Tally table concept to create a table of time values for a 24-hour day, then identifies all the 1 minute intervals that are covered one way or another by a given staff member. Finally, it counts the intervals, needing to divide by 2 as each pair of times that are 1 minute apart is just 1 interval. I added an additional line to the sample data so that I could test it for an overlapping, but not completely overlapping, interval.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 9:33 pm
I think this will work!
My times are currently in integer form, so I need to do some adjusting before I can run this.
Seriously thank you... It's actually quite genius.
December 10, 2012 at 10:30 pm
Don't forget to adjust for spanning a day, as you'll either have to add extra records to fit it into this paradigm, or you'll need to adjust the queries to take the date out of the GROUP BY clauses and filter the input data on a SERVICE_DATE and SERVICE_DATE_THRU basis that won't be your average everyday filter, and then expand the time values to full date-time values instead, and of course, expand the TIMES table to cover the entire range of dates your input data will cover. There may be other adjustments too, and even as is, it should be subjected to some rigorous testing to be sure it covers all the possibilities.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 11, 2012 at 3:18 am
Food for thought:
CREATE TABLE #LOGGING_DATA (
STAFF_MBR varchar(15),
SERVICE_DATE date,
PATIENT varchar(15),
START_TIME time,
END_TIME time,
PATIENT_MINUTES int
);
INSERT INTO #LOGGING_DATA
SELECT 'SUZY STAFF', '06/16/11', 'JOHNNY', '10:00', '10:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'DAVID', '10:30', '11:00', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JACKSON', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'MADDY', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'KEVIN', '11:15', '12:00', 45 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'SARAH', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'JUDY', '13:00', '13:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JOSEPH', '13:15', '13:45', 30 --overlap AND non-overlap counts as 15
--union all select 'SUZY STAFF', '06/16/11', 'JOSEPH', '23:45', '00:15', 30; -- Uncomment this row to see how it handles spanning a day
go
with
BaseData as (
select
STAFF_MBR,
SERVICE_DATE,
PATIENT,
START_TIME,
END_TIME,
PATIENT_MINUTES,
dt1 = datediff(mi,0,START_TIME),
dt2 = datediff(mi,0,END_TIME) + case when START_TIME < END_TIME THEN 0 ELSE 1440 END
from
#LOGGING_DATA
),
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eNumbers(n) as (select 0 union all
select row_number() over (order by (select null))
from e4 a cross join e2 b)
, UniqueData as (
select distinct
STAFF_MBR,
SERVICE_DATE,
dtn = dt1 + n
from
BaseData bd1
cross apply (select top (PATIENT_MINUTES + 1) n from eNumbers)dt(n)
)
, FinalBaseData as (
select
STAFF_MBR,
SERVICE_DATE,
dtn,
grpdtn = dtn - row_number() over (partition by STAFF_MBR, SERVICE_DATE order by dtn)
from
UniqueData
)
, FinalData as (
select
STAFF_MBR,
SERVICE_DATE,
TimeInMinutes = max(dtn) - min(dtn)
from
FinalBaseData
group by
STAFF_MBR,
SERVICE_DATE,
grpdtn
)
select
STAFF_MBR,
SERVICE_DATE,
TotalTime = sum(TimeInMinutes)
from
FinalData
group by
STAFF_MBR,
SERVICE_DATE
;
go
drop table #LOGGING_DATA;
go
December 11, 2012 at 1:13 pm
Steve and Lynn - Both of these are awesome... I'm a bit star-struck by you! 😉 I am a social work therapist by education, so I have been teaching myself for the past few years (so please bear with me).
Steve's ran without error. Need to follow up with your suggestions, but so far looking awesome!
Lynn - Yours ran perfectly with the table you created, but when I used my tables I received an error: Msg 1014, Level 15, State 1, Line 3 TOP clause contains an invalid value. I'd really like to see this work, too. Please let me know if there is anything obvious I'm missing. Thanks again to you both!
with
BaseData as (
select
STAFF_ID,
SERVICE_DATE,
CASE_NO,
STARTTIME,
ENDTIME,
ELAPSED_MINS,
dt1 = datediff(mi,0,STARTTIME),
dt2 = datediff(mi,0,ENDTIME) + case when STARTTIME < ENDTIME THEN 0 ELSE 1440 END
from
##TIMES --my table
),
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eNumbers(n) as (select 0 union all
select row_number() over (order by (select null))
from e4 a cross join e2 b)
, UniqueData as (
select distinct
STAFF_ID,
SERVICE_DATE,
dtn = dt1 + n
from
BaseData bd1
cross apply (select top (ELAPSED_MINS + 1) n from eNumbers) dt (n)
)
, FinalBaseData as (
select
STAFF_ID,
SERVICE_DATE,
dtn,
grpdtn = dtn - row_number() over (partition by STAFF_ID, SERVICE_DATE order by dtn)
from
UniqueData
)
, FinalData as (
select
STAFF_ID,
SERVICE_DATE,
TimeInMinutes = max(dtn) - min(dtn)
from
FinalBaseData
group by
STAFF_ID,
SERVICE_DATE,
grpdtn
)
select
STAFF_ID,
SERVICE_DATE,
TotalTime = sum(TimeInMinutes)
from
FinalData
group by
STAFF_ID,
SERVICE_DATE
;
go
/*--Not sure if this makes any difference:
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
STAFF_IDintno4100yes(n/a)(n/a)NULL
CASE_NOcharno10NULLNULLyesnoyesSQL_Latin1_General_CP1_CI_AS
SERVICE_DATEdatetimeno8NULLNULLyes(n/a)(n/a)NULL
ELAPSED_MINSintno4100yes(n/a)(n/a)NULL
STARTTIMEtimeno380yes(n/a)(n/a)NULL
ENDTIMEtimeno380yes(n/a)(n/a)NULL
*/
December 11, 2012 at 5:23 pm
kounslr (12/11/2012)
Steve and Lynn - Both of these are awesome... I'm a bit star-struck by you! 😉 I am a social work therapist by education, so I have been teaching myself for the past few years (so please bear with me).Steve's ran without error. Need to follow up with your suggestions, but so far looking awesome!
Lynn - Yours ran perfectly with the table you created, but when I used my tables I received an error: Msg 1014, Level 15, State 1, Line 3 TOP clause contains an invalid value. I'd really like to see this work, too. Please let me know if there is anything obvious I'm missing. Thanks again to you both!
withBaseData as (
select
STAFF_ID,
SERVICE_DATE,
CASE_NO,
STARTTIME,
ENDTIME,
ELAPSED_MINS,
dt1 = datediff(mi,0,STARTTIME),
dt2 = datediff(mi,0,ENDTIME) + case when STARTTIME < ENDTIME THEN 0 ELSE 1440 END
from
##TIMES --my table
),
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eNumbers(n) as (select 0 union all
select row_number() over (order by (select null))
from e4 a cross join e2 b)
, UniqueData as (
select distinct
STAFF_ID,
SERVICE_DATE,
dtn = dt1 + n
from
BaseData bd1
cross apply (select top (ELAPSED_MINS + 1) n from eNumbers) dt (n)
)
, FinalBaseData as (
select
STAFF_ID,
SERVICE_DATE,
dtn,
grpdtn = dtn - row_number() over (partition by STAFF_ID, SERVICE_DATE order by dtn)
from
UniqueData
)
, FinalData as (
select
STAFF_ID,
SERVICE_DATE,
TimeInMinutes = max(dtn) - min(dtn)
from
FinalBaseData
group by
STAFF_ID,
SERVICE_DATE,
grpdtn
)
select
STAFF_ID,
SERVICE_DATE,
TotalTime = sum(TimeInMinutes)
from
FinalData
group by
STAFF_ID,
SERVICE_DATE
;
go
/*--Not sure if this makes any difference:
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
STAFF_IDintno4100yes(n/a)(n/a)NULL
CASE_NOcharno10NULLNULLyesnoyesSQL_Latin1_General_CP1_CI_AS
SERVICE_DATEdatetimeno8NULLNULLyes(n/a)(n/a)NULL
ELAPSED_MINSintno4100yes(n/a)(n/a)NULL
STARTTIMEtimeno380yes(n/a)(n/a)NULL
ENDTIMEtimeno380yes(n/a)(n/a)NULL
*/
Your column ELAPSED_MINS has at least one null value, that's why it errors out. Just tested here at home.
December 11, 2012 at 6:54 pm
I am totally doing the happy dance right now.
Thank you!!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply