I have the below data in a table A which I need to insert into table B along with one computed column.
Account_No | Balance | As_on_date 1001 |-100 | 1-Jan-2013 1001 |-150 | 2-Jan-2013 1001 | 200 | 3-Jan-2013 1001 |-250 | 4-Jan-2013 1001 |-300 | 5-Jan-2013 1001 |-310 | 6-Jan-2013
In table B, there should be no of days to be shown when balance is negative and the date one which it has gone into negative.
So, for 6-Jan-2013, this table should show below data:
Account_No | Balance | As_on_date | Days_passed | Start_date 1001 | -310 | 6-Jan-2013 | 3 | 4-Jan-2013
Here, no of days should be the days when the balance has gone negative in recent time and not from the old entry.
I need to write a SQL query to get the no of days passed and the start date from when the balance has gone negative.
I tried to formulate a query using
Lag analytical function, but I am not succeeding.
How should I check the first instance of negative balance by traversing back using
first_value function was given a try but not getting how to partition in it based on negative value.
Any help or direction on this will be really helpful.
Here's a way to achive this using analytical functions.
INSERT INTO tableb WITH tablea_grouped1 AS (SELECT account_no, balance, as_on_date, SUM (CASE WHEN balance >= 0 THEN 1 ELSE 0 END) OVER (PARTITION BY account_no ORDER BY as_on_date) grp FROM tablea), tablea_grouped2 AS (SELECT account_no, balance, as_on_date, grp, LAST_VALUE ( balance) OVER ( PARTITION BY account_no, grp ORDER BY as_on_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) closing_balance FROM tablea_grouped1 WHERE balance < 0 AND grp != 0 --keep this, if starting negative balance is to be ignored ) SELECT account_no, closing_balance, MAX (as_on_date), MAX (as_on_date) - MIN (as_on_date) + 1, MIN (as_on_date) FROM tablea_grouped2 GROUP BY account_no, grp, closing_balance ORDER BY account_no, MIN (as_on_date);
- First, SUM is used as analytical function to assign group number to consecutive balances less than 0.
- LAST_VALUE function is then used to find the last -ve balance in each group
- Finally, the result is aggregated based on each group. MAX(date) gives the last date, MIN(date) gives the starting date, and the difference of the two gives number of days.
Demo at sqlfiddle.