SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Data to create Alias or "as ..."
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blund
Starting Member

23 Posts

Posted - 10/09/2012 :  18:18:56  Show Profile  Reply with Quote
Can data from a sql field or expression be used to create the Alias of a calculated table column?

For instance,
We want the column to be called the current year (e.g. "2012" or "FY2012"), due to the grouping and summing of invoiced dollars for that year. (we also have a column for the previous 2 or 3 years). Next year the Alias of the first column will need to be "2013". Ultimately this data is being pulled into an Excel spreadsheet thru a data connection, and we would like the Excel column header to show that year, via the Alias (and the last 2 or 3 years, as well)

Thank you

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2012 :  21:20:27  Show Profile  Reply with Quote
you need to use dynamic sql for that? other wise you need to write a cross tabbing logic based on GETDATE() function. If you could post structure of the tables we could provide more accurate suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

blund
Starting Member

23 Posts

Posted - 10/10/2012 :  09:29:42  Show Profile  Reply with Quote


We are viewing the SQL data thru an Excel data connection. Sales wants to see invoice dollars (along with a few bits of other customer information).
The "Alias" of the Sql view column imports into the Excel spreadsheet as the column header so we want to see the fiscal year (and a previous year or 2) show up in the Excel file as the column headers.
From the code below we are calculating our fiscal dollars (Currently, Oct 1st, 2012 - Sep 30th, 2013) as "Current FY", we would like the alias to be "FY2013" and then ideally automatcally show "FY2014" when next Oct 1st, rolls around. If that all works out, we would also show "FY2013" and "FY2012" in additional columns, then all three alias/columns headers would adjust as the years roll by.

Please let me know if this is possible, or if there are better options for what we are attempting.
Also please let me know if I can provide more information as well.

Thanks again




SELECT dbo.Invoice_Header.Customer, dbo.Invoice_Header.Document_Date, DATEADD(month, 3, dbo.Invoice_Header.Document_Date) AS FYconvertedtoCY,
YEAR(DATEADD(month, 3, dbo.Invoice_Header.Document_Date)) AS fy, CASE WHEN DATEADD(month, + 3, dbo.Invoice_Header.Document_Date)
>= '1/1/' + CAST(year(getdate()) AS varchar(4)) THEN dbo.Invoice_Detail.Amount END AS [Current FY]
FROM dbo.Invoice_Detail INNER JOIN
dbo.Invoice_Header ON dbo.Invoice_Detail.[Document] = dbo.Invoice_Header.[Document]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  00:04:31  Show Profile  Reply with Quote
do you've a calendar table in your database which stores the fiscal date ranges?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

blund
Starting Member

23 Posts

Posted - 10/11/2012 :  10:36:11  Show Profile  Reply with Quote
We do not (we actually have not even considered it). We have been using the "DATEADD(month, 3, dbo.Invoice_Header.Document_Date)" command to convert our dates for reports.
Your question is intriguing though, and would like to hear more! Do you think it would help with our situation?

Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  15:26:15  Show Profile  Reply with Quote
yep...it would add more flexibilty so that you dont have worry about changes in your fiscal year if one happens at a later point of time and think about all places where you might need to change the logic. storing fiscal dateranges in a table means its just a matter of updating that table alone whenever your fiscal year definition changes. All other dependent code will continue to work as it is

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

blund
Starting Member

23 Posts

Posted - 10/11/2012 :  17:45:08  Show Profile  Reply with Quote
Thank you, I will look at our FY based queries and see if we can work that in.

Is there a way to use that data for creating the annually changing alias names then?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  18:56:41  Show Profile  Reply with Quote
as i suggested that would require some kind of dynamic crosstabbing logic

like this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

blund
Starting Member

23 Posts

Posted - 10/12/2012 :  11:00:46  Show Profile  Reply with Quote
Thank you for the example.
We will dig into that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2012 :  12:49:09  Show Profile  Reply with Quote
welcome
Let us know if face any issues and need more assistance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000