sql - Query that looks for contract status in another table and flags accordingly -
i have following 2 tables:
table contract number other field 1 2 b 3 c 4 d … … table b contract number contract status date of contract status 1 status-1 1/1/2016 1 status-2 1/2/2016 1 status-3 1/3/2016 2 status-1 1/1/2016 2 status-3 1/2/2016 2 status-4 1/3/2016 2 status-5 1/4/2016 3 status-1 1/1/2016 3 status-2 1/2/2016 4 status-3 1/1/2016 4 status-4 1/2/2016 4 status-5 1/3/2016 4 status-6 1/4/2016 4 status-7 1/5/2016 4 stauts-8 1/6/2016 … … … i trying write query retrieves "contract number" , "some other field" table a. additionally, want have third field in query should show 1 if "status-2" found contract number in "contract status" of table b , 0 otherwise.
what managed far:
flag: iif(table b.contract stauts='status-2',1,0) i used code field should show either 1 or 0. however, unable include in query. guess need kind of loop, such each record retrieved table a, loop searches table b "status-2".
the desired result this:
query result contract number other field flag 1 1 2 b 0 3 c 1 4 d 0 … … ... can guys advise? lot!
if understood correctly, need this:
select t.contract_number, t.someother, iif(exists(select 1 tableb p p.contract_number = t.contract_number , p.contract_status = 'status-2'),1,0) flag_col tablea t for each contract_number, if status-2 exists in other table, put 1 else 0
Comments
Post a Comment