T-SQL Record Separator

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:
copyraw
SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/
  1.  SELECT 
  2.      DATENAME(dw, StartDate) AS 'Day' 
  3.  FROM 
  4.      Timetable 
  5.  ORDER BY 
  6.      StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC 
  7.   
  8.  -- Yields 
  9.  /* 
  10.  Day 
  11.  ---------------- 
  12.  Monday 
  13.  Monday 
  14.  Monday 
  15.  Tuesday 
  16.  Tuesday 
  17.  Wednesday 
  18.  Thursday 
  19.  Thursday 
  20.  Thursday 
  21.  Thursday 
  22.  Friday 
  23.  Friday 
  24.  */ 

This is actually part of a SQL query (Transact-SQL) used in an Excel file. Some of our end customers are not even computer literate so the SQL query had to do the bulk of the work. The SQL query we send to the database has to insert separator rows so ideally we would like:
copyraw
Day              
---------------- 
Monday
Monday
Monday

Tuesday
Tuesday

Wednesday

Thursday
Thursday
Thursday
Thursday

Friday
Friday
  1.  Day 
  2.  ---------------- 
  3.  Monday 
  4.  Monday 
  5.  Monday 
  6.   
  7.  Tuesday 
  8.  Tuesday 
  9.   
  10.  Wednesday 
  11.   
  12.  Thursday 
  13.  Thursday 
  14.  Thursday 
  15.  Thursday 
  16.   
  17.  Friday 
  18.  Friday 

The Official Gist
Okay so the below article with the 3 stages can be a little confusing. When I wrote the support documentation which makes use of this little trick for my managers, I explained it as displayed in the following image (apologies for the image but it's still clear as mud in my head):
Now these are the cases after assigning main query to T1.

Oracle SQL*Plus
Only for display/print but SQL*Plus has a nifty separator record activated similarly to the following:
copyraw
SET RECSEP WRAPPED
SET RECSEPCHAR "-"
  1.  SET RECSEP WRAPPED 
  2.  SET RECSEPCHAR "-" 

Stage #1:   Duplicate the table and create a separator marker
Granted this will slow the query down a touch and only adds a check column which marks rows (identifying when a separator is needed) but this works. Inspired by Namwar Rizvi's blog, I wrapped my SQL query within a common table expression:
copyraw
--Create CTE
WITH tblDifference AS
(
	SELECT 
		t1.*,
		ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber'
	FROM 
		(
		-- start your original SQL query and save results as table t1

			SELECT
				-- perform formatting to extract the minimum data here (eg. datetime into date, etc)
				*
			FROM 
				[pretendDatabase].[pretendSchema].[pretendTable] t0
			WHERE 
				[pretendField]='elephantastic'

		-- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by)
		) AS t1
)

-- Query that will display extracted data
SELECT
	t2.*,
	CASE
		WHEN t2.Day=t3.Day THEN NULL
		ELSE 'Separator Marker'
	END as 'Marker'	
FROM
	tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1
ORDER BY 
	t2.RowNumber


-- -------------------------------------------------------------------------------------------
-- Yields

/*
Day		Date		Room		Start	Finish		Marker
--------------- --------------- --------------- ------- --------------- -----
Monday		10/10/2011	A001		08:00	17:00		NULL
Monday		10/10/2011	A002		08:00	17:00		NULL
Monday		10/10/2011	A003		08:00	17:00		Separator Marker
Tuesday		11/10/2011	A001		08:00	17:00		NULL
Tuesday		11/10/2011	A002		08:00	17:00		Separator Marker
...
Sunday		16/10/2011	A007		08:00	17:00		NULL
Sunday		16/10/2011	A009		08:00	17:00		Separator Marker
*/
  1.  --Create CTE 
  2.  WITH tblDifference AS 
  3.  ( 
  4.      SELECT 
  5.          t1.*, 
  6.          ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber' 
  7.      FROM 
  8.          ( 
  9.          -- start your original SQL query and save results as table t1 
  10.   
  11.              SELECT 
  12.                  -- perform formatting to extract the minimum data here (eg. datetime into date, etc) 
  13.                  * 
  14.              FROM 
  15.                  [pretendDatabase].[pretendSchema].[pretendTable] t0 
  16.              WHERE 
  17.                  [pretendField]='elephantastic' 
  18.   
  19.          -- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by) 
  20.          ) AS t1 
  21.  ) 
  22.   
  23.  -- Query that will display extracted data 
  24.  SELECT 
  25.      t2.*, 
  26.      CASE 
  27.          WHEN t2.Day=t3.Day THEN NULL 
  28.          ELSE 'Separator Marker' 
  29.      END as 'Marker' 
  30.  FROM 
  31.      tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 
  32.  ORDER BY 
  33.      t2.RowNumber 
  34.   
  35.   
  36.  -- ------------------------------------------------------------------------------------------- 
  37.  -- Yields 
  38.   
  39.  /* 
  40.  Day        Date        Room        Start    Finish        Marker 
  41.  --------------- --------------- --------------- ------- --------------- ----- 
  42.  Monday        10/10/2011    A001        08:00    17:00        NULL 
  43.  Monday        10/10/2011    A002        08:00    17:00        NULL 
  44.  Monday        10/10/2011    A003        08:00    17:00        Separator Marker 
  45.  Tuesday        11/10/2011    A001        08:00    17:00        NULL 
  46.  Tuesday        11/10/2011    A002        08:00    17:00        Separator Marker 
  47.  ... 
  48.  Sunday        16/10/2011    A007        08:00    17:00        NULL 
  49.  Sunday        16/10/2011    A009        08:00    17:00        Separator Marker 
  50.  */ 
As I mentioned at the beginning of this article, I am comparing on Day names. The above query generates a last column which is either empty or says "Separator Marker". Based on the CTE above, I now need to add an actual row separating the different days which is either blank or full of hyphens/dashes.

Stage #2: Replace values in last data row with blanks
The trick to this is to get Stage #1 working for you with your query and then for every column of data that you are extracting, you will need to use the case statement to either put the value or your empty row. Here's an example of the query for a timetabling solution with spacing rows between days. This worked ok but pay special attention to Stage #3 afterwards otherwise if you stop in Stage #2 you'll be missing data:
copyraw
--Create CTE
WITH tblDifference AS
(
	SELECT 
		t1.*,
		ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber'
	FROM 
		(
		-- start your original SQL query and save results as table t1

			SELECT
				-- perform formatting to extract the minimum data here (eg. datetime into date, etc)
				*
			FROM 
				[pretendDatabase].[pretendSchema].[pretendTable] t0
			WHERE 
				[pretendField]='elephantastic'

		-- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by)
		) AS t1
)

-- Query that will display extracted data
SELECT
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Day]
		ELSE NULL
	END as 'Day',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Date]
		ELSE NULL
	END as 'Date',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Room]
		ELSE NULL
	END as 'Room',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Start]
		ELSE NULL
	END as 'Start',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Finish]
		ELSE NULL
	END as 'Finish',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Event Type]
		ELSE NULL
	END as 'Event Type',
FROM
	tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1
Order by 
	t2.RowNumber	


-- -------------------------------------------------------------------------------------------
-- Yields

/*
Day		Date		Room		Start	Finish		...
--------------- --------------- --------------- ------- --------------- -----
Monday		10/10/2011	A001		08:00	17:00
Monday		10/10/2011	A002		08:00	17:00
NULL		NULL		NULL		NULL	NULL		NULL
Tuesday		11/10/2011	A001		08:00	17:00
NULL		NULL		NULL		NULL	NULL		NULL
...
Sunday		16/10/2011	A007		08:00	17:00
NULL		NULL		NULL		NULL	NULL		NULL
*/
  1.  --Create CTE 
  2.  WITH tblDifference AS 
  3.  ( 
  4.      SELECT 
  5.          t1.*, 
  6.          ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber' 
  7.      FROM 
  8.          ( 
  9.          -- start your original SQL query and save results as table t1 
  10.   
  11.              SELECT 
  12.                  -- perform formatting to extract the minimum data here (eg. datetime into date, etc) 
  13.                  * 
  14.              FROM 
  15.                  [pretendDatabase].[pretendSchema].[pretendTable] t0 
  16.              WHERE 
  17.                  [pretendField]='elephantastic' 
  18.   
  19.          -- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by) 
  20.          ) AS t1 
  21.  ) 
  22.   
  23.  -- Query that will display extracted data 
  24.  SELECT 
  25.      CASE 
  26.          WHEN t2.Day=t3.Day THEN t2.[Day] 
  27.          ELSE NULL 
  28.      END as 'Day', 
  29.      CASE 
  30.          WHEN t2.Day=t3.Day THEN t2.[Date] 
  31.          ELSE NULL 
  32.      END as 'Date', 
  33.      CASE 
  34.          WHEN t2.Day=t3.Day THEN t2.[Room] 
  35.          ELSE NULL 
  36.      END as 'Room', 
  37.      CASE 
  38.          WHEN t2.Day=t3.Day THEN t2.[Start] 
  39.          ELSE NULL 
  40.      END as 'Start', 
  41.      CASE 
  42.          WHEN t2.Day=t3.Day THEN t2.[Finish] 
  43.          ELSE NULL 
  44.      END as 'Finish', 
  45.      CASE 
  46.          WHEN t2.Day=t3.Day THEN t2.[Event Type] 
  47.          ELSE NULL 
  48.      END as 'Event Type', 
  49.  FROM 
  50.      tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 
  51.  Order by 
  52.      t2.RowNumber 
  53.   
  54.   
  55.  -- ------------------------------------------------------------------------------------------- 
  56.  -- Yields 
  57.   
  58.  /* 
  59.  Day        Date        Room        Start    Finish        ... 
  60.  --------------- --------------- --------------- ------- --------------- ----- 
  61.  Monday        10/10/2011    A001        08:00    17:00 
  62.  Monday        10/10/2011    A002        08:00    17:00 
  63.  NULL        NULL        NULL        NULL    NULL        NULL 
  64.  Tuesday        11/10/2011    A001        08:00    17:00 
  65.  NULL        NULL        NULL        NULL    NULL        NULL 
  66.  ... 
  67.  Sunday        16/10/2011    A007        08:00    17:00 
  68.  NULL        NULL        NULL        NULL    NULL        NULL 
  69.  */ 
Note that my data is inaccurate as the last row of each grouped data has been changed to NULL. Also note how the actual select query is pulling data from my CTE in its post-formatted form. I perform all the formatting within the CTE and when it comes to the CASE events comparing to the previous row, all I do is say display this value or display a blank value based on if this is a marked row (current row is different to previous row)...


Stage #3: Use dummy separator row
Thinking logically, Stage #2 overwrites data in the last row of the day (the one marked with a separator) which happens to be data my customer wants. What if that last row of that day was actually blank and didn't have any of the data we want in the end? The aim here then is to get the inserted blank row to always be the last one on the day and then subsequently marked with the separator marker of Stage #1 (note that here separator marker is marked by day1 does not equal day2 rather than a column called marker):
copyraw
--Create CTE
WITH tblDifference AS
(
	SELECT 
		t1.*,
		ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber'
	FROM 
		(
		-- start your original SQL query and save results as table t1

			SELECT
				-- perform formatting to extract the minimum data here (eg. datetime into date, etc)
				*
			FROM 
				[pretendDatabase].[pretendSchema].[pretendTable] t0
			WHERE 
				[pretendField]='elephantastic'

			-- insert some empty rows to blank out later as separators (select from your existing table, needs to contain data to order by so that it's the last row of each day -- if grouping by day -- basically insert a row that when ordered will fit where the separator is going to go)
			UNION ALL
			SELECT TOP 1 'Monday' AS Day, '10/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Tuesday' AS Day, '11/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Wednesday' AS Day, '12/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Thursday' AS Day, '13/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Friday' AS Day, '14/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Saturday' AS Day, '15/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'
			UNION ALL
			SELECT TOP 1 'Sunday' AS Day, '16/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic'

		-- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by)
		) AS t1
)

-- Query that will display extracted data
SELECT
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Day]
		ELSE NULL
	END as 'Day',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Date]
		ELSE NULL
	END as 'Date',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Room]
		ELSE NULL
	END as 'Room',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Start]
		ELSE NULL
	END as 'Start',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Finish]
		ELSE NULL
	END as 'Finish',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Event Type]
		ELSE NULL
	END as 'Event Type',
FROM
	tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1
Order by 
	t2.RowNumber	


-- -------------------------------------------------------------------------------------------
-- Yields

/*
Day		Date		Room		Start	Finish		...
--------------- --------------- --------------- ------- --------------- -----
Monday		10/10/2011	A001		08:00	17:00
Monday		10/10/2011	A002		08:00	17:00
Monday		10/10/2011	A003		08:00	17:00		
NULL		NULL		NULL		NULL	NULL
Tuesday		11/10/2011	A001		08:00	17:00
Tuesday		11/10/2011	A002		08:00	17:00		
NULL		NULL		NULL		NULL	NULL
...
Sunday		16/10/2011	A007		08:00	17:00
Sunday		16/10/2011	A009		08:00	17:00		
NULL		NULL		NULL		NULL	NULL
*/
  1.  --Create CTE 
  2.  WITH tblDifference AS 
  3.  ( 
  4.      SELECT 
  5.          t1.*, 
  6.          ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber' 
  7.      FROM 
  8.          ( 
  9.          -- start your original SQL query and save results as table t1 
  10.   
  11.              SELECT 
  12.                  -- perform formatting to extract the minimum data here (eg. datetime into date, etc) 
  13.                  * 
  14.              FROM 
  15.                  [pretendDatabase].[pretendSchema].[pretendTable] t0 
  16.              WHERE 
  17.                  [pretendField]='elephantastic' 
  18.   
  19.              -- insert some empty rows to blank out later as separators (select from your existing table, needs to contain data to order by so that it's the last row of each day -- if grouping by day -- basically insert a row that when ordered will fit where the separator is going to go) 
  20.              UNION ALL 
  21.              SELECT TOP 1 'Monday' AS Day, '10/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  22.              UNION ALL 
  23.              SELECT TOP 1 'Tuesday' AS Day, '11/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  24.              UNION ALL 
  25.              SELECT TOP 1 'Wednesday' AS Day, '12/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  26.              UNION ALL 
  27.              SELECT TOP 1 'Thursday' AS Day, '13/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  28.              UNION ALL 
  29.              SELECT TOP 1 'Friday' AS Day, '14/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  30.              UNION ALL 
  31.              SELECT TOP 1 'Saturday' AS Day, '15/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  32.              UNION ALL 
  33.              SELECT TOP 1 'Sunday' AS Day, '16/10/2011' AS Date, NULL, '23:59', NULL, NULL FROM [pretendDatabase].[pretendSchema].[pretendTable] WHERE [pretendField]='elephantastic' 
  34.   
  35.          -- end your original SQL query here (note no "order by" allowed -- refer to 6th line of this query for order by) 
  36.          ) AS t1 
  37.  ) 
  38.   
  39.  -- Query that will display extracted data 
  40.  SELECT 
  41.      CASE 
  42.          WHEN t2.Day=t3.Day THEN t2.[Day] 
  43.          ELSE NULL 
  44.      END as 'Day', 
  45.      CASE 
  46.          WHEN t2.Day=t3.Day THEN t2.[Date] 
  47.          ELSE NULL 
  48.      END as 'Date', 
  49.      CASE 
  50.          WHEN t2.Day=t3.Day THEN t2.[Room] 
  51.          ELSE NULL 
  52.      END as 'Room', 
  53.      CASE 
  54.          WHEN t2.Day=t3.Day THEN t2.[Start] 
  55.          ELSE NULL 
  56.      END as 'Start', 
  57.      CASE 
  58.          WHEN t2.Day=t3.Day THEN t2.[Finish] 
  59.          ELSE NULL 
  60.      END as 'Finish', 
  61.      CASE 
  62.          WHEN t2.Day=t3.Day THEN t2.[Event Type] 
  63.          ELSE NULL 
  64.      END as 'Event Type', 
  65.  FROM 
  66.      tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 
  67.  Order by 
  68.      t2.RowNumber 
  69.   
  70.   
  71.  -- ------------------------------------------------------------------------------------------- 
  72.  -- Yields 
  73.   
  74.  /* 
  75.  Day        Date        Room        Start    Finish        ... 
  76.  --------------- --------------- --------------- ------- --------------- ----- 
  77.  Monday        10/10/2011    A001        08:00    17:00 
  78.  Monday        10/10/2011    A002        08:00    17:00 
  79.  Monday        10/10/2011    A003        08:00    17:00 
  80.  NULL        NULL        NULL        NULL    NULL 
  81.  Tuesday        11/10/2011    A001        08:00    17:00 
  82.  Tuesday        11/10/2011    A002        08:00    17:00 
  83.  NULL        NULL        NULL        NULL    NULL 
  84.  ... 
  85.  Sunday        16/10/2011    A007        08:00    17:00 
  86.  Sunday        16/10/2011    A009        08:00    17:00 
  87.  NULL        NULL        NULL        NULL    NULL 
  88.  */ 
YAY!!! Note that in this final stage, you can replace NULL with lots of hyphens (if you want lots of dashes/hyphens as separators) or replace with whatever character. I just put NULL because I wanted an empty row.

My final query
This displays all room bookings for today and the next 6 days. Note how I can't use normal dates because the database wasn't designed with timestamps (despite being a timetabling and room booking system). But I do get given a weekday and the start date of the week...
copyraw
WITH tblDifference AS
(
	SELECT 
		t1.*, 
		ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber'
	FROM
		(
	
		SELECT
			DATENAME(dw, tt.[WeekDay]-1) AS 'Day',
			dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber])) AS 'Date',
			tt.[RoomId] AS 'Room', 
			tt.[StartTime] AS 'Start', 
			tt.[FinishTime] AS 'Finish',
			sd.[Descrip] AS 'Event Type', 
			sd.[Notes] AS 'Booking Notes',
			c.[Name] AS 'Contact',
			CHAR(10)+c.[Owner]+CHAR(10) AS 'Dept', 
			CASE 
				WHEN CHARINDEX('/', c.[Phone]) > 0 THEN REPLACE(REPLACE(c.[Phone], ' ', ''), '/', CHAR(10))
				WHEN CHARINDEX(' or ', c.[Phone]) > 0 THEN REPLACE(REPLACE(c.[Phone], ' or ', CHAR(10)), ' ', '')
				WHEN LEN(c.[Phone]) > 20 THEN REPLACE(REPLACE(c.[Phone], '01202 ', '01202'), ' ', CHAR(10))
				ELSE c.[Phone]
			END AS 'Phone',
			tt.[SiteId] AS 'Site',
			wm.[WeekNumber] AS FacilityWeek,
			CONVERT(CHAR(10), GETDATE(), 103) AS 'WeekStart',
			CONVERT(CHAR(10), DATEADD(day, 6, GETDATE()), 103) AS 'WeekEnd',
			SD.[SetId] AS 'Set'
		
		FROM [pretendDatabase].[pretendSchema].[SLOTDETAILS] sd
			INNER JOIN [pretendDatabase].[pretendSchema].[TIMETABLE] tt 
				ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
			INNER JOIN [pretendDatabase].[pretendSchema].[CONTACT] c 
				ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
			INNER JOIN [pretendDatabase].[pretendSchema].[WEEKMAPNUMERIC] wm 
				ON wm.[WeekId] = tt.[WeekId] AND wm.[SetId] = tt.[SetId]
		
		WHERE sd.[SetId] = (SELECT TOP 1 SetId FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, -6, GETDATE()) AND DATEADD(day, 6, GETDATE()))
			AND tt.[Status]  3		
			AND (
				wm.[WeekNumber] = (SELECT TOP 1 WeekNumber FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, -6, GETDATE()) AND DATEADD(day, 1, GETDATE()))
				OR
				wm.[WeekNumber] = (SELECT TOP 1 WeekNumber FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, 1, GETDATE()) AND DATEADD(day, 6, GETDATE()))
			)
			AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'
			AND tt.[RoomId]  ''
			AND sd.[SourcesId]='RB'
			AND tt.[SiteId]='LC'
			AND (
				dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber])) >= DATEADD(day, -1, GETDATE())
				AND
				dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber]))
  1.  WITH tblDifference AS 
  2.  ( 
  3.      SELECT 
  4.          t1.*, 
  5.          ROW_NUMBER() OVER (ORDER BY t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber' 
  6.      FROM 
  7.          ( 
  8.   
  9.          SELECT 
  10.              DATENAME(dw, tt.[WeekDay]-1) AS 'Day', 
  11.              dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber])) AS 'Date', 
  12.              tt.[RoomId] AS 'Room', 
  13.              tt.[StartTime] AS 'Start', 
  14.              tt.[FinishTime] AS 'Finish', 
  15.              sd.[Descrip] AS 'Event Type', 
  16.              sd.[Notes] AS 'Booking Notes', 
  17.              c.[Name] AS 'Contact', 
  18.              CHAR(10)+c.[Owner]+CHAR(10) AS 'Dept', 
  19.              CASE 
  20.                  WHEN CHARINDEX('/', c.[Phone]) > 0 THEN REPLACE(REPLACE(c.[Phone], ' ', ''), '/', CHAR(10)) 
  21.                  WHEN CHARINDEX(' or ', c.[Phone]) > 0 THEN REPLACE(REPLACE(c.[Phone], ' or ', CHAR(10)), ' ', '') 
  22.                  WHEN LEN(c.[Phone]) > 20 THEN REPLACE(REPLACE(c.[Phone], '01202 ', '01202'), ' ', CHAR(10)) 
  23.                  ELSE c.[Phone] 
  24.              END AS 'Phone', 
  25.              tt.[SiteId] AS 'Site', 
  26.              wm.[WeekNumber] AS FacilityWeek, 
  27.              CONVERT(CHAR(10), GETDATE(), 103) AS 'WeekStart', 
  28.              CONVERT(CHAR(10), DATEADD(day, 6, GETDATE()), 103) AS 'WeekEnd', 
  29.              SD.[SetId] AS 'Set' 
  30.   
  31.          FROM [pretendDatabase].[pretendSchema].[SLOTDETAILS] sd 
  32.              INNER JOIN [pretendDatabase].[pretendSchema].[TIMETABLE] tt 
  33.                  ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] 
  34.              INNER JOIN [pretendDatabase].[pretendSchema].[CONTACT] c 
  35.                  ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] 
  36.              INNER JOIN [pretendDatabase].[pretendSchema].[WEEKMAPNUMERIC] wm 
  37.                  ON wm.[WeekId] = tt.[WeekId] AND wm.[SetId] = tt.[SetId] 
  38.   
  39.          WHERE sd.[SetId] = (SELECT TOP 1 SetId FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, -6, GETDATE()) AND DATEADD(day, 6, GETDATE())) 
  40.              AND tt.[Status]  3 
  41.              AND ( 
  42.                  wm.[WeekNumber] = (SELECT TOP 1 WeekNumber FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, -6, GETDATE()) AND DATEADD(day, 1, GETDATE())) 
  43.                  OR 
  44.                  wm.[WeekNumber] = (SELECT TOP 1 WeekNumber FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE StartDate BETWEEN DATEADD(day, 1, GETDATE()) AND DATEADD(day, 6, GETDATE())) 
  45.              ) 
  46.              AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' 
  47.              AND tt.[RoomId]  '' 
  48.              AND sd.[SourcesId]='RB' 
  49.              AND tt.[SiteId]='LC' 
  50.              AND ( 
  51.                  dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber])) >= DATEADD(day, -1, GETDATE()) 
  52.                  AND 
  53.                  dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE WeekNumber=wm.[WeekNumber])) 

Other Issues
  1. Remember to check in SSMS if the row_number is in incremental order. I had to add all "Order By" criteria used in the original SQL query:
  2. Number and Date data type columns cannot have a space as value and will need to be NULL or 0
  3. Stage #3 is the tricky part. You need to think about what data to put in rows so that they are ordered and positioned exactly where the separator markers will be (so that they are the last entry of the grouping).
  4. Considering the last row of each day is overwritten with NULL values, if you incorrectly position a row of data in the place of where a separator should be (last row valid for that day), then that data will not display.
To quickly check where all the NULL rows will be inserted, display all data from table t2 and comment out the case clauses:
copyraw
...


-- Query that will display extracted data
SELECT
	t2.*
	/* -- the following cases are now commented
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Day]
		ELSE NULL
	END as 'Day',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Date]
		ELSE NULL
	END as 'Date',	
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Room]
		ELSE NULL
	END as 'Room',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Start]
		ELSE NULL
	END as 'Start',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Finish]
		ELSE NULL
	END as 'Finish',
	CASE
		WHEN t2.Day=t3.Day THEN t2.[Event Type]
		ELSE NULL
	END as 'Event Type',
	*/
FROM
	tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1
Order by 
	t2.RowNumber
  1.  ... 
  2.   
  3.   
  4.  -- Query that will display extracted data 
  5.  SELECT 
  6.      t2.* 
  7.      /* -- the following cases are now commented 
  8.      CASE 
  9.          WHEN t2.Day=t3.Day THEN t2.[Day] 
  10.          ELSE NULL 
  11.      END as 'Day', 
  12.      CASE 
  13.          WHEN t2.Day=t3.Day THEN t2.[Date] 
  14.          ELSE NULL 
  15.      END as 'Date', 
  16.      CASE 
  17.          WHEN t2.Day=t3.Day THEN t2.[Room] 
  18.          ELSE NULL 
  19.      END as 'Room', 
  20.      CASE 
  21.          WHEN t2.Day=t3.Day THEN t2.[Start] 
  22.          ELSE NULL 
  23.      END as 'Start', 
  24.      CASE 
  25.          WHEN t2.Day=t3.Day THEN t2.[Finish] 
  26.          ELSE NULL 
  27.      END as 'Finish', 
  28.      CASE 
  29.          WHEN t2.Day=t3.Day THEN t2.[Event Type] 
  30.          ELSE NULL 
  31.      END as 'Event Type', 
  32.      */ 
  33.  FROM 
  34.      tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 
  35.  Order by 
  36.      t2.RowNumber 

This concept
  1. All-in-one query without declaring variables, using stored procedures, loops, functions, etc.
  2. Uses row_number instead of cursors or loops.
  3. Can be applied to MySQL queries (but because MySQL is so much better I'll post an equivalent article later on)
  4. Can put any characters instead of NULL to create a separator.
Anyone in IT who says something is impossible, doesn't belong in IT - just a personal thought.

If someone asks you why are you doing the formatting in the back-end, ask them if they have ever used Microsoft Excel and then continue the question "to connect to a database?". Anyone who has will tell you that Excel automatically resizes its column widths and row heights ignoring any formatting you may have added to the Excel file. If everytime the end-user clicks on the "Refresh" button and they have to re-format the report, then we're not doing our job as IT to speed things up.

Searches that got me here
  • insert separator row between specific rows
  • t-sql record separater
  • Insert blank row between groups of rows
  • print record separator using a single SQL query
  • inserting Space when a Break Column's Value Changes
  • transact-sql compare previous and current row
Category: Transact-SQL :: Article: 394

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.