Aggregating activity from the Fact table

Database

Aggregating activity from the Fact table

Hi All,

I have the below table structure and around 90 million records to process.

S_ID    Activity_Dt        Activity

S1        2015-01-03      Y

S1        2015-01-10      Y

S1        2015-01-17      N

S1        2015-01-24      N

S1        2014-01-31      Y  

The expected output is:

S_ID            From_Date     To_Date     Activity

S1                2014-01-01    2014-01-13   Y

S1                2014-01-14      2014-01-27 N

S1                2014-01-28      current_date Y

The column S_ID will have different values like S1,S2,S3... and each will have activity performed for all the weekend_date.

2 REPLIES

Re: Aggregating activity from the Fact table

Sorry the expected output is as below:

The expected output is:

S_ID            From_Date     To_Date     Activity

S1                2015-01-03    2015-01-16   Y

S1                2015-01-17      2015-01-30 N

S1                2015-01-31      current_date Y

Senior Apprentice

Re: Aggregating activity from the Fact table

This is quite similar to another post from today:

create SDC from a history table

You can use the same approach, just simplified:

SELECT S_ID, Activity_Dt
,COALESCE(MIN(Activity_Dt)
OVER (PARTITION BY S_ID
ORDER BY Activity_Dt
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) -1
, CURRENT_DATE)
,Activity
FROM
(
SELECT S_ID, Activity_Dt, Activity
,MIN(Activity)
OVER (PARTITION BY S_ID
ORDER BY Activity_Dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevActivity
FROM tab
QUALIFY Activity <> PrevActivity
OR PrevActivity IS NULL
) AS dt