| Author |
Topic |
|
blund
Starting Member
23 Posts |
 Posted - 2012-10-09 : 18:18:56
|
| 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
52326 Posts |
Posted - 2012-10-09 : 21:20:27
|
| 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
blund
Starting Member
23 Posts |
Posted - 2012-10-10 : 09:29:42
|
| 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 againSELECT 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] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-11 : 00:04:31
|
| do you've a calendar table in your database which stores the fiscal date ranges?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
blund
Starting Member
23 Posts |
Posted - 2012-10-11 : 10:36:11
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-11 : 15:26:15
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
blund
Starting Member
23 Posts |
Posted - 2012-10-11 : 17:45:08
|
| 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
blund
Starting Member
23 Posts |
Posted - 2012-10-12 : 11:00:46
|
| Thank you for the example.We will dig into that. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-12 : 12:49:09
|
| welcomeLet us know if face any issues and need more assistance------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Sql Data to create Alias or "as ..."