Hi,
I want to get fetch records for latest expired record along with Non-expired record for all batches in table. I have used below code but it is not giving expected result.
MainTable:
Load *, if(expiration_date < '$(varExpirationDate)',1,0) as expFlag inline [
batch_meta_data_id ,campaign_id,campaign_name, expiration_date
42,442,1,2017-07-24
42,455,2,2017-07-31
42,473,3,2017-03-18
42,487,5,2017-03-18
42,520,6,2017-03-19
45,546,7,2017-03-19
45,553,8,2017-10-02
45,565,9,2016-10-09
45,442,10,2017-01-24
45,455,12,2017-01-31
45,473,11,2017-03-18
23,565,13,2016-03-17
23,442,14,2017-07-24
23,455,15,2017-07-31
23,473,16,2017-03-18
23,49,17,2017-03-18
];
left join (MainTable)
LOAD
MAX(expiration_date) as expiration_date,
'1' as expFlag,
batch_meta_data_id as last_batch
Resident MainTable
where expFlag = 1
Group By batch_meta_data_id;
I want this result
| batch_meta_data_id ,campaign_id,campaign_name, | expiration_date,expFlag |
42,442,1,2017-07-24,0
42,455,2,2017-07-31,0
42,508,4,2016-09-04,0
42,520,6,2017-03-19,1
45,546,7,2017-03-19,1
45,553,8,2017-10-02,0
45,565,9,2016-10-09,0
23,442,14,2017-07-24,0
23,455,15,2017-07-31,0
23,473,16,2017-03-18,1
What am i missing in above code.
Please help me on this.
