All Forums Database
09 Jan 2015
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.
 

09 Jan 2015

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

dnoeth 4628 posts Joined 11/04
09 Jan 2015

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

 

Dieter

You must sign in to leave a comment.