locked
Dynamic pivot table with date range RRS feed

  • Question

  • Hi,

    I need to create a dynamic pivot table to group data by day. The SP would take in a date, and get a date range from an existing table based on the current date. For example, today's date would belong in a period from 29th April, so I would then get all dates from 29th April to today as my date range. This is fine, I can get all the dates from the start of the relevant period up to the current date. However, I then need to use those dates as the column headers in my dynamic pivot table, and this is where I'm running in to trouble. The dynamic pivot table is querying another table, not the table with the date ranges, so I'm having trouble trying to join the two. 

    Here is my code as it currently stands, this has changed a lot while trying to work this out. I've left in some original code, so you can see what I'm trying to do. Basically, I'm trying to get all the relevant records from the Dialler table and have a count for the number of records created each day, with a column for each day, within the time period defined in the date range.

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    ---- Date range
    use [AUX_DB]
    declare @startDate datetime
    declare @endDate datetime
    
    set @endDate = GETDATE()
    
    select @startDate = PeriodStart from ProductionPeriods where PeriodStart <= @endDate and ProdPeriodEnd >= @endDate
    print @startDate
    
    ;WITH cte AS (
    select @startDate as [Day]
      where dateadd(dd, 1, @startDate) <= @endDate
      union all 
      select dateadd(dd, 1, [Day])
      from cte 
      where dateadd(dd, 1, [Day]) <= @endDate
    )
    SELECT DATEPART(dd,[Day]) as myDate into #TempTable
    FROM cte
    OPTION (MAXRECURSION 0)
    select * from #TempTable -- This gets the correct dates
    
     
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           --+ QUOTENAME([Date])
           + QUOTENAME([myDate])
    FROM (SELECT DISTINCT [myDate] FROM #TempTable) AS Dates
    
    -- THIS IS WHERE I'M TRYING TO CREATE A DYNAMIC PIVOT TABLE QUERY. ORIGINAL QUERY BELOW AFTER DROP TABLE
    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
      N'SELECT ' + @ColumnName + '
        FROM #TempTable
        PIVOT(SUM( [totals]   ) 
              FOR [DayNum] IN (' + @ColumnName + ')) AS PVTTable'
              
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    drop table #TempTable
    
    -- ORIGINAL QUERY FOR PIVOT TABLE. WORKS FINE IF COLUMNS ARE HARD CODED, BUT I NEED DYNAMIC COLUMN NAMES: A COLUMN FOR EACH DATE IN #TEMPTABLE
    use [UAT_DB] 
    select *
    from (
    select D.agent as Agent, DATEPART(dd, D.createdon) as DayNum,COUNT(D.createdon) as totals 
    from Dialler D
    -- Createdon from 1st day of production period inclusive, to current date & time
    where D.createdon > DATEADD(dd, -1, @startDate) and D.createdon <= DATEADD(dd, 1, @endDate)
    group by agent, D.createdon  
    ) as calls
    PIVOT
    (
    	sum(totals) 
    	--for [DayNum] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) 
    	--for [DayNum] in ([select * from temp]) 
    	for [DayNum] in (@cols)
    ) AS pvtCalls

    Monday, May 22, 2017 12:33 PM

Answers

  • You need to watch the date range parameter:

    create table Dialler ( CreatedOn datetime, Agent varchar(20))
    
    Insert into Dialler values('2017-04-11 21:08:56.000','Agent1')
    ,('2017-04-12 08:36:28.000','Agent2'),('2017-04-12 08:44:30.000','Agent3')
    ,('2017-04-12 08:48:56.000','Agent4'),('2017-04-12 08:49:33.000','Agent4')
    
    Declare @dt date =current_timestamp
    
    
     
     Declare @sqlCase as NVarchar(4000) =null
     
     
      
    declare @ColumnHeaders VARCHAR(MAX)
    set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN dt=' + quotename(dt,'''') + ' 
    THEN cnt else null end ) as ' + quotename(Convert(varchar(12),dt,107),'[')  + char(10)+char(13)
     FROM (  
      Select Cast(CreatedOn as date) dt, Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-10,@dt)
    Group by Cast(CreatedOn as date) , Agent
    							 ) t
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
      --print @ColumnHeaders
     
    Set @sqlCase  =' Select   Agent,'+   @ColumnHeaders + ' from 
    (
      Select Cast(CreatedOn as date) dt, Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-10,'''+ Convert(varchar(12),@dt,110)+''' )
    Group by Cast(CreatedOn as date) , Agent
    
    ) t
     Group by   Agent ';
         
    print @sqlCase
    EXEC(@sqlCase)
    
     
    
    drop table Dialler

    Monday, May 22, 2017 3:18 PM

All replies


  • Please post sample data + desired result. Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 22, 2017 12:36 PM
    Answerer
  • Thanks for your reply. I'll explain the problem a bit better.

    Today's date is passed into a stored procedure: 22/05/2017

    I would query the ProductionPeriods table, which would return a date range from 29/04/2017 - today.

    I then need to use these 2 dates to query the Dialler table for records created between these 2 dates inclusive. The dialler table looks like this:

    So, all these records would be returned. I then need the pivot table to look like this:

    I think now, that I don't need to have a column for all dates in the date range that is a result of querying the ProductionPeriods table, just whatever dates are returned from querying the Dialler table, in the Created On field.

    Monday, May 22, 2017 12:51 PM
  • create table Dialler ( CreatedOn date, Agent varchar(20))
    
    Insert into Dialler values('2017-05-01','Agent1'),('2017-05-02','Agent2'),('2017-05-02','Agent3'),('2017-05-03','Agent4'),('2017-05-03','Agent4')
    
    Declare @dt date =current_timestamp
    
     
     
     Declare @sqlCase as NVarchar(4000) =null
     
     
      
    declare @ColumnHeaders VARCHAR(MAX)
    set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN CreatedOn=' + quotename(CreatedOn,'''') + ' THEN cnt else null end ) as ' + quotename(CreatedOn,'[')  + char(10)+char(13)
     FROM (  
     Select CreatedOn , Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-4,@dt)
    Group by CreatedOn , Agent
    							 ) t
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
      --print @ColumnHeaders
     
    Set @sqlCase  =' Select   Agent,'+   @ColumnHeaders + ' from 
    (
     Select CreatedOn , Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-4,'''+ Convert(varchar(10),@dt,110)+''' )
    Group by CreatedOn , Agent
    ) t
     Group by   Agent ';
         
    print @sqlCase
    EXEC(@sqlCase)
    
     
    
    drop table Dialler

    Monday, May 22, 2017 1:32 PM
  • Thank you very much. That works for the temp table you create in the code. But when I update the table and column names to my real table, it does not work. I get an error message saying "Incorrect syntax near 'else' ". I think it is because the date format I provided in my sample data is different in the database. I was just trying to use some simple, sample data, but it is incorrect. Here is the actual data from the database:


    So I need it to look like this:

    I am trying to do this myself too, but I don't know enough about pivot tables to do this. So, thanks for your help!


    Monday, May 22, 2017 1:51 PM
  • Can you try to put your new sample date into insert script?  Thanks.

    • Marked as answer by Jingyang Li Monday, May 22, 2017 3:18 PM
    • Unmarked as answer by Jingyang Li Monday, May 22, 2017 3:18 PM
    Monday, May 22, 2017 1:59 PM
  • I put in my new sample date, but all that happens is that I get "5 row(s) affected" in the Messages tab. Here is the script with the proper sample dates:

    create table Dialler ( CreatedOn date, Agent varchar(20))
    
    Insert into Dialler values('2017-04-11 21:08:56.000','Agent1'),('2017-04-12 08:36:28.000','Agent2'),('2017-04-12 08:44:30.000','Agent3'),('2017-04-12 08:48:56.000','Agent4'),('2017-04-12 08:49:33.000','Agent4')
    
    Declare @dt date =current_timestamp
    
     
     
     Declare @sqlCase as NVarchar(4000) =null
     
     
      
    declare @ColumnHeaders VARCHAR(MAX)
    set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN CreatedOn=' + quotename(CreatedOn,'''') + ' THEN cnt else null end ) as ' + quotename(CreatedOn,'[')  + char(10)+char(13)
     FROM (  
     Select CreatedOn , Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-4,@dt)
    Group by CreatedOn , Agent
    							 ) t
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
      --print @ColumnHeaders
     
    Set @sqlCase  =' Select   Agent,'+   @ColumnHeaders + ' from 
    (
     Select CreatedOn , Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-4,'''+ Convert(varchar(10),@dt,110)+''' )
    Group by CreatedOn , Agent
    ) t
     Group by   Agent ';
         
    print @sqlCase
    EXEC(@sqlCase)
    
     
    
    drop table Dialler

    Monday, May 22, 2017 2:08 PM
  • You need to watch the date range parameter:

    create table Dialler ( CreatedOn datetime, Agent varchar(20))
    
    Insert into Dialler values('2017-04-11 21:08:56.000','Agent1')
    ,('2017-04-12 08:36:28.000','Agent2'),('2017-04-12 08:44:30.000','Agent3')
    ,('2017-04-12 08:48:56.000','Agent4'),('2017-04-12 08:49:33.000','Agent4')
    
    Declare @dt date =current_timestamp
    
    
     
     Declare @sqlCase as NVarchar(4000) =null
     
     
      
    declare @ColumnHeaders VARCHAR(MAX)
    set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN dt=' + quotename(dt,'''') + ' 
    THEN cnt else null end ) as ' + quotename(Convert(varchar(12),dt,107),'[')  + char(10)+char(13)
     FROM (  
      Select Cast(CreatedOn as date) dt, Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-10,@dt)
    Group by Cast(CreatedOn as date) , Agent
    							 ) t
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
      --print @ColumnHeaders
     
    Set @sqlCase  =' Select   Agent,'+   @ColumnHeaders + ' from 
    (
      Select Cast(CreatedOn as date) dt, Agent, count(*) cnt
     from Dialler
     WHERE CreatedOn>=dateadd(week,-10,'''+ Convert(varchar(12),@dt,110)+''' )
    Group by Cast(CreatedOn as date) , Agent
    
    ) t
     Group by   Agent ';
         
    print @sqlCase
    EXEC(@sqlCase)
    
     
    
    drop table Dialler

    Monday, May 22, 2017 3:18 PM