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:
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 */
- 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
- */
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:
Day ---------------- Monday Monday Monday Tuesday Tuesday Wednesday Thursday Thursday Thursday Thursday Friday Friday
- Day
- ----------------
- Monday
- Monday
- Monday
- Tuesday
- Tuesday
- Wednesday
- Thursday
- Thursday
- Thursday
- Thursday
- Friday
- 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):
Oracle SQL*Plus
Only for display/print but SQL*Plus has a nifty separator record activated similarly to the following:
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:
--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 */
- --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
- */
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:
--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 */
- --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
- */
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):
--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 */
- --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
- */
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...
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]))
- 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]))
Other Issues
- 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:
- Number and Date data type columns cannot have a space as value and will need to be NULL or 0
- 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).
- 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.
... -- 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
- ...
- -- 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
This concept
- All-in-one query without declaring variables, using stored procedures, loops, functions, etc.
- Uses row_number instead of cursors or loops.
- Can be applied to MySQL queries (but because MySQL is so much better I'll post an equivalent article later on)
- Can put any characters instead of NULL to create a separator.
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