Schedule Adherence report

View previous topic View next topic Go down

Schedule Adherence report

Post  TheNJDevil on Wed Jun 25, 2014 11:56 am

I have 2 transaction fact tables. First one contains an activity key, employee key, start timestamp, and end timestamp that reflects a schedule. I have the same structure that contains the actual activities and timestamps worked. I am struggling with creating a single SQL statement to produce an adherence report.

As it was described to me, Adherence is the number of minutes that Actual perfectly overlaps Schedule. Am I oversimplifying in thinking this can be done in 1 SQL SELECT statement?

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Schedule Adherence report

Post  nathanjones77 on Fri Jun 27, 2014 6:50 am


Something like this? I assume here that the grain is activity * employee for both fact tables.

select activity, employee max(scheduledstart), max(scheduledend), max(actualstart), max(actualend) from --you can then add and subtract these as necessary to calculate the adherence.
(Select activity, employee, scheduledstart, scheduledEnd, null, null
ScheduleFact + dimensions
union
select activity, employee, null, null, actualstart, actualend
ActualFact + dimensions ) a
group by activity, employee

nathanjones77

Posts : 11
Join date : 2014-06-03
Location : Zurich

View user profile

Back to top Go down

Re: Schedule Adherence report

Post  TheNJDevil on Fri Jun 27, 2014 9:20 am

Breaking down each possible variation, I came up with the following. It seems to work to find the activity time range that falls between the scheduled time range.

Code:
CASE WHEN (a.ETIME>=s.STIME And a.STIME<=s.ETIME)
            THEN DateDiff(Minute, a.STIME,a.ETIME)  - CASE WHEN (a.STIME<s.STIME)
                                                  THEN DateDiff(Minute, a.STIME,s.STIME)
                                                  ELSE 0 END
            - CASE WHEN (a.ETIME>s.ETIME)
                    THEN DateDiff(Minute, s.ETIME,a.ETIME)
                    ELSE 0 END
            ELSE 0  END AS 'MinutesAdherence'

a is the activity table, s is the schedule table, STIME is start timestamp, ETIME is end timestamp

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Schedule Adherence report

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum