Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am currently working on a report in which I would like to flag those records which have gaps in their support contract.
One asset has more than one support contract records associated.
I would like to flag those records in which there is a gap in the end date of previous record and start date of the current record.
For example, if one contract expired at 12/13/2014 and next contract of the same asset kicks in at 12/15/2014, than I would like to flag it.
Please let me know how to achieve it. Any help is appreciated.
Thank you,
Parth
Perhaps like this?
LEFT JOIN (Contract)
LOAD
asset_id
,contract_id
,if(asset_id = previous(asset_id) and contract_start_date <> previous(contract_end_date) + 1,'Y','N') as contract_gap?
RESIDENT Contract
ORDER BY
asset_id
,contract_id
,contract_start_date
Edit: If you can do the order by when you first read in the data, it may be better to add the field there rather than do a left join.
Perhaps like this?
LEFT JOIN (Contract)
LOAD
asset_id
,contract_id
,if(asset_id = previous(asset_id) and contract_start_date <> previous(contract_end_date) + 1,'Y','N') as contract_gap?
RESIDENT Contract
ORDER BY
asset_id
,contract_id
,contract_start_date
Edit: If you can do the order by when you first read in the data, it may be better to add the field there rather than do a left join.
Thank you John.
Regards,
Parth