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?
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)