Query to get the previous date in Oracle in a specific scenario

advertisements

I have the below data in a table A which I need to insert into table B along with one computed column.

TABLE A:

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

Table B:

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 LAG function?

Even the 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.