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.In

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 <= '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 <= 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 >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < 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 >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < 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] |

StackExchange Database Administrators Q#196362, answer score: 9