Link to home
Create AccountLog in
Avatar of pvsbandi
pvsbandiFlag for United States of America

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

Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image


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))

Open in new window

Avatar of pvsbandi

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

SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank much! Greatly appreciate!