Advice on T-SQL script, change to use dynamic SQL The below script will query Ola's Maintenance Solution results table (CommandLog) and come up with a comparison on the bi-weekly reindex exec duration, for the last 4 weeks.

The way it looks right now, it's a quick hack to help me tune the maintenance windows. But I would like to remove the date hardcoding, so that I don't have to manually add every future weekend as a new JOIN. 

Please provide a complete rewrite (dynamic SQL?) or just some hints on how to achieve this or some other useful features to be included. Ok to change the sproc and add extra output to the table. Using SQL 2016. If there's already a script out there that serves the purpose, happy to go with that one.

WITH t0 AS 
(
SELECT ObjectName, IndexName, IndexType
FROM Tools.dbo.CommandLog
WHERE 1=1
AND DatabaseName = 'testdb'
AND CommandType = 'ALTER_INDEX'
GROUP BY ObjectName, IndexName, IndexType
)

SELECT 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
,DATEDIFF(ss,t1.StartTime,t1.EndTime) as '20-40 01-06'
,DATEDIFF(ss,t2.StartTime,t2.EndTime) as '5-40 01-07'
,DATEDIFF(ss,t3.StartTime,t3.EndTime) as '20-40 01-13'
,DATEDIFF(ss,t4.StartTime,t4.EndTime) as '5-40 01-14'
,DATEDIFF(ss,t5.StartTime,t5.EndTime) as '20-40 01-20'
,DATEDIFF(ss,t6.StartTime,t6.EndTime) as '5-40 01-21'
,DATEDIFF(ss,t7.StartTime,t7.EndTime) as '20-40 01-27'
,DATEDIFF(ss,t8.StartTime,t8.EndTime) as '5-40 01-28'
FROM t0
LEFT JOIN Tools.dbo.CommandLog as t1 ON t0.ObjectName = t1.ObjectName AND t0.IndexName = t1.IndexName AND t1.StartTime BETWEEN '2018-01-06 00:30:00' AND '2018-01-06 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t2 ON t0.ObjectName = t2.ObjectName AND t0.IndexName = t2.IndexName AND t2.StartTime BETWEEN '2018-01-07 00:30:00' AND '2018-01-07 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t3 ON t0.ObjectName = t3.ObjectName AND t0.IndexName = t3.IndexName AND t3.StartTime BETWEEN '2018-01-13 00:30:00' AND '2018-01-13 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t4 ON t0.ObjectName = t4.ObjectName AND t0.IndexName = t4.IndexName AND t4.StartTime BETWEEN '2018-01-14 00:30:00' AND '2018-01-14 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t5 ON t0.ObjectName = t5.ObjectName AND t0.IndexName = t5.IndexName AND t5.StartTime BETWEEN '2018-01-20 00:30:00' AND '2018-01-20 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t6 ON t0.ObjectName = t6.ObjectName AND t0.IndexName = t6.IndexName AND t6.StartTime BETWEEN '2018-01-21 00:30:00' AND '2018-01-21 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t7 ON t0.ObjectName = t7.ObjectName AND t0.IndexName = t7.IndexName AND t7.StartTime BETWEEN '2018-01-27 00:30:00' AND '2018-01-27 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t8 ON t0.ObjectName = t8.ObjectName AND t0.IndexName = t8.IndexName AND t8.StartTime BETWEEN '2018-01-28 00:30:00' AND '2018-01-28 23:59:59'
WHERE 1=1
ORDER BY 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
Calendar table

As LowlyDBA and Anti-weakpasswords have pointed out in their comments you can find more than one method to generate a calendar table:


Bones of SQL - The Calendar Table
Calendar Tables in T-SQL
Creating a date dimension or calendar table in SQL Server


For the sake of the question I'll generate a (basic) calendar table just to show you how to use it with your data, you'll find examples on-line that use more detailed fields (seconds, day of year, quarters, etc)




CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date &lt;= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, 
                                 DAY(@date), 
                                 DATEPART(weekday, @date), 
                                 DATENAME(weekday, @date));

    SET @date = DATEADD(day, 1, @date);
END



Accordingly with your query, it seems that you're interested on get results for Saturday and Sunday only. In this case you should filter Calendar table by day name or by day of week. Have a look at SET DATEFIRST if you want to filter by day of week. 


SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday');
GO



cDate               | cDay | cDayOfWeek | cDayName
:------------------ | ---: | ---------: | :-------
06/01/2018 00:00:00 |    6 |          7 | Saturday
07/01/2018 00:00:00 |    7 |          1 | Sunday  
13/01/2018 00:00:00 |   13 |          7 | Saturday
14/01/2018 00:00:00 |   14 |          1 | Sunday  
20/01/2018 00:00:00 |   20 |          7 | Saturday
21/01/2018 00:00:00 |   21 |          1 | Sunday  
27/01/2018 00:00:00 |   27 |          7 | Saturday
28/01/2018 00:00:00 |   28 |          1 | Sunday  



Populating CommandLog table with random data

Now let me simulate a CommandLog table and populate it with 1000 random dates between Jan-01 and Jan-31.




CREATE TABLE CommandLog 
(
    ObjectName varchar(10), 
    IndexName varchar(10), 
    IndexType int, 
    StartTime datetime,
    EndTime datetime
);

DECLARE @step int = 1;
DECLARE @startDate datetime;

WHILE @step &lt;= 1000
BEGIN
    SET @startDate = DATEADD(minute, RAND() * 59, 
                             DATEADD(hour, RAND() * 23, 
                                     DATEADD(day, RAND() * 31, '20180101')))

    INSERT INTO CommandLog VALUES('OBJ1', 
                                  'INDEX1', 
                                  1, 
                                  @startDate, 
                                  DATEADD(second, RAND() * 59,@startDate));
    SET @step += 1;
END

/* just to check first 10 records */
SELECT TOP 10 * FROM CommandLog;
GO



ObjectName | IndexName | IndexType | StartTime           | EndTime            
:--------- | :-------- | --------: | :------------------ | :------------------
OBJ1       | INDEX1    |         1 | 16/01/2018 04:36:00 | 16/01/2018 04:36:43
OBJ1       | INDEX1    |         1 | 06/01/2018 09:11:00 | 06/01/2018 09:11:33
OBJ1       | INDEX1    |         1 | 05/01/2018 14:23:00 | 05/01/2018 14:23:43
OBJ1       | INDEX1    |         1 | 10/01/2018 19:53:00 | 10/01/2018 19:53:11
OBJ1       | INDEX1    |         1 | 14/01/2018 14:31:00 | 14/01/2018 14:31:53
OBJ1       | INDEX1    |         1 | 06/01/2018 18:43:00 | 06/01/2018 18:43:07
OBJ1       | INDEX1    |         1 | 21/01/2018 21:52:00 | 21/01/2018 21:52:41
OBJ1       | INDEX1    |         1 | 28/01/2018 06:51:00 | 28/01/2018 06:51:03
OBJ1       | INDEX1    |         1 | 19/01/2018 08:39:00 | 19/01/2018 08:39:58
OBJ1       | INDEX1    |         1 | 30/01/2018 19:57:00 | 30/01/2018 19:57:50



Formatting data

Ok, so far so good, I've chosen a PIVOT solution in this case, but first I need to format source data to make it possible. Records can be filtered or grouped using the Calendar table.

ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)





/* check first 10 records again */
SELECT     TOP 10 
           cl.ObjectName, cl.IndexName, cl.IndexType, cDayName,
           CASE cDayName 
                WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
           END FormatDate,
           DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GO



ObjectName | IndexName | IndexType | cDayName | FormatDate    | DiffSeconds
:--------- | :-------- | --------: | :------- | :------------ | ----------:
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           7
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          25
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          40
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           0
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          13
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          52
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          50
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          15



PIVOT query

Well, this is the PIVOT query I'm trying to simulate. Keep in mind that PIVOT queries require a well-know number of columns and you often must generate it dynamically.




SELECT ObjectName, IndexName, IndexType,
      [20-40 06-01],[5-40 07-01],[20-40 13-01],[5-40 14-01],
      [20-40 20-01],[5-40 21-01],[20-40 27-01],[5-40 28-01]
FROM
(
    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
               CASE cDayName WHEN 'Saturday' THEN '20-40 ' + FORMAT(cDate, 'dd-MM')
                             WHEN 'Sunday'   THEN '5-40 ' + FORMAT(cDate, 'dd-MM')
                             END FormatDate,
               DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
    FROM       CommandLog cl
    INNER JOIN Calendar c
    ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
    AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)
    WHERE      cDayName IN ('Saturday', 'Sunday')
)src
PIVOT 
(
    SUM(DiffSeconds) FOR FormatDate IN ([20-40 06-01],[5-40 07-01],[20-40 13-01],
                                        [5-40 14-01],[20-40 20-01],[5-40 21-01],
                                        [20-40 27-01],[5-40 28-01])
) pvt;
GO



ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605



Generating dynamic query

You can use STUFF function to dynamically generate columns names. And then simply concatenate strings.




DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ',' + 
                   CASE cDayName 
                     WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                     WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                   END
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =     
    'SELECT ObjectName, IndexName, IndexType, ' + @cols
 + ' FROM
     (
       SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
                  CASE cDayName 
                       WHEN ''Saturday'' THEN ''20-40 '' + FORMAT(cDate, ''dd-MM'')
                       WHEN ''Sunday''   THEN ''5-40 '' + FORMAT(cDate, ''dd-MM'')
                  END FormatDate,
                  DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
       FROM       CommandLog cl
       INNER JOIN Calendar c
       ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
       AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)
       WHERE      cDayName IN (''Saturday'', ''Sunday'')
     )src
    PIVOT 
    (
      SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')'
 + ') pvt';

EXEC sp_executesql @cmd;
GO



Final result



ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605



GROUP BY solution

Ok, it's Saturday afternoon and I have nothing else to do than read some interesting book. Having a look at your query I think you could replace all this table joins by a single GROUP BY + SUM(CASE... query using the Calendar table.

Similar to this one:




SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
           SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 06-01],
           SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 07-01],
           SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 13-01],
           SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 14-01],
           SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 20-01],
           SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 21-01],
           SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 27-01],
           SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 28-01]
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType;
GO



ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605



This query can be generated dynamically too:




DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ', ' + ('SUM(CASE WHEN cDay = ' 
                                 + FORMAT(cDay, 'D')
                                 + ' THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS '
                                 + CASE cDayName 
                                        WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                                        WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                                   END
                                 + ' ')
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =   'SELECT     cl.ObjectName, cl.IndexName, cl.IndexType, '
           + @cols
           + 'FROM       CommandLog cl
              INNER JOIN Calendar c
              ON         cl.StartTime &gt;= DATEADD(minute, 30, c.cDate)
              AND        cl.StartTime &lt; DATEADD(day, 1, c.cDate)
              WHERE      cDayName IN (''Saturday'', ''Sunday'')
              GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType';

/* REMOVE Warning: Null value is eliminated by an aggregate or other SET operation. */
SET ANSI_WARNINGS OFF;

EXEC sp_executesql @cmd;
GO



dbfiddle here