CODEWITHSUNDEEP
sqlsql-server
Afrah
Afrah

Reputation: 63

dynamic columns for dates

Columns [today], [30days ago], [60days ago], [90 days ago]

I have a table the will provide me with the sales number fro today and 30days ago, 60 days ago and 90 days ago,

But the issue I have is to figure out what the actual date was 60 days ago. I wanted to update my script not show me 60days ago but to show me the actual date 60days ago. I want to make my columns dynamic so i get the actual date 90days ago.

Can anyone help me here?

Please remember this is a long scripts and 10 columns and I want to change each column to show me the actual date and not the 90 days ago.

Upvotes: 0

Views: 2007

Answers (4)

FutbolFan
FutbolFan

Reputation: 13763

If you are trying to build a list of dynamic date columns, you could do this using dynamic pivot method something like this:

Creating a Test Table

create table pvtTbl(DateColumns varchar(50),sales money,employeeid int);

insert into pvtTbl 
select cast(getdate() as date) datecol,1249.00 as sales,123 employeeid
UNION
select cast(dateadd(day,-30,getdate()) as date)  datecol,15615.00 as sales,456 employeeid
UNION
select cast(dateadd(day,-60,getdate()) as date) datecol,125583.00 sales,356 employeeid
UNION
select cast(dateadd(day,-90,getdate()) as date) datecol,25571.00 sales,859 employeeid

This part, I am just creating as an example to build the pivot table. But, you could actually create this step as an insert statement to a temp table in your stored proc, so that it will be updated each time to dynamically build your date columns using this query:

Query to build a list of needed date values

select cast(getdate() as date) datecol
UNION
select cast(dateadd(day,-30,getdate()) as date) datecol
UNION
select cast(dateadd(day,-60,getdate()) as date) datecol
UNION
select cast(dateadd(day,-90,getdate()) as date) datecol

After that, all you will need to do is build a dynamic pivot using STUFF function and XML path to first build a comma separated list of date values and then pivot it dynamically to output it as your columns like this:

Dynamic Pivot to build date columns

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.DateColumns)  
            FROM pvtTbl c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT  employeeid,' + @cols + ' from 
            (
                select sales,employeeid,DateColumns
                from pvtTbl
           ) x
            pivot 
            (
                 sum(sales)
                for DateColumns in (' + @cols + ')
            ) p '


execute(@query)

Anyway, this would be a good idea if you build this as a stored procedure. Let me know if you need any more help.

Demo

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

This is best handled in the client code that retrieves the sql results. Using c# as an example:

string sql = String.Format(
    "SELECT [today] as [{0}], [30days ago] as [{1}], [60days ago] as [{2}], [90 days ago] as [{3}] FROM [MyTable]",
         DateTime.Today.ToShortDateString(),
         DateTime.Today.AddDays(-30).ToShortDateString(),
         DateTime.Today.AddDays(-60).ToShortDateString(),
         DateTime.Today.AddDays(-90).ToShortDateString());

If you really need to, you could put the same string logic into procedural sql, and save that to a stored procedure. But don't do that.

Upvotes: 1

Tanner
Tanner

Reputation: 22753

You can use GETDATE() and DATEADD() to get these values:

SELECT CONVERT(DATE, GETDATE()) AS Today, 
       CONVERT(DATE, DATEADD(DAY, -30, GETDATE())) Minus30

Converting to DATE simply takes off the time portion.

Produces:

Today       Minus30
2015-06-08  2015-05-09

To use these values you can assign a few variables and set the values to be used later in your code:

DECLARE @today DATE, @Minus30 DATE

SELECT @today = CONVERT(DATE, GETDATE()), 
       @Minus30 = CONVERT(DATE, DATEADD(DAY, -30, GETDATE()))

PRINT @today
PRINT @Minus30

To use the values as column names, you'll need to use some dynamic SQL:

DECLARE @today NVARCHAR(15), @Minus30 NVARCHAR(15)

SELECT @today = CONVERT(NVARCHAR(15), CONVERT(DATE, GETDATE())), 
   @Minus30 = CONVERT(NVARCHAR(15), CONVERT(DATE, DATEADD(DAY, -30, GETDATE())))


EXEC ('SELECT ''30'' AS ''' + @today + ''', ''123'' AS ''' + @Minus30 + '''')

Produces:

2015-06-08  2015-05-09
30          123

Upvotes: 2

Sam CD
Sam CD

Reputation: 2097

I haven't actually tried doing this, but you can try to use an sp_rename statement using the variable names that @Tanner suggested:

DECLARE @Minus30 DATE

SELECT @Minus30 = CONVERT(DATE, DATEADD(DAY, -30, GETDATE()))
EXEC sp_rename 'Table.[30days ago]', @Minus30, 'COLUMN'

Upvotes: 0

Related Questions