CODEWITHSUNDEEP
sql-server
user3362246
user3362246

Reputation: 13

Dynamic Column Name by Year

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

Answers (1)

M.Ali
M.Ali

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

Related Questions