Creating Dynamic Dates as Variable (Column Names) in SQL

Question

First, I have read about similar posts and have read the comments that this isn't an ideal solution and I get it but the boss (ie client) wants it this way. The parameters are as follows (for various reasons too bizarre to go into but trust me): 1. SQL Server Mgmt Studio 2016 2. NO parameters or pass throughs or temp tables. All has to be within contained code.

So here we go:

I need to create column headings that reflect dates:

1. Current date
2. Most recent quarter end prior to current date
3. Most recent quarter end prior to #2
4. Most recent quarter end prior to #3
5. Most recent quarter end prior to #4
6. Most recent quarter end prior to #5

So if using today's date, my column names would be as follows

12/18/2016  9/30/2016  6/30/2016  3/31/2016  12/31/2016 9/30/2015

I can easily do it in SAS but can't in SQL given the requirements stated above.

Help please with same code.

Thank you

Paula

(1) Are you aware that for dynamic column names you need dynamic code? (2) Do you really care about the column names? Puting the right data in the relevant columns is not enough? — David דודו Markovitz
– David דודו Markovitz, Commented Dec 18, 2016 at 17:13

John Cappelletti · Accepted Answer · 2016-12-18 21:52:36Z

Seems like a long way to go for something which really belongs in the presentation layer. That said, consider the following:

Let's assume you maintain a naming convention for your calculated fields, for example [CurrentDay], [QtrMinus1], [QtrMinus2], [QtrMinus3], [QtrMinus4],[QtrMinus5]. Then we can wrap your complicated query in some dynamic SQL.

Just as an illustration, let's assume your current query results looks like this

After the "wrap", the results will then look like so:

The code - Since you did NOT exclude Dynamic SQL.

Declare @S varchar(max)='
Select [CustName]
      ,['+convert(varchar(10),GetDate(),101)+'] = [CurrentDay]
      ,['+Convert(varchar(10),EOMonth(DateFromParts(Year(DateAdd(QQ,-1,GetDate())),DatePart(QQ,DateAdd(QQ,-1,GetDate()))*3,1)),101)+'] = [QtrMinus1]
      ,['+Convert(varchar(10),EOMonth(DateFromParts(Year(DateAdd(QQ,-2,GetDate())),DatePart(QQ,DateAdd(QQ,-2,GetDate()))*3,1)),101)+'] = [QtrMinus2]
      ,['+Convert(varchar(10),EOMonth(DateFromParts(Year(DateAdd(QQ,-3,GetDate())),DatePart(QQ,DateAdd(QQ,-3,GetDate()))*3,1)),101)+'] = [QtrMinus3]
      ,['+Convert(varchar(10),EOMonth(DateFromParts(Year(DateAdd(QQ,-4,GetDate())),DatePart(QQ,DateAdd(QQ,-4,GetDate()))*3,1)),101)+'] = [QtrMinus4]
      ,['+Convert(varchar(10),EOMonth(DateFromParts(Year(DateAdd(QQ,-5,GetDate())),DatePart(QQ,DateAdd(QQ,-5,GetDate()))*3,1)),101)+'] = [QtrMinus5]
 From (
        -- Your Complicated Query --
        Select * from YourTable
      ) A
'
Exec(@S)

If it helps the visualization, the generated SQL is as follows:

Select [CustName]
      ,[12/18/2016] = [CurrentDay]
      ,[09/30/2016] = [QtrMinus1]
      ,[06/30/2016] = [QtrMinus2]
      ,[03/31/2016] = [QtrMinus3]
      ,[12/31/2015] = [QtrMinus4]
      ,[09/30/2015] = [QtrMinus5]
 From (
        -- Your Complicated Query --
        Select * from YourTable
      ) A

Pரதீப் · Accepted Answer · 2016-12-18 17:36:20Z

0

Here is one way using dynamic query

DECLARE @prior_quarters INT = 4,
        @int            INT =1,
        @col_list       VARCHAR(max)=Quotename(CONVERT(VARCHAR(20), Getdate(), 101))

WHILE @int <= @prior_quarters
  BEGIN
      SELECT @col_list += Concat(',', Quotename(CONVERT(VARCHAR(20), Eomonth(Getdate(), ( ( ( ( Month(Getdate()) - 1 ) % 3 ) + 1 ) * -1 ) * @int), 101)))
      SET @int+=1
  END

--SELECT @col_list -- for debugging 

EXEC ('select '+@col_list+' from yourtable')

edited Dec 18, 2016 at 17:36

answered Dec 18, 2016 at 17:25

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

Creating Dynamic Dates as Variable (Column Names) in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related