# How to write this logic?

Hi I have two sets of dates as the following scenarios:

Basically, i want to show the criteria under scenario, where the payment_start_dt or payment_end_dt fall between eli.start_dt and eli.end_dt. If the eli.end_dt is null, then the eli.end_dt is current date.

I know this is simple, but not able to code it properly.

SCENARIO#1:

ELI.START_DT = 1/3/2009
ELI.END_DT IS 3/31/2009
ELI.CRITERIA = INCOMPLETE

PAYMENT_START_DT IS 2/9/2009
PAYMENT_END_DT IS 2/28/2009

OUTPUT : CRITERIA = INCOMPLETE

SCENARIO#2:

ELI.START_DT = 2/3/2009
ELI.END_DT IS NULL
ELI.CRITERIA = PENDING

PAYMENT_START_DT IS 2/9/2009
PAYMENT_END_DT IS 2/28/2009

OUTPUT : CRITERIA = PENDING

Kent Olsen

Piece of cake.  :)

>> where the payment_start_dt or payment_end_dt fall between eli.start_dt and eli.end_dt. If the eli.end_dt is null, then the eli.end_dt is current date.

Kent

``````SELECT ...
WHERE (payment_start_dt between eli.start_dt and coalesce (eli.end_dt, current date))
OR (payment_end_dt between eli.start_dt and coalesce (eli.end_dt, current date))
``````

sorry, forgot another scenario.

SCENARIO#2:

ELI.START_DT = 2/10/2009
ELI.END_DT IS 2/11/2009
ELI.CRITERIA = PENDING

PAYMENT_START_DT IS 2/1/2009
PAYMENT_END_DT IS 2/28/2009

OUTPUT : CRITERIA = PENDING

SOLUTION
momi_sabag

membership
Create an account to see this answer
Signing up is free. No credit card required.

Thanks for the quick turnaround..

Both of your logic makes sense to me, until i added another scenario.
Can you also please consider in the code?
Thanks again for the help!

To explain better, i think i mean, whenever the month and year of eli.startdt/eli.end_dt overlaps with the payment_start/end dates, then the criteria should be picked.

membership
Create an account to see this answer
Signing up is free. No credit card required.