Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: change sql statement and make it simple
"Cobra__77" <Cobra__77_at_hotmail.com> wrote in message
news:m5X95.11600$Tb7.78632_at_news-server.bigpond.net.au...
> I have the following query....it checks which half of the year is last
> month..
> and then if it is first half it returns 1-Jan-2000 and if it is second
half
> 1-July-2000
>
> is there a way to get rid of the in-line view and do it in one go...and
not
> use a function on custmer contact_date too...
>
> any ideas...
> thanks
>
> select customer_id
> from customers a,
> (select trunc(add_months(sysdate,-1),'year') begin_date
> from dual
> where to_number(to_char(add_months(sysdate,-1),'mm')) between 1 and
6
> union
> select add_months(trunc(add_months(sysdate,-1),'year'),6) begin_date
> from dual
> where to_number(to_char(add_months(sysdate,-1),'mm')) between 7 and
12
> ) b
> where a.contact_date > b.begin_date
>
>
>
>
Interesting challenge! Thank you!
Is this simple enough?
select add_months(trunc(to_date('&&datum','dd-mm-yy'),'year')
,(round(to_char(to_date('&&datum','DD-MM-RR'),'Q')/2) - 1) * 6)
from dual
/
The second argument to add_months calculates the quarter, divs the quarter by 2 and multiplies that by 6 to get the correct number of months to be added.
Hth,
Sybrand Bakker, Oracle DBA Received on Sun Jul 09 2000 - 00:00:00 CDT
![]() |
![]() |