Dynamic column name change based on date [T-SQL]

Question

Is there a way to make this work? change static, "13MonthsAgo" into January?

Original

> COUNT(CASE WHEN dateadd(MONTH, - 13, getdate()) >
> HireDate AND dateadd(MONTH, - 13, getdate()) <
> TerminationDate OR
>TerminationDate IS NULL THEN 1 ELSE NULL END) AS 13Monthsago

Preferred

> COUNT(CASE WHEN dateadd(MONTH, - 13, getdate()) >
> HireDate AND dateadd(MONTH, - 13, getdate()) <
> TerminationDate OR
>TerminationDate IS NULL THEN 1 ELSE NULL END) AS
>DATENAME(month, dateadd(MONTH,-13,getdate()))

If you are asking whether you can generate column names on-the-fly then have a look at dynamic SQL. Then see SQL Injection. Then think about what you are really trying to accomplish. — HABO
– HABO, Commented Feb 24, 2015 at 18:45

SouravA · Accepted Answer · 2015-02-25 14:24:07Z

1

A lengthy approach but will work..

declare @MonthName varchar(20)

select 1 num, 'January' name into #Months
union
select 2 num, 'February' name
union
select 3 num, 'March' name
union
select 4 num, 'April' name
union
select 5 num, 'May' name
union
select 6 num, 'June' name
union
select 7 num, 'July' name
union
select 8 num, 'August' name
union
select 9 num, 'September' name
union
select 10 num, 'October' name
union
select 11 num, 'November' name
union
select 12 num, 'December' name

select @MonthName = name from #Months where datepart(mm,getdate()) = num

--Add the other columns to the dataset here
--This is just an example
select HireDate, TerminationDate, 
 COUNT(CASE WHEN dateadd(MONTH, - 13, getdate()) >
 HireDate AND dateadd(MONTH, - 13, getdate()) <
 TerminationDate OR
TerminationDate IS NULL THEN 1 ELSE NULL END) AS 13Monthsago
into #Dataset
FROM SomeTable GROUP BY HireDate, TerminationDate

use tempdb

EXEC sp_RENAME '#Dataset.13Monthsago' , @MonthName, 'COLUMN'

SELECT * FROM #Dataset

edited Feb 25, 2015 at 14:24

answered Feb 25, 2015 at 10:23

SouravA

5,2632 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ColorfulWind Over a year ago

This works perfectly, Thank you! But how do I add 12MonthsAgo, 11MonthsAgo etc into this query?

SouravA Over a year ago

Then what you need to do is get rid of the column name 13MonthAgo and have a generic name like SomeTimeAgo and then create #Dataset13(for 13 months), #Dataset12(for 12 months)..etc and have all of their SomeTimeAgo column renamed as per the above logic.

Gouri Shankar Aechoor · Accepted Answer · 2015-02-25 03:25:58Z

This could be one way to go about it.

;with cte_Dates AS
(
SELECT CAST('20150101' as DATEtime) as DateStr UNION ALL
SELECT '20150201' UNION ALL
SELECT '20150202' UNION ALL
SELECT '20150203' UNION ALL
SELECT '20150204' UNION ALL
SELECT '20150301' UNION ALL
SELECT '20150401' UNION ALL
SELECT '20150501' UNION ALL
SELECT '20150601' UNION ALL
SELECT '20150701' UNION ALL
SELECT '20150801' UNION ALL
SELECT '20150901' UNION ALL
SELECT '20151001' UNION ALL
SELECT '20151101' UNION ALL
SELECT '20151201'
)


SELECT      *  
FROM
    (SELECT DateStr,DATENAME(MONTH,DateStr) As MONTHS 
     FROM
            cte_Dates
     )P
        PIVOT
        (  
        count(DateStr)
        FOR MONTHS IN ([January], [February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
        )AS PVT

Collectives™ on Stack Overflow

Dynamic column name change based on date [T-SQL]

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related