Need Column Alias to be an Expression - Can this be accomplished

Question

Need Column Alias to be an Expression - Can this be accomplished

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 14, 2011 at 9:51 am

#246059

I execute the following command and it returns the following:
SELECT CAST(UPPER(DATEADD(mm, 0, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD'
Current MTD
NOV 2011 MTD
SELECT CompanyNumber, CompanyName,MAX(Quote.SalesTransferredTS) AS CAST(UPPER(DATEADD(mm, 0, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD'
As opposed to the following:
CASE Datediff(mm, Quote.SalesTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, Quote.SalesTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PreviousMonth
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

I don't really see any reasonable way around using dynamic sql to do what you want. You could do an absurd list of IF blocks but that would be terrible. Honestly, it would be much better to use 1, 2, 3, etc to denote what month offset you're talking about and handle the logic for the column name in the presentation layer.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Has to be dynamic sql.

Or pivot in the front end.

Or pivot in sql then use front end to display the column name. Which you can dynamically concatenate sql server side.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thank you for the responses.

Currently I'm using a CASE Statement to get PreviousMonthNumberSales,

PreviousMonthSumSales,TwoMonthsPriorNumberSales,

TwoMonthsPriorSumSales, etc going back 5 months.

The original requirement was to generate a Report for each of the Marketing Groups and send them a Report containing the other Reps information.

I questions whether they wanted to do that or not.

I also questioned how they wanted it delivered.

They want the informationed e-mailed to them the table contains their e-mail address and is in my query.

The information that I need to send is Their Account Number,Account Name, Phone, Fax, Sales Year & Month, and finally the Count of Sales / #Months.

I know that a Cursor is like dirty word in SQL Server unlike Oracle but I would like to use one based on the Query that I'm writing.

I could swap the PreviousMonthNumberSales Column Name with 2011-10, etc.

I'm sure that I will get replies on this if not hate mail.:hehe:

Thanks again!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Well you can buld the columns name like that and then swap them in the presentation. I still don't see the issue with that option.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Ninja's_RGR'us (11/14/2011)
Well you can buld the columns name like that and then swap them in the presentation. I still don't see the issue with that option.

I'm sorry, I did not mean to suggest that I had a problem with the option in the presentation.

I'm supposed to create 4 Reports involing two Databases.

In addition I'm supposed to E-Mail from SQL Server a Record for each Marketing Rep.

I created a CTE and the Output from One Record in the Report is as follows

Bill to #Marketing GroupPHONEFAXEMAILCurrentMonthIssuesCurrentMonthPremiumPreviousMonthIssuesPreviousMonthPremiumTwoMonthsPriorIssueTwoMonthsPriorPremiumThreeMonthsPriorIssuesThreeMonthsPriorPremiumFourMonthsPriorIssuesFourMonthsPriorPremiumFiveMonthsPriorIssuesFIveMonthsPriorPremiumSixMonthsPriorIssuesSixMonthsPriorPremiumMos Avg

9999999John Doe555-222-1111555-222-1112xyz@ATT.NET0013124749814198755650122018098292682214

I was intending on using Database Mail.

I just got word that they would like to have an attachment for each rep to include the detail and the summary list above.

For the other reports I need the summary and the detail records.

There are report headings.

Does this sound like Reporting Servicves would be a solution to this problem?

I know that SSIS would be a bear.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Just got this IM:

so to summarize the requirements, we just need to generate an email to each agency on the list with their policy count to date.

I don't know what is next. :hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

SSRS + subscriptions could work if you have enterprise.

If not send an html e-mail and use code to calculate the correct headers for the code and you'll be 100% on target.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Ninja's_RGR'us (11/14/2011)
SSRS + subscriptions could work if you have enterprise.
Unfortunately no enterprise.:laugh:
If not send an html e-mail and use code to calculate the correct headers for the code and you'll be 100% on target.

I sent HTML e-mails years ago.

Do you have a link to a good sample that you could share?

Thanks a lot.:cool:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Hit google to search this site. Lowelll posted his sample a few times.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

I'm sorry google what site?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Welsh Corgi (11/14/2011)
I'm sorry google what site?

ssc.com

http://www.sqlservercentral.com/Forums/FindPost1176448.aspx

Type this in google => site:sqlservercentral.com lowell html mail

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Ninja's_RGR'us (11/14/2011)
SSRS + subscriptions could work if you have enterprise.
If not send an html e-mail and use code to calculate the correct headers for the code and you'll be 100% on target.

So is the difference between Enterprise & Standard with respect to Data Driven Subscriptions is that you can derive recipient information at run time from SQL?

http://msdn.microsoft.com/en-us/library/aa179341(v=sql.80).aspx

To what extent are you limited when using Standard Edition?

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Welsh Corgi (11/14/2011)
Ninja's_RGR'us (11/14/2011)
SSRS + subscriptions could work if you have enterprise.
If not send an html e-mail and use code to calculate the correct headers for the code and you'll be 100% on target.
So is the difference between Enterprise & Standard with respect to Data Driven Subscriptions is that you can derive recipient information at run time from SQL?
http://msdn.microsoft.com/en-us/library/aa179341(v=sql.80).aspx
To what extent are you limited when using Standard Edition?
Thanks!

DDS requires enterprise.

You can fake that / build your own, but that's a PITA.

If the report is simple, you might as well build the html table and e-mail table.

From the template I sent you it should be easy enough.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Ninja's_RGR'us (11/14/2011)
Welsh Corgi (11/14/2011)
Ninja's_RGR'us (11/14/2011)
SSRS + subscriptions could work if you have enterprise.
If not send an html e-mail and use code to calculate the correct headers for the code and you'll be 100% on target.
So is the difference between Enterprise & Standard with respect to Data Driven Subscriptions is that you can derive recipient information at run time from SQL?
http://msdn.microsoft.com/en-us/library/aa179341(v=sql.80).aspx
To what extent are you limited when using Standard Edition?
Thanks!
DDS requires enterprise.
You can fake that / build your own, but that's a PITA.
If the report is simple, you might as well build the html table and e-mail table.
From the template I sent you it should be easy enough.

I have the reports part that are for internal use, then I have a requirement to send e-mails to Reps with just a few columns inside the body of the e-mail.

I need to include the following variables from a cursor with the Column Name: Column Value in a tabular format:

@AccountID INT

@Company VARCHAR(60)

@BillToNumber int

@SalesCount int

I need it justified (Right for Variable Names) & Left or Right depending upon the Data Type, Text, Numeric, etc.

Any Ideas on how I can format this?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/