Need help calculating current row depended on previous rows

Database
Fan

Need help calculating current row depended on previous rows

i all,

 

I am having a trouble writing an sql query. I have the below data:

 

Account

Date

Flag

Grace Period

1

2017-07-31

0

2017-03-31

1

2017-08-31

0

2017-03-31

1

2017-09-30

1

2017-11-30

1

2017-10-31

0

2017-11-30

1

2017-11-30

1

2018-02-28

1

2017-12-31

0

2018-02-28

1

2018-01-31

0

2018-02-28

1

2018-02-28

1

2018-04-30

1

2018-03-31

1

2018-05-31

1

2018-04-30

0

2018-05-31

1

2018-05-31

0

2018-05-31

1

2018-06-30

0

2018-05-31

1

2018-07-31

0

2018-05-31

1

2018-08-31

1

2018-10-31

 

 

And what i want is: Whenever the flag is set to 1 then the account has an additional grace period which is calculated as Grace Period End + 2 months.

During this new calculated period if a flag comes as 1, then do not calculated a new grace period and keep the current value.

Once the new calculated period is ended (Date > New Calculated Period) then if flag = 1 calculated the new grace period end. At the end, I want a cumulative sum of the valid Flag = 1.

 

To be more specific this is what the above data should look like

 

 

Account

Date

Flag

Grace Period

New Grace Period

Valid Flag Counter

1

2017-07-31

0

2017-03-31

-

 

1

2017-08-31

0

2017-03-31

-

 

1

2017-09-30

1

2017-11-30

2018-01-31

1

1

2017-10-31

0

2017-11-30

2018-01-31

1

1

2017-11-30

1

2018-02-28

2018-01-31

1

1

2017-12-31

0

2018-02-28

2018-01-31

1

1

2018-01-31

0

2018-02-28

2018-01-31

1

1

2018-02-28

1

2018-04-30

2018-06-30

2

1

2018-03-31

1

2018-05-31

2018-06-30 

2

1

2018-04-30

0

2018-05-31

2018-06-30 

2

1

2018-05-31

0

2018-05-31

2018-06-30 

2

1

2018-06-30

0

2018-05-31

2018-06-30 

2

1

2018-07-31

0

2018-05-31

2018-06-30 

2

1

2018-08-31

1

2018-10-31

2018-12-31 

3

1 REPLY
Junior Apprentice

Re: Need help calculating current row depended on previous rows

Hi,

 

I think the following will do what you want - it certainly seems to work with your sample data shown above (thanks for that, much easier to do this sort of stuff if we have an example of valid input/output).

 

I'll say right now that there might be a more efficient way of doing this, but I've concentrated on functionality for now.

 

I've also assumed that you'll be needing to do this for multiple values of 'account'.

 

For this testing I've loaded your sample data (input and output) into a table called 'tc1'

 

CREATE TABLE tc1
(account_ID INTEGER NOT NULL
,date_col DATE NOT NULL
,flag_col INTEGER NOT NULL
,grace_period DATE NOT NULL
,new_grace_period DATE 
,valid_flag_counter INTEGER
)
PRIMARY INDEX (account_id);

REPLACE VIEW vtc1
AS
SELECT account_id
   ,date_col
   ,flag_col
   ,grace_period
FROM tc1;


DELETE FROM tc1;
INSERT INTO tc1 VALUES(1,DATE '2017-07-31',0,DATE '2017-03-31',NULL,NULL);
INSERT INTO tc1 VALUES(1,DATE '2017-08-31',0,DATE '2017-03-31',NULL,NULL);
INSERT INTO tc1 VALUES(1,DATE '2017-09-30',1,DATE '2017-11-30',DATE '2018-01-31',1);
INSERT INTO tc1 VALUES(1,DATE '2017-10-31',0,DATE '2017-11-30',DATE '2018-01-31',1);
INSERT INTO tc1 VALUES(1,DATE '2017-11-30',1,DATE '2018-02-28',DATE '2018-01-31',1);
INSERT INTO tc1 VALUES(1,DATE '2017-12-31',0,DATE '2018-02-28',DATE '2018-01-31',1);
INSERT INTO tc1 VALUES(1,DATE '2018-01-31',0,DATE '2018-02-28',DATE '2018-01-31',1);
INSERT INTO tc1 VALUES(1,DATE '2018-02-28',1,DATE '2018-04-30',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-03-31',1,DATE '2018-05-31',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-04-30',0,DATE '2018-05-31',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-05-31',0,DATE '2018-05-31',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-06-30',0,DATE '2018-05-31',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-07-31',0,DATE '2018-05-31',DATE '2018-06-30',2);
INSERT INTO tc1 VALUES(1,DATE '2018-08-31',1,DATE '2018-10-31',DATE '2018-12-31',3);

 

The view shown above is simply so that I've got an object which only returns the 'input' data.

 

Create and populate a volatile table containing only the 'flag = 1' rows - these are the rows that trigger changes in processing.

 

 

CREATE SET VOLATILE TABLE vt1
AS
(
SELECT t.account_id
     ,t.date_col
	 ,t.grace_period
      ,LAST_DAY(ADD_MONTHS(grace_period,2)) AS egp_c1
	  ,CAST(NULL AS DATE) AS new_gp_date
	  ,CAST(NULL AS INTEGER) AS valid_flag_counter
FROM vtc1 AS t
WHERE flag_col = 1
)
WITH DATA
PRIMARY INDEX(account_id)
ON COMMIT PRESERVE ROWS;

 

The two updates that follow (both targeting the volatile table) sort out which 'flag = 1' rows need to be grouped together:

 

UPDATE vt1 FROM (SELECT a.account_id AS v_account_id
                       ,a.date_col AS v_date_col
                       ,MIN(b.egp_c1) AS egp_date_start
                 FROM vt1 AS a
                 INNER JOIN vt1 AS b
                    ON a.account_id = b.account_id
                    AND a.date_col BETWEEN b.date_col AND b.egp_c1
                 GROUP BY 1,2
                 ) AS dt1
  SET new_gp_date = egp_date_start
WHERE   account_id = v_account_id
  AND date_col = v_date_col ;
  
UPDATE vt1 FROM (SELECT account_id AS v_account_id,new_gp_date AS v_new_gp_date,MIN(egp_c1) AS v_egp_c1
                        ,ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY v_egp_c1) AS v_vfc
                 FROM vt1
                GROUP BY 1,2) AS dt1
SET new_gp_date = v_egp_c1
    ,valid_flag_counter = v_vfc
WHERE account_id = v_account_id
  AND new_gp_date = v_new_gp_date;

 

This final select joins the updated volatile table back to the original data to produce (I hope!) the result that you're looking for.

 

SELECT a.account_id
      ,a.date_col
      ,a.flag_col
      ,a.grace_period
      ,vt1.new_gp_date
      ,vt1.valid_flag_counter
FROM tc1 AS a
LEFT OUTER JOIN vt1
ON a.account_id = vt1.account_id
  AND a.date_col BETWEEN vt1.date_col AND vt1.egp_c1
ORDER BY 1,2
QUALIFY ROW_NUMBER() OVER(PARTITION BY a.account_id ,a.date_col ORDER BY  a.date_col) = 1
ORDER BY 1,2;

 

It's not pretty (!) but I think it produces the correct result.

 

Have a go and see whether it works for you.

 

Cheers,

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com