2

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?

Jenna
  • 75
  • 6
  • 1
    what is `aqua-data-studio`? Tag say is a ide for sql.. but what database? – Juan Carlos Oropeza Oct 30 '15 at 21:50
  • Why don't you add an auto-increment ID to `T1` and use that to refer to it in `new_var` - much simpler. – Kenney Oct 30 '15 at 21:52
  • Your rules for `new_var` arent clear, some order 1 and 2 are also `0` – Juan Carlos Oropeza Oct 30 '15 at 21:54
  • Add another case to your `else`? – Elliott Frisch Oct 30 '15 at 21:59
  • @JuanCarlosOropeza The database is netezza. – Jenna Oct 30 '15 at 22:01
  • `(please advise how to code this) ` but what value you want there? You already say have 2 values for `order = 1 ` so which one? – Juan Carlos Oropeza Oct 30 '15 at 22:03
  • @JuanCarlosOropeza Just did some changes. Thanks. – Jenna Oct 30 '15 at 22:04
  • 1
    My question isnt answer you still have two values for `order=1` which one you will use for `order=2` ? You should read [**Need an Answer? Actually, No ... You Need a Question**](http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx) – Juan Carlos Oropeza Oct 30 '15 at 22:08
  • @JuanCarlosOropeza Just as I said in my post, if several records with the same date, the value of 'new_var' for the order<>1 should be the same with order=1. – Jenna Oct 30 '15 at 22:09
  • @ElliottFrisch Yes, but don't know how to code... – Jenna Oct 30 '15 at 22:16
  • As I say in my comment your question doesnt make sense and dont give enough information. – Juan Carlos Oropeza Oct 30 '15 at 22:23
  • @JuanCarlosOropeza Just retain the new_var value from previous order_ind=1 for the following order_ind<>1 as long as they are on the same day. Why it doesn't make sense to you? – Jenna Oct 30 '15 at 22:32
  • @JuanCarlosOropeza The value for order_ind=1 is based on my criteria. The values for order_ind<>1 are based on the value of order_ind=1. Clear? – Jenna Oct 30 '15 at 22:34
  • Fine you calculate `new_var` for `order_ind = 1` using your two criteria... but you have `new_var = 1` for date `1/1/2015 ` and `new_var = 0` for date `1/5/2015` ... so for `order_ind<>1` which value of `new_var` will you use? – Juan Carlos Oropeza Oct 30 '15 at 22:39
  • @JuanCarlosOropeza Depends on the date, said many times though... Let's say on 1/10/2015, new_var=1 for order_ind=1, then for the other records that on THE SAME DAY(1/10/2015) and order_ind<>1, new_var=1. If new_var=0 for order_ind=1 on 1/10/2015, then for the other records that on 1/10/2015 and order_ind<>1, new_var=0. – Jenna Oct 31 '15 at 16:54

1 Answers1

1

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)
Jeremy Fortune
  • 2,459
  • 1
  • 18
  • 21
  • You shouldn't try to answer a question by guessing what the OP want. In my experience is a waste of time. And you cant use `max(ind_1_new_var)` inside the same level of the `case .. end ind_1_new_var` You have to create a sub query first – Juan Carlos Oropeza Nov 02 '15 at 04:56
  • I understand that it may waste my time. In Netezza, you most certainly can reference column aliases defined before the current column. I did it in a production script just today. – Jeremy Fortune Nov 02 '15 at 18:56
  • While we're giving suggestions, you shouldn't say something can't be done when you don't know for sure. In my experience, this is misleading and unhelpful. – Jeremy Fortune Nov 02 '15 at 22:50
  • I say you cant do that in normal sql like postgre or sql server, if you say you can do that in netezza I will take your word for it, but I dount it. – Juan Carlos Oropeza Nov 03 '15 at 00:52
  • 1
    _Wow_. Here's an [SO post](http://stackoverflow.com/questions/24246283/field-aliasing-in-queries-nzsql) and I updated my answer with results from my db. – Jeremy Fortune Nov 03 '15 at 13:07
  • @jeremytwfortune This really helps me! Just tried it and it worked great! Thanks a lot! – Jenna Nov 04 '15 at 02:49