SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find non-overlapping time per day for staff productivity report...


Find non-overlapping time per day for staff productivity report...

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97195 Visits: 38988
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.

Cool
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)
kounslr
kounslr
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 31
I am totally doing the happy dance right now.

Thank you!!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search