pvsbandi

asked on

# 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.

Please help.

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

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.

Please help.

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

ASKER

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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!

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!

ASKER

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.

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Thank much! Greatly appreciate!

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

Open in new window