0 Replies Latest reply: Mar 21, 2017 5:22 AM by Supriya R RSS

    fetch latest expired record along with Non-expired record

    Supriya R

      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.