Return past 3 appointment and next 3 appointments

August 30, 2023

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

>Solution :

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