ON
07-02-2003, 01:42 PM
You're right : I missed a last step that will give (A or B) and (C or D) and
that leads to your solution.
All my excuses
Regards.
"andrewst" <member14183@dbforums.com> a écrit dans le message news:
3063449.1057086382@dbforums.com... Originally posted by On Hi all, I think that the question is not quite clear. So I guest that it concerns the available prices to be applied for May of the current year. The solution consist to solve the following logical statements : Statement A : effective_start_date is before May of the year Statement B : effective_start_date is in May of the year Statement C : effective_end_date is in May of the year Statement D : effective_end_date is after May of the year The selected prices must satisfy this date interval condition : (A and C) or (A and D) or (B and C) or (B and D) that can be covered by : (A and (C or D)) or (B and (C or D)) So try this : select price,effective_start_date,effective_end_date from pricing where (to_char(effective_start_date,'YYYYMM') '200305')) or (to_char(effective_start_date,'YYYYMM') = '200305' and (to_char(effective_end_date,'YYYYMM') = '200305' or to_char(effective_end_date,'YYYYMM') > '200305')) Regards How does that 4-condition solution improve on the 2-condition solution I gave earlier? :- WHERE effective_start_date <= TO_DATE('31-MAY-2003') AND effective_end_date >= TO_DATE('01-MAY-2003') Less is more... -- Posted via http://dbforums.com
that leads to your solution.
All my excuses
Regards.
"andrewst" <member14183@dbforums.com> a écrit dans le message news:
3063449.1057086382@dbforums.com... Originally posted by On Hi all, I think that the question is not quite clear. So I guest that it concerns the available prices to be applied for May of the current year. The solution consist to solve the following logical statements : Statement A : effective_start_date is before May of the year Statement B : effective_start_date is in May of the year Statement C : effective_end_date is in May of the year Statement D : effective_end_date is after May of the year The selected prices must satisfy this date interval condition : (A and C) or (A and D) or (B and C) or (B and D) that can be covered by : (A and (C or D)) or (B and (C or D)) So try this : select price,effective_start_date,effective_end_date from pricing where (to_char(effective_start_date,'YYYYMM') '200305')) or (to_char(effective_start_date,'YYYYMM') = '200305' and (to_char(effective_end_date,'YYYYMM') = '200305' or to_char(effective_end_date,'YYYYMM') > '200305')) Regards How does that 4-condition solution improve on the 2-condition solution I gave earlier? :- WHERE effective_start_date <= TO_DATE('31-MAY-2003') AND effective_end_date >= TO_DATE('01-MAY-2003') Less is more... -- Posted via http://dbforums.com