Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find non-overlapping time per day for staff productivity report... Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 11, 2012 5:23 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 6:04 PM Points: 22,115, Visits: 29,089
 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 + nfrom 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 FinalBaseDatagroup by STAFF_ID, SERVICE_DATE, grpdtn)select STAFF_ID, SERVICE_DATE, TotalTime = sum(TimeInMinutes)from FinalDatagroup by STAFF_ID, SERVICE_DATE;go/*--Not sure if this makes any difference:Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource CollationSTAFF_ID int no 4 10 0 yes (n/a) (n/a) NULLCASE_NO char no 10 NULL NULL yes no yes SQL_Latin1_General_CP1_CI_ASSERVICE_DATE datetime no 8 NULL NULL yes (n/a) (n/a) NULLELAPSED_MINS int no 4 10 0 yes (n/a) (n/a) NULLSTARTTIME time no 3 8 0 yes (n/a) (n/a) NULLENDTIME 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.
Post #1395363
 Posted Tuesday, December 11, 2012 6:54 PM
 Forum 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

 Permissions