Forming a sequence of actions from recurring events

Sam324 0
Viewed 32

I have a table that looks like this:

ID          TARGET_ACTION              TARGET_DATE 
       
366                    0                21.04.2021      
186                    1                03.04.2021
929                    0                14.04.2021
366                    1                17.04.2021

Each ID in this table can be repeated and have a different TARGET_ACTION value for a different date. I want to form a sequence of actions for each id, dividing it into weeks so that it looks like this:

ID          01.04.2021-07.04.2021    08.04.2021-14.04.2021   15.04.2021-21.04.2021 
       
366                    0                       0                       1
186                    1                       1                       0
929                    0                       1                       0

How can I do that?

oracle sql
1 Answers

You could use pivoting in this case, though for the whole year there would be 52 week columns. First, your sample data:

WITH
    a_table AS
        (
            Select 366  "ID",   0 "TARGET_ACTION",  To_Date('21.04.2021', 'dd.mm.yyyy') "TARGET_DATE" From Dual Union All
            Select 186  "ID",   1 "TARGET_ACTION",  To_Date('03.04.2021', 'dd.mm.yyyy') "TARGET_DATE" From Dual Union All
            Select 929  "ID",   0 "TARGET_ACTION",  To_Date('14.04.2021', 'dd.mm.yyyy') "TARGET_DATE" From Dual Union All
            Select 366  "ID",   1 "TARGET_ACTION",  To_Date('17.04.2021', 'dd.mm.yyyy') "TARGET_DATE" From DUAL
        ),

Next thing to do is to join your dataset with some kind of time dimension table. It is a very good thing to have for different uses. The link to how to create one is here: http://oracleolap.blogspot.com/2011/01/script-for-time-dimension-table.html
Instead, I've created a small cte just with weeks from the question:

  weeks AS
    (
        SELECT 14 "WEEK_NUM", To_Date('01.04.2021', 'dd.mm.yyyy') "WEEK_STARTS", To_Date('07.04.2021', 'dd.mm.yyyy') "WEEK_ENDS" From Dual Union All
        SELECT 15 "WEEK_NUM", To_Date('08.04.2021', 'dd.mm.yyyy') "WEEK_STARTS", To_Date('14.04.2021', 'dd.mm.yyyy') "WEEK_ENDS" From Dual Union All
        SELECT 16 "WEEK_NUM", To_Date('15.04.2021', 'dd.mm.yyyy') "WEEK_STARTS", To_Date('21.04.2021', 'dd.mm.yyyy') "WEEK_ENDS" From Dual 
    )

Now, if you get the weeks from your data and join them with time dimension you could pivot the resulting dataset. Here is the mainSQL with the result.

SELECT DISTINCT
    ID, W14, W15, W16
FROM
    (
        SELECT
            t.ID "ID",
            t.TARGET_ACTION "TARGET_ACTION",
            To_Char(t.TARGET_DATE, 'WW') "WEEK_NUM",
            w.WEEK_STARTS "WEEK_STARTS", 
            w.WEEK_ENDS "WEEK_ENDS"
        FROM
            a_table t
        INNER JOIN
            weeks w ON(To_Char(w.WEEK_NUM) = To_Char(t.TARGET_DATE, 'WW'))
    )
PIVOT
    (
        Count(WEEK_NUM)
        FOR WEEK_NUM IN(14 "W14", 15 "W15", 16 "W16")
    )
--  
--  R e s u l t
--  
--          ID        W14        W15        W16
--  ---------- ---------- ---------- ----------
--         929          0          1          0 
--         366          0          0          1 
--         186          1          0          0

Regards...

d r0
Related