Re: Can DECODE do this?

John L Dunn <misioror_at_gas.uug.arizona.edu> wrote:

>Greetings:
>
>I'm hoping someone can share their knowledge of DECODE with me. I've used
>it for fairly simple purposes and it has worked well, but the current
>problem has be stumped.
>
>Here's what I'd like to do...
>
>I have a view of patient enrollment information that has 2 date fields I
>want to use to calculate the number of days the patient was enrolled in a
>given plan. The enrollment table has 2 dates, enroll_date(the date the
>patient enrolled) and term_date(the date the patient left the plan). I
>want to supply 2 more date fields, which are the beginning(begin_date) and
>ending(end_date) dates of the reporting period, and calculate how many
>member days the plan had for the reporting period.
>
>In procedural code, I'd write something like
> if enroll_date<begin_date and term_date>end_date
> then total_days=end_date - start_date
>
> else if enroll_date < begin_date and term_date < end_date
> then total_days=term_date - start_date
>
> else if enroll_date > begin_date and term_date > end_date
> then total_days=end_date - enroll_date
>
> else if enroll_date > begin_date and term_date < end_date
> then total_days=term_date - enroll_date
>
>Now, how can I do something like this using DECODE? Any ideas
>appreciated. I've looked at several examples but can't find any where the
>ifs contain an "AND" clause.
>
>Thanks for your help!
>
>John Dunn
>The University Physicians

Instead of decode, how about something like this:

least(end_date, term_date) - greatest(begin_date, start_date)

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!