I am looking into a scenario some similar to future dated stuff.
i have table something similar to this
Product id Product Status Effective From Date Effective to Date Cancelled Indicator 345 A 7/7/2016 (null) 1 345 S 7/7/2016 11/7/2016 (null) 345 A 12/7/2016 (null) (null)
i need to fetched the current dated product if i find a cancelled indicator it means it is no more active if their are two rows one with future dated status. based on the table above i get a latest record if i check for the efd < sysdate and etd is null. but to get the current active status which is the case which i need to implement
i need to check if the sysdate is b/w the efd and etd of the older record if not i need to take the latest record which will be the current status.
i have query which does that
but the thing is what happens when i check
sysdate between efd and etd where etd can be null most of the time.