Return past 3 appointment and next 3 appointments

Question

I need to get the past 3 appointments and the next 3 appointments from a certain date. Each of these queries give the expected result. My problem is that I need the results together. I can't use UNION because I need to use ORDER BY in both queries. How can I accomplish this?

--This is for the future appointments
SELECT TOP 3 
    e.Events, e.EventDate
FROM
    events e
JOIN 
    EventMatters em ON em.Events = e.Events
WHERE 
    Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
    AND EventKind = 'D' 
    AND CONVERT(DATE, e.eventdate) > '2022/05/03'
ORDER BY 
    e.EventDate

--This is for the past appointments
SELECT TOP 3 
    e.Events, e.EventDate
FROM
    events e
JOIN 
    EventMatters em ON em.Events = e.Events
WHERE 
    Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
    AND e.EventKind = 'D' 
    AND CONVERT(DATE, e.eventdate) < '2022/05/03'
ORDER BY 
    EventDate DESC

Answer 1

You could make two CTE and UNION ALL THEM

--This is for the future appointments
WITH CTE_AFTER AS(SELECT TOP 3 
    e.Events, e.EventDate
FROM
    events e
JOIN 
    EventMatters em ON em.Events = e.Events
WHERE 
    Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
    AND EventKind = 'D' 
    AND CONVERT(DATE, e.eventdate) > '2022/05/03'
ORDER BY 
    e.EventDate),  
CTE_BEFORE AS(

SELECT TOP 3 
    e.Events, e.EventDate
FROM
    events e
JOIN 
    EventMatters em ON em.Events = e.Events
WHERE 
    Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
    AND e.EventKind = 'D' 
    AND CONVERT(DATE, e.eventdate) < '2022/05/03'
ORDER BY 
    EventDate DESC)
    SELECT Events, EventDate FROM CTE_AFTER
    UNION ALL 
    SELECT Events, EventDate FROM CTE_BEFORE

Answer 2

Two nested queries.

1. Join the two base table, and apply a LAG(eventuate,3) and a LEAD(eventuate,3) to get a date three rows ahead and three rows back.
2. Join the table obtained under 1 with the events base table.

I would, however, just to understand the resulting data and to avoid duplicates, keep the columns from the row obtained with the WHERE condition, and the columns of the three preceding and three following rows as additional columns in the report.

I just filter out one row, with the predicate WHERE eventdate= '2022-05-12', to get just one exemplary cutout. You can take it from here.

WITH
events(events,eventdate,eventkind) AS (
            SELECT 'A',CAST('2022-05-06' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-07' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-08' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-09' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-10' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-11' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-12' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-13' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-14' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-15' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-16' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-17' AS DATE),'D'
  UNION ALL SELECT 'A',CAST('2022-05-18' AS DATE),'D'
)
,
eventmatters(events,matters) AS (
  SELECT 'A','000122FD-47B6-47B6-47B6-1FCAB474CE53' 
)
-- real query starts here, replace following comma with "WITH"
,
w_borders AS (
  SELECT
    e.events
  , e.eventdate
  , e.eventkind
  , LAG (e.eventdate,3) OVER(ORDER BY e.eventdate) AS earliest
  , LEAD(e.eventdate,3) OVER(ORDER BY e.eventdate) AS latest
  FROM events e
  JOIN eventmatters em ON em.events=e.events
  WHERE em.matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
    AND e.eventkind = 'D' 
)
SELECT
  e.events     AS this_events
, e.eventdate  AS this_eventdate
, e.eventkind  AS this_eventkind
, o.events     AS other_events
, o.eventdate  AS other_eventdate
, o.eventkind  AS other_eventkind
FROM w_borders e
JOIN events    o 
  ON e.events = o.events
 AND e.earliest <= o.eventdate
 AND e.latest   >= o.eventdate
WHERE e.eventdate = CAST('2022-05-12' AS DATE)
;

this_events	this_eventdate	this_eventkind	other_events	other_eventdate	other_eventkind
A	2022-05-12	D	A	2022-05-09	D
A	2022-05-12	D	A	2022-05-10	D
A	2022-05-12	D	A	2022-05-11	D
A	2022-05-12	D	A	2022-05-12	D
A	2022-05-12	D	A	2022-05-13	D
A	2022-05-12	D	A	2022-05-14	D
A	2022-05-12	D	A	2022-05-15	D

fiddle