date

[RamanaSQL]

i would like to create last 3 months data only for a view.I will be creating this view from 15 different tables but i have cdate in table A which i want to restrict only for last 3 months.


Right now if i create this view with out date condition it's taking like forever and it has 10 million rows , if i reduce to 3 months then it will be fast ...

Will that make any difference , if i restict only to 3 to 4 months

also how do i do that

like

create view demoview as select * from table a, tableb, tablec
where trunc(tablea.cdate)in(select ...... from dual)

I am not sure what to place in between "......"

Pls help

[tamilselvan]

Will the "Last 3 months " be equal to sysdate - 90 ?
Or calendar months?

Tamil

[lluf44]

where trunc(tablea.cdate) between add_months((last_day(sysdate)+1),-3) and sysdate

would yield 03/01/2004 to today if you wanted the current month plus previous 2 months, else change -3 to -4 to start with 02/01/2004.

[slimdave]

Originally posted by lluf44
where trunc(tablea.cdate) between add_months((last_day(sysdate)+1),-3) and sysdate

would yield 03/01/2004 to today if you wanted the current month plus previous 2 months, else change -3 to -4 to start with 02/01/2004.

Better to say ...

Where
   tablea.cdate Between
      Add_Months(Trunc(sysdate,'MM'),-3) And
      SysDate

or

Where
   tablea.cdate >= Add_Months(Trunc(sysdate,'MM'),-3)