Reputation: 13
Is there a way to title my query fields/columns base on the current year? For example in the query below id like the field name to be sales2012 instead of TwoPriorYrs_sales. The objective would the that on Jan 1st of 2015 the field would be automatically be sales2013
(SELECT SUM(sales_dol)
FROM dbo.sumsales
WHERE (a.part_code = part_code)
AND (YEAR(sale_date) = YEAR(GETDATE()) - 2)) AS TwoPriorYrs_sales
is there a way to do something like
(SELECT SUM(sales_dol)
FROM dbo.sumsales
WHERE (a.part_code = part_code)
AND (YEAR(sale_date) = YEAR(GETDATE()) - 2)) AS ('sales'+ (YEAR(getdate())-2))
Thanks!
M.Ali? Where would I put the dynamic sql in a query that has multiple columns? Does each column have to be declared individually? The following is the SQL and I'm looking to put the year in the last 2 columns:
SELECT a.cust_code, a.part_code, c.part_desc, c.part_status, c.part_ddate, c.part_dtype,
(SELECT SUM(qty_sold) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()))) AS YTD_qty, ROUND
((SELECT SUM(sales_dol) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()))), 2) AS YTD_sales,
(SELECT SUM(qty_sold) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 1) AND (sale_date <= DATEADD(Year, - 1,
GETDATE()))) AS YTDLastYr_qty, ROUND
((SELECT SUM(sales_dol) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 1) AND (sale_date <= DATEADD(Year, - 1,
GETDATE()))), 2) AS YTDLastYr_sales,
(SELECT SUM(qty_sold) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 1)) AS LastYr_qty, ROUND
((SELECT SUM(sales_dol) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 1)), 2) AS LastYr_sales,
(SELECT SUM(qty_sold) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 2)) AS TwoPriorYrs_qty, ROUND
((SELECT SUM(sales_dol) AS Expr1
FROM dbo.sumsales AS b
WHERE (a.cust_code = cust_code) AND (a.part_code = part_code) AND (YEAR(sale_date) = YEAR(GETDATE()) - 2)), 2) AS TwoPriorYrs_sales
FROM dbo.sumsales AS a INNER JOIN
dbo.partmstr AS c ON a.part_code = c.part_code INNER JOIN
dbo.orderfrom AS d ON a.cust_code = d.cust_code
WHERE (YEAR(a.sale_date) >= YEAR(GETDATE()) - 2)
GROUP BY a.cust_code, a.part_code, c.part_desc, c.part_status, c.part_ddate, c.part_dtype
Upvotes: 1
Views: 1999
Reputation: 69594
If you want Column name to be set dynamically you will have to do it using dynamic sql something like this...
DECLARE @Sql NVARCHAR(MAX);
DECLARE @Column_Alias NVARCHAR(MAX);
SET @Column_Alias = 'Year'+ CAST(YEAR(GETDATE())-2 AS NVARCHAR);
SET @Sql = N'SELECT SUM(sales_dol) AS ' + @Column_Alias +
N'FROM dbo.sumsales
WHERE a.part_code = part_code
AND YEAR(sale_date) = YEAR(GETDATE())-2'
EXECUTE sp_executesql @Sql
Upvotes: 1