6 Replies Latest reply: Sep 15, 2013 9:53 AM by chris227 RSS

    Query for Getting previous date in oracle in specifc scenario

    SwapK

      I have the below data in a table A which i need to insert into table B along with one compute 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 getting 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.

      Thanks

        • 1. Re: Query for Getting previous date in oracle in specifc scenario
          Solomon Yakobson

          Post create & populate table statements and query you have so far.

           

          Sy.

          • 2. Re: Query for Getting previous date in oracle in specifc scenario
            Frank Kulash

            Hi,

             

            Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the complete results you want from that data.  Do you want just the 1 row of output you posted, or do you want as many rows of output as you have in the table?  Since the very first row has a negative balance, does that count as "going negative"?
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

             

            As Solomon said, post what you've written so far.  LAG is a good start: with LAG you can see if a balance "goes negative", but you'll need something else, such as LAST_VALUE to find when that happened most recently.

            • 3. Re: Query for Getting previous date in oracle in specifc scenario
              Stew Ashton

              I think you can apply the Tabibitoshan method here. The query is more complex but performs better than other methods. For an explanation, see Tabibitosan method tutorial by Aketi Jyuuzou


              The idea behind the method is to identify "continuous" records without gaps. Here I identify records that are "continuous" because they contain an uninterrupted series of negative balances.


              drop table t;

              CREATE TABLE t (Account_No , Balance , As_on_date) AS SELECT

              1001 ,-100 , to_date('1-Jan-13', 'DD-MON-YY') FROM dual UNION ALL SELECT

              1001 ,-150 , to_date('2-Jan-13', 'DD-MON-YY') FROM dual UNION ALL SELECT

              1001 , 200 , to_date('3-Jan-13', 'DD-MON-YY') FROM dual UNION ALL SELECT

              1001 ,-250 , to_date('4-Jan-13', 'DD-MON-YY') FROM dual UNION ALL SELECT

              1001 ,-300 , to_date('5-Jan-13', 'DD-MON-YY') FROM dual UNION ALL SELECT

              1001 ,-310 , to_date('6-Jan-13', 'DD-MON-YY') FROM dual;

               

              SELECT Account_No, Balance, to_char(As_on_date, 'DD-MON-YYYY') as_on_date,

              CASE

                WHEN balance < 0

                THEN row_number() OVER(PARTITION BY account_no, grp ORDER BY as_on_date)

              END days_passed,

              CASE

                WHEN balance < 0

                THEN to_char(

                  first_value(as_on_date) OVER(PARTITION BY account_no, grp ORDER BY as_on_date),

                  'DD-MON-YYYY'

                )

              end start_date

              from (

                SELECT Account_No, Balance, As_on_date,

                CASE WHEN balance >= 0 THEN NULL ELSE

                  row_number() OVER(PARTITION BY account_no ORDER BY as_on_date) -

                  sum(CASE WHEN balance < 0 THEN 1 ELSE 0 END) OVER(PARTITION BY account_no ORDER BY as_on_date)

                end grp

                FROM t

              )

              order by account_no, As_on_date;


              ACCOUNT_NOBALANCEAS_ON_DATEDAYS_PASSEDSTART_DATE
              1001-10001-JAN-2013101-JAN-2013
              1001-15002-JAN-2013201-JAN-2013
              100120003-JAN-2013
              1001-25004-JAN-2013104-JAN-2013
              1001-30005-JAN-2013204-JAN-2013
              1001-31006-JAN-2013304-JAN-2013
              • 4. Re: Query for Getting previous date in oracle in specifc scenario
                SwapK

                Thank you StewAshton...quite helpful answer...but row number didn't give the correct number of days if there is no record in consecutive days..like if after 1-jan there is entry for 5th Jan,then no of days should be shown as 5 days on 5th jan,but it will show row number output as 2 due to 2 entries..I think subtraction of min date from the specific partition from the as on date will give correct result.

                 

                Thanks anyways for giving the clue.

                • 5. Re: Query for Getting previous date in oracle in specifc scenario
                  Solomon Yakobson

                  with t as (

                             select  account_no,

                                     balance,

                                     as_on_date,

                                     case

                                       when balance < 0 and lag(balance,1,0) over(order by as_on_date) >= 0 then as_on_date

                                     end start_date

                               from  tbl

                            )

                  select  account_no,

                          balance,

                          as_on_date,

                          case sign(balance)

                            when -1 then as_on_date - last_value(start_date ignore nulls) over(order by as_on_date)

                          end + 1 days_passed,

                          case sign(balance)

                            when -1 then last_value(start_date ignore nulls) over(order by as_on_date)

                          end start_date

                    from  t

                    order by as_on_date

                  /


                  ACCOUNT_NO    BALANCE AS_ON_DAT DAYS_PASSED START_DAT
                  ---------- ---------- --------- ----------- ---------
                        1001       -100 01-JAN-13           1 01-JAN-13
                        1001       -150 02-JAN-13           2 01-JAN-13
                        1001        200 03-JAN-13
                        1001       -250 04-JAN-13           1 04-JAN-13
                        1001       -300 05-JAN-13           2 04-JAN-13
                        1001       -310 06-JAN-13           3 04-JAN-13

                  6 rows selected.

                  SQL>

                   

                  SY.

                  • 6. Re: Query for Getting previous date in oracle in specifc scenario
                    chris227

                    with start_dates as (

                    select

                    account_no

                    ,balance

                    ,as_on_date

                    ,null days_passed

                    ,case

                    when sign(balance) != lag(sign(balance), 1, 0) over (partition by account_no order by as_on_date)

                      and sign(balance) < 0

                    then as_on_date

                    else null

                    end start_date

                    from t

                    )

                     

                    select

                    account_no

                    ,balance

                    ,as_on_date

                    ,as_on_date + 1 -

                    last_value(start_date ignore nulls) over (partition by account_no order by as_on_date)

                    days_passed

                    ,last_value(start_date ignore nulls) over (partition by account_no order by as_on_date)

                    start_date

                    from start_dates

                    order by

                    account_no

                    ,as_on_date

                     

                    ACCOUNT_NOBALANCEAS_ON_DATEDAYS_PASSEDSTART_DATE
                    1001-10001/01/2013101/01/2013
                    1001-15001/02/2013201/01/2013
                    100120001/03/2013301/01/2013
                    1001-25001/04/2013101/04/2013
                    1001-30001/05/2013201/04/2013
                    1001-31001/06/2013301/04/2013
                    1002-100001/01/2013101/01/2013
                    1002-150001/02/2013201/01/2013
                    1002200001/03/2013301/01/2013
                    1002-250001/04/2013101/04/2013
                    1002-300001/05/2013201/04/2013
                    1002-310001/06/2013301/04/2013