do i need to use sql with a case or decode to do this?

[bookouri]

I have a report that gives the HR people of count of applications received last month. It works fine as long as the month is not January. Can somebody point me in the right direction to fix it so that it works in all cases?

thanks for any suggestions.

select count(*) from applicant where
(to_char(initreceiv,'yyyy') = to_char(sysdate,'yyyy')
and to_char(initreceiv,'mm') = to_char(sysdate,'mm') - 1)

 

[hoinz]

Have a look at add_months function;
something like
to_char(add_months(sysdate,-1),'yyyymm')

 

[bookouri]

thanks, ill take a look at that, Ive been trying to do it with a decode so far.

 

[SantaMufasa]

Bookouri,

The reason why your original code didn't work is because when the run date is in January of the current year, your WHERE clause looks for the current month number (in this case, '1') minus 1, which becomes '0'. For those years which do not have a "0th" month (which is most years), you get non-existent results.

This phenomenon is one reason why Oracle came up with the add months function (as Hoinz pointed out). Just change your code to read:

select count(*)
  from applicant
 where trunc(initreceiv,'MM') = trunc(add_months(sysdate,-1),'MM');

...then let us know your satisfaction level.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bookouri]

As always, you guys are exactly right. Once again, I couldnt see the forest for the trees.. trying to make something a LOT more difficult than it had to be...

thanks again...