Aggregate event log to weekly overview

Question

Suppose I have the following table:

Table A: Event Log

ID        Step        WeekArrived        WeekCompleted
-------------------------------------------------------
1         A           1                  1
1         B           1                  3
2         B           3                  3

So in week 1, case 1 arrives. For this case, step A is completed in week 1, but step B is not. Step B is completed in week 3. Case 2 also arrives in week 3, and step B is completed in the same week (Step A wasn't needed for this case).

I would like to aggregate this event log into a weekly per step overview like this:

Table B: Weekly Overview

Week      Step        In                 Backlog               Out
------------------------------------------------------------------
1         A           1                  0                     1
1         B           1                  1                     0
2         A           0                  0                     0
2         B           0                  1                     0
3         A           0                  0                     0
3         B           1                  0                     2

For each week X and step Y, this table shows:

In: the number of cases that started step Y in week X
Backlog: the number of cases which arrived at step Y in week <= X, and are not yet completed at the end of week X.
Out: the number of cases that completed step Y in week X

How can I get from table A to table B?

What are the actual data types of `WeekArrived`and `WeekCompleted`? — dnoeth, Dec 04 '17 at 14:37

Juan Carlos Oropeza · Answer 1 · 2017-12-04T15:22:37.263

0

You need a list of weeks so you can evaluate if there is data on that week.

in sql server for example something like this How to generate a range of numbers between two numbers?

Then you join with your steps. Will work better if you have a separated table for steps too.

SELECT weeks.week_id, steps.step, 
       CASE WHEN s_in.WeekArrived IS NULL THEN 0 ELSE 1 END as step_in,
       CASE WHEN s_completed.WeekCompleted IS NULL THEN 0 ELSE 1 END as step_out,
       CASE WHEN s_pending.WeekArrived IS NULL THEN 0 ELSE 1 END as step_pending
FROM weeks
CROSS JOIN (SELECT DISTINCT step
            FROM event_log) as steps

LEFT JOIN event_Log as s_in
  ON weeeks.week_id = e.WeekArrived
 AND steps.step = e.step

LEFT JOIN event_Log as s_completed
  ON weeeks.week_id = e.WeekCompleted
 AND steps.step = e.step

LEFT JOIN event_Log as s_pending
  ON weeeks.week_id > e.WeekArrived
 AND weeeks.week_id < e.WeekCompleted
 AND steps.step = e.step

NOTE: Dont know what back log is.

edited Dec 04 '17 at 15:22

answered Dec 04 '17 at 13:59

Juan Carlos Oropeza

47,252
12
78
118

Thanks, I'll take a look! For week X and step Y, backlog should contain the number of cases which arrived at step Y in week <= X, and are not yet completed in week X. – John Doe Dec 04 '17 at 14:08
I add another join for backlog, but not sure about the logic. Please double check. – Juan Carlos Oropeza Dec 04 '17 at 14:12
I change the table alias because conflict with the field names. – Juan Carlos Oropeza Dec 04 '17 at 15:23

score 0 · Accepted Answer · answered Dec 04 '17 at 21:26

First, you need a way to get a list of week_ids, I used Teradata's system calendar. Then it's a Cross Join plus some conditional aggregation:

SELECT week_id, step,
   Sum(CASE WHEN week_id = WeekArrived THEN 1 ELSE 0 END) AS "in",
   Sum(CASE WHEN week_id >= WeekArrived AND week_id < WeekCompleted THEN 1 ELSE 0 END) as "backlog",
   Sum(CASE WHEN week_id = WeekCompleted THEN 1 ELSE 0 END) AS "out"
FROM
 ( -- get a lsit of week ids
   SELECT day_of_calendar AS week_id
   FROM sys_calendar.CALENDAR
   WHERE week_id
   -- calculate the range of week ids
   BETWEEN (SELECT Min(WeekArrived) FROM event_log)
       AND (SELECT Max(WeekCompleted) FROM event_log)
 ) AS weeks
CROSS JOIN
 (
   SELECT  *
   FROM event_log
 ) AS steps
GROUP BY week_id, step
ORDER BY week_id, step

Aggregate event log to weekly overview

2 Answers2