Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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 > > SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday'); > GO > > <pre> > 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 > </pre> <!-- --> > 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 > > <pre> > ObjectName | IndexName | IndexType | StartTime | EndTime > :--------- | :-------- | --------: | :------------------ | :------------------ > OBJ1 | INDEX1 | 1 | 13/01/2018 00:43:00 | 13/01/2018 00:43:38 > OBJ1 | INDEX1 | 1 | 21/01/2018 11:49:00 | 21/01/2018 11:49:22 > OBJ1 | INDEX1 | 1 | 07/01/2018 08:22:00 | 07/01/2018 08:22:10 > OBJ1 | INDEX1 | 1 | 11/01/2018 09:17:00 | 11/01/2018 09:17:19 > OBJ1 | INDEX1 | 1 | 23/01/2018 00:02:00 | 23/01/2018 00:02:37 > OBJ1 | INDEX1 | 1 | 24/01/2018 04:17:00 | 24/01/2018 04:17:23 > OBJ1 | INDEX1 | 1 | 16/01/2018 21:24:00 | 16/01/2018 21:24:12 > OBJ1 | INDEX1 | 1 | 21/01/2018 11:30:00 | 21/01/2018 11:30:25 > OBJ1 | INDEX1 | 1 | 04/01/2018 14:01:00 | 04/01/2018 14:01:58 > OBJ1 | INDEX1 | 1 | 11/01/2018 08:43:00 | 11/01/2018 08:43:38 > </pre> <!-- --> > /* just to check 10 first records */ > 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 > > <pre> > ObjectName | IndexName | IndexType | cDayName | FormatDate | DiffSeconds > :--------- | :-------- | --------: | :------- | :------------ | ----------: > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 24 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 3 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 54 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 52 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 5 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 14 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 17 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 26 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 19 > OBJ1 | INDEX1 | 1 | Saturday | [20-40 06-01] | 16 > </pre> <!-- --> > 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 >= DATEADD(minute, 30, c.cDate) > AND cl.StartTime < 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 > > <pre> > 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 | 1132 | 867 | 1339 | 1234 | 1312 | 923 | 978 | 839 > </pre> <!-- --> > 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 >= DATEADD(minute, 30, c.cDate) > AND cl.StartTime < DATEADD(day, 1, c.cDate) > WHERE cDayName IN (''Saturday'', ''Sunday'') > )src > PIVOT > ( > SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')) pvt'; > > EXEC sp_executesql @cmd; > GO > > <pre> > 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 | 1132 | 867 | 1339 | 1234 | 1312 | 923 | 978 | 839 > </pre> <!-- --> > SELECT cl.ObjectName, cl.IndexName, cl.IndexType, > SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [20-40 06-01], > SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [5-40 07-01], > SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [20-40 13-01], > SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [5-40 14-01], > SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [20-40 20-01], > SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [5-40 21-01], > SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [20-40 27-01], > SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS [5-40 28-01] > 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') > GROUP BY cl.ObjectName, cl.IndexName, cl.IndexType; > GO > > <pre> > 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 | 1132 | 867 | 1339 | 1234 | 1312 | 923 | 978 | 839 > Warning: Null value is eliminated by an aggregate or other SET operation. > </pre> <!-- --> > 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 >= DATEADD(minute, 30, c.cDate) > AND cl.StartTime < 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 > > <pre> > 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 | 1132 | 867 | 1339 | 1234 | 1312 | 923 | 978 | 839 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=3dd1931030657e04cb8e7196e353de9c)*
back to fiddle