Flag records which have Gaps in support contract

[#1]

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

[johnw · Accepted solution]

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.

[#3]

Thank you John.

Regards,

Parth