Previous month of previous year?

[patdools]

Hello,

I am trying to find the proper syntax to obtain records that are from the previous month (from current sysdate) of the previous year. For example, we are now in Feb. 2006 and I would like all records from Jan. 2005. I have this (which parses OK), but does not return the correct records (Jan. 2005):

SUM(CASE WHEN TRUNC(TO_DATE(DIM_DATE.THEDATE),'MONTH') >= TRUNC(TRUNC(SYSDATE,'MONTH')-1,'MONTH') AND TRUNC(TO_DATE(DIM_DATE.THEDATE),'MONTH') <= LAST_DAY(TRUNC(TRUNC(SYSDATE,'MONTH')-1,'MONTH'))
AND (TRUNC(TO_DATE(DIM_DATE.THEDATE),'YYYY') >= TRUNC(TRUNC(SYSDATE,'YYYY')-1,'YYYY')) AND SDK_VISIT.VISIT_TYPE_CODE='I' AND SDK_VISIT.EFFECTIVE_TO_DATE='31-DEC-9999' THEN 1 ELSE 0 END)

Any help would be greatly appreciated.

Thanks.

 

[SantaMufasa]

Pat,

How about using these function combinations:

select add_months(trunc(sysdate,'MM'),-13) "13 Months Ago",
add_months(trunc(sysdate,'MM'),-12)-1 "End of 13 Months Ago"
from dual;

13 Months End of 13
--------- ---------
01-JAN-05 31-JAN-05

Let us know how this works for you.

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.

 

[patdools]

Hi Mufasa,

Thanks for the reply. We're playing with it and should be able to make it work. On a more general level, I've had some exposure to SQL, but need to get a lot better at it to do the web intelligence consulting I do now. I've been told to get a SQL book, but there are many out there. Do you have any recommendations? Unfortunately, I will not have the luxury of concentrating on one flavor of SQL, as I will be writing queries based on what is used at our customer sites. That being said, I do need to build my base query-writing and trouble-shooting skills. I would greatly appreciate any ideas you might have.

Thanks again, Patrick

 

[SantaMufasa]

Patrick,

I haven't read any generic SQL books, but I noticed that on Amazon.com there are:

1) "SQL for Dummies" by Allen G. Taylor ($16.49) and
2) "Sams Teach Yourself SQL in 10 Minutes", Third Edition ($10.19).

I'm thinking that you can't go wrong with titles like those. Let us know your thoughts after you select one of these titles or some other.



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.

 

[Beilstwh]

There are also many tutorials on the web to learn sql. CHeck out the following, which I found on google (ist item returned)

http://www.w3schools.com/sql/sql_intro.asp

Bill
Oracle DBA/Developer
New York State, USA

 

[Welshbird]

I've used this one lots

http://www.techonthenet.com/sql/index.php

and still recommend it around the office.

It's similar to the SAMS teach yourself in 10 minutes book, but free and online.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]