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 Tuesday, December 11, 2012 5:23 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
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!

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

*/



Your column ELAPSED_MINS has at least one null value, that's why it errors out. Just tested here at home.



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 #1395363
Posted Tuesday, December 11, 2012 6:54 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 am totally doing the happy dance right now.

Thank you!!!!
Post #1395389
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse