| Author |
Topic |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2008-03-07 : 12:27:11
|
| I currently have this statement:CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate().I've tried the month(dateadd("m',-11, getdate())but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-07 : 12:41:10
|
| Your column names when retrieving data from a database should always remain constants, only the values themselves in the rows should change. Why do you think you want to do this?- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-03-07 : 13:31:24
|
| Keep presentation layer detail in the presentation layer.How do you expect anyone to consume this data if the name is changing every month anyway?Jayto here knows when |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2008-03-07 : 13:55:05
|
| It's a rolling or running 12 month Sales report based on getdate(). When I run it at the end of the month next then MonthLessEleven would change from 4/2007 to 5/2007. Does this make sense? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-07 : 14:02:41
|
| No, it doesn't make sense. Again, you can label a web page or report or Excel file with any column headings that you want, but your database should return data with consistent column names and a consistent structure.Or, perhaps you are not explaining well enough exactly what you are trying to do.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2008-03-07 : 15:50:13
|
| The rows in the columns are InvoiceAmounts for Months. Current month, month-1, month-2, etc. for a rolling 12 month report showing Top 20 customers by Location.I have in my report to where I put in the column header:month(getdate())+'/'+year(getdate()) for CurrentMonth. This is in the Report Designer. I wanted to be able to do this on the query side. Again, I don't want the column header to read CurrentMonth......I'd like it to read 3/2008.Is this possible. Sorry, if I'm not up on this stuff. Very new. Thanks. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-07 : 22:50:56
|
Then you should always have your columns labeled 1-12 and do the month/name formatting on the report presentation side.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-03-08 : 18:15:32
|
| While I agree, whole heartedly, that this type of thing should be done in the presentation layer, has anyone even thought of asking the op if there was a presentation layer?--Jeff Moden |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 18:20:13
|
quote:
Originally posted by Jeff Moden
While I agree, whole heartedly, that this type of thing should be done in the presentation layer, has anyone even thought of asking the op if there was a presentation layer?--Jeff Moden
he said he was doing this for a report, in report designer..implied that he wants the presentation to automatically change (by way of actually physically changing the source tables column names)
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-03-08 : 18:20:15
|
| OsuPratt... what are you using to display this report? Are you just printing whatever SQL spits out or are you importing into Excel or do you have some custom application that uses this data? In other words, do you have a presentation layer or is the SQL Server output the only thing you have?--Jeff Moden |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2008-03-10 : 10:10:37
|
| Visual Studio (Sql Server Reporting Services). OK. So I should do the formatting on the report side. That's fine. I didn't know if that was the way to go about it or not. Thanks. |
|
|
|
|
Column label using Dateadd function