Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find non-overlapping time per day for staff productivity report... Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:06 PM
Points: 8, Visits: 31
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!
Post #1394733
Posted Monday, December 10, 2012 12:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,669, Visits: 2,203
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)

Internet ATM Machine
Post #1394744
Posted Monday, December 10, 2012 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:06 PM
Points: 8, Visits: 31
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?
Post #1394762
Posted Monday, December 10, 2012 2:07 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,669, Visits: 2,203
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)

Internet ATM Machine
Post #1394780
Posted Monday, December 10, 2012 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:06 PM
Points: 8, Visits: 31
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.
Post #1394787
Posted Monday, December 10, 2012 3:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,669, Visits: 2,203
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)

Internet ATM Machine
Post #1394799
Posted Monday, December 10, 2012 9:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:06 PM
Points: 8, Visits: 31
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.

Post #1394874
Posted Monday, December 10, 2012 10:30 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,669, Visits: 2,203
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)

Internet ATM Machine
Post #1394887
Posted Tuesday, December 11, 2012 3:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 23,215, Visits: 31,895
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1394990
Posted Tuesday, December 11, 2012 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:06 PM
Points: 8, Visits: 31
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_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
STAFF_ID int no 4 10 0 yes (n/a) (n/a) NULL
CASE_NO char no 10 NULL NULL yes no yes SQL_Latin1_General_CP1_CI_AS
SERVICE_DATE datetime no 8 NULL NULL yes (n/a) (n/a) NULL
ELAPSED_MINS int no 4 10 0 yes (n/a) (n/a) NULL
STARTTIME time no 3 8 0 yes (n/a) (n/a) NULL
ENDTIME time no 3 8 0 yes (n/a) (n/a) NULL

*/

Post #1395299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse