Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
 Posted - 2009-08-27 : 14:33:38
|
| Sorry for the horrible rookie question but I was given a challenge and I am hoping to have a somewhat easy solution...I am trying to extract data from a table using multiple derived tables where each column will be it's own sub-select incrementaly decreasing the month/year being selected based upon todays date.Column 1 is Current Month and Year, the next column is the current month minus 1, the next is current mins 2, etc...I was hoping to be able to do something like the following:DATEPART(month, GetDate()) -1 AS [Prev Month]It does not like the minus sign when I validate it.The application is to retrieve complaints for the past 12 months, drop into monthly buckets and do trend analysis.If I can figure that part out, I will use a case statement that will do something like:Case If Month - counter <1 then add 12 and reduce yearElse Use Month - counter and Curr YearEnd for the select of the dated records.Again, sorry for the rookie question.John"The smoke monster is just the Others doing barbecue" |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-27 : 14:55:13
|
| Try SELECT MONTH(dateadd(m,-1,getdate()))An infinite universe is the ultimate cartesian product. |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-08-27 : 15:02:07
|
It appears you are looking for a pivot table, but please post some sample data and desired results and someone will be able to assist you more, if you need additional help.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-27 : 15:03:31
|
| I was about to post the same thing as Vinnie for month name.Perhaps you want to look into PIVOT for part of your solution.An infinite universe is the ultimate cartesian product. |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-08-28 : 06:51:41
|
| Vinnie881 and cat_Jesus,Thanks for the suggestion. I have never done Pivot before and will follow up.The data is simply:Dept, Complaint Date, Complaint Status, Complaint Text, Complaint resolution. I would like to have a graph, by dept of total complaints by month. John"The smoke monster is just the Others doing barbecue" |
|
|
|
|
|
|
|
|
Using DatePart, GETDATE() and subtraction