Schedule Adherence report

[TheNJDevil]

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?

[nathanjones77]

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

[TheNJDevil]

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

[Sponsored content]