Oracle 10g

[hsandwick]

Hello, I need to retrieve data for the entire last month (and then run a scheduled report any day of the current month).

In its simplest form, this is what I'm looking for:

SELECT
TABLE.DATE_RECEIVED
FROM
TABLE
WHERE
TABLE.DATE_RECEIVED IN ..... LAST MONTH

Appreciate your help,
Helen

 

[jimirvine]

define what you mean by last month. Last 31 days, between now and this day last month, from the first day to the last day of last month ....

 

[Dagon]

Why do you need an IN clause ? What's wrong with BETWEEN ?

 

[SantaMufasa]

Helen,

There are tons of ways to code your need. If, by "last month" you mean the month prior to the current month, you can say:

SELECT
TABLE.DATE_RECEIVED
FROM
TABLE
WHERE
trunc(TABLE.DATE_RECEIVED,'MM') = trunc(add_months(sysdate,-1),'MM');

Or, if you mean during the previous 30 days, you can say:

SELECT
TABLE.DATE_RECEIVED
FROM
TABLE
WHERE
TABLE.DATE_RECEIVED between sysdate-30 and sysdate;

Let us know if any of this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[hsandwick]

jimirvine, Dagon, and SantaMufasa:

Thank you all for your responses. Yes, I see where my question was rather generic - however, there are multiple ways that I will need to apply this, so all of your questions and answers are v. helpful.

I was able to retrieve the results with Crystal Reports logic, but prefer to push everything down to the db wherever possible for better performance.

SantaMufasa:

I will be able to apply both of your suggestions - these are great!

Much obliged,
Helen

 

[KenCunningham]

Helen - you may not be aware that it's customary in these fora to award little purple stars to people supplying 'great' suggestions?

It might not mean much in the outside world, but here on Tek-Tips it's the currency!

I want to be good, is that not enough?

 

[cmmrfrds]

Here is an example of the method I use - just another way.

where (tran.POST_DATE between trunc(last_day(add_months(sysdate, -2)) + 1)
and trunc(last_day(add_months(sysdate,-1))) )

I think it is better to avoid using any function on an indexed field since that will cause the index to be bypassed - on the literal sysdate it is fine.
For example.
trunc(TABLE.DATE_RECEIVED,'MM')