sqlnetezzaaqua-data-studio

How to keep the value from previous records


My original table 'T1' looks like this:

     ID  Date        Order_ind   Var2   Var3
      1  1/1/2015            1  .....  .....
      1  1/5/2015            1  .....  .....
      1  1/5/2015            2  .....  .....
      2  1/10/2015           1  .....  .....
      2  1/20/2015           1  .....  .....
      2  1/20/2015           2  .....  .....
      2  1/20/2015           3  .....  .....

The final table that I want to create is adding an additional variable 'new_var' based on some criteria. As you may notice, there are some records with the same date, and those criteria only work on the first record (order_ind=1). For the rest of the records with the same date, such as order_ind=2, or 3, the new_var value should be the same with the order_ind=1 record.

     ID  Date        order_ind   Var1   Var2    new_var   
      1  1/1/2015            1  .....  .....    1
      1  1/5/2015            1  .....  .....    0
      1  1/5/2015            2  .....  .....    0
      2  1/10/2015           1  .....  .....    0
      2  1/20/2015           1  .....  .....    1
      2  1/20/2015           2  .....  .....    1
      2  1/20/2015           3  .....  .....    1

The SQL codes that I wrote are like these:

     SELECT *,
            CASE
            WHEN order_ind=1 and (criteria1....) THEN '1'
            WHEN order_ind=1 and (criteria2....) THEN '0' 
            WHEN order_ind<>1 .......(please advise how to code this) 
            END AS new_var
     FROM T1
     ;

Any idea how to write the code for records with order_ind<>1?


Solution

  • I would do this in a few passes. First, make an ind_1_new_var column that contains values only for the order_ind = 1 records.

    select
      *
      ,case
        when order_ind = 1 and (criteria1...) then 1
        when order_ind = 1 and (criteria2...) then 0
        else null
      end ind_1_new_var
    from
      t1;
    

    Then build your new_var referencing this column.

    select
      *
      ,case
        when order_ind = 1 and (criteria1...) then 1
        when order_ind = 1 and (criteria2...) then 0
        else null
      end ind_1_new_var
      ,max(ind_1_new_var) over (
        partition by id, date
      ) new_var
    from
      t1;
    

    I don't know your criteria1, but here's a working example in my nz database with the data you gave.

    TEST_DB(ADMIN)=> select * from t1 order by 1,2,3;
     ID |  T1_DATE   | ORDER_IND | VAR1 | VAR2
    ----+------------+-----------+------+------
      1 | 2015-01-01 |         1 |    0 |    0
      1 | 2015-01-05 |         1 |    0 |    0
      1 | 2015-01-05 |         2 |    0 |    0
      2 | 2015-01-10 |         1 |    0 |    0
      2 | 2015-01-20 |         1 |    0 |    0
      2 | 2015-01-20 |         2 |    0 |    0
      2 | 2015-01-20 |         3 |    0 |    0
    (7 rows)
    
    TEST_DB(ADMIN)=> select
    TEST_DB(ADMIN)->   *
    TEST_DB(ADMIN)->   ,case
    TEST_DB(ADMIN)->     when order_ind = 1 and (
    TEST_DB(ADMIN)(>       (id = 1 and t1_date = '2015-01-01')
    TEST_DB(ADMIN)(>       or (id = 2 and t1_date = '2015-01-20')
    TEST_DB(ADMIN)(>     ) then 1
    TEST_DB(ADMIN)->     when order_ind = 1 and (
    TEST_DB(ADMIN)(>       (id = 1 and t1_date = '2015-01-05')
    TEST_DB(ADMIN)(>       or (id = 2 and t1_date = '2015-01-10')
    TEST_DB(ADMIN)(>     ) then 0
    TEST_DB(ADMIN)->     else null
    TEST_DB(ADMIN)->   end ind_1_new_var
    TEST_DB(ADMIN)->   ,max(ind_1_new_var) over (
    TEST_DB(ADMIN)(>     partition by id, t1_date
    TEST_DB(ADMIN)(>   ) new_var
    TEST_DB(ADMIN)-> from
    TEST_DB(ADMIN)->   t1
    TEST_DB(ADMIN)-> order by 1,2,3;
     ID |  T1_DATE   | ORDER_IND | VAR1 | VAR2 | IND_1_NEW_VAR | NEW_VAR
    ----+------------+-----------+------+------+---------------+---------
      1 | 2015-01-01 |         1 |    0 |    0 |             1 |       1
      1 | 2015-01-05 |         1 |    0 |    0 |             0 |       0
      1 | 2015-01-05 |         2 |    0 |    0 |               |       0
      2 | 2015-01-10 |         1 |    0 |    0 |             0 |       0
      2 | 2015-01-20 |         1 |    0 |    0 |             1 |       1
      2 | 2015-01-20 |         2 |    0 |    0 |               |       1
      2 | 2015-01-20 |         3 |    0 |    0 |               |       1
    (7 rows)