clear markdown help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335866 fiddles created (27454 in the last week).

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');
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
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
 hidden batch(es)


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;
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
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
 hidden batch(es)


/* 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')
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
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
 hidden batch(es)


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;
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
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
 hidden batch(es)


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;
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
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
 hidden batch(es)


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;
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.
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.
 hidden batch(es)


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;
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
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
 hidden batch(es)