How to query for latest and upcoming course dates in SQL?

Courses
1

Hey everyone, I’m working on a database with two tables: ‘course’ and ‘event’. I need to create a query that displays all courses along with their most recent past event and the next upcoming event dates. I’m currently getting the next date for a specific course with this query:

SELECT TOP 1
    startdate as nextdate    
FROM event
WHERE 
    CONVERT(VARCHAR(8), startdate, 112) > CONVERT(VARCHAR(8), GETDATE(), 112)
    AND coursecode = 'EXM101'
ORDER BY startdate ASC

However, I need to modify it so that each course appears only once and includes both the latest and upcoming dates. The output should resemble the following format:

coursecode | coursetitle | lastevent  | lasteventid | nextevent  | nexteventid
-----------------------------------------------------------------------
EXM101     | Example     | 2023-05-15 | 1234        | 2023-09-01 | 5678
TST202     | Test        | 2023-06-20 | 2345        | NULL       | NULL

It’s acceptable to have NULL values if a course does not have a past or future event date. Any suggestions on how to craft this query?

2

Hey there, LeoNinja22! :wave: That’s a pretty interesting challenge you’ve got there. I’m curious about a few things:

Have you considered using a combination of LEFT JOINs with subqueries? That might help you get both the latest and upcoming dates in one go.

What version of SQL are you using? Some newer versions have neat functions like LEAD() and LAG() that could make this easier.

Also, how large is your dataset? Depending on the size, we might need to think about performance optimization.

Oh, and here’s a thought - what if a course has multiple events on the same day? How would you want to handle that?

I’d love to hear more about your specific setup. Maybe we could brainstorm some creative solutions together! What have you tried so far besides the query you shared?

3

hey leo, tryn window funcs. try FIRST_VALUE() over partition to grab last & next events for each course. watch for duplicate day events tho. if your sql supports it, it can work great. hope it helps, cheers

4

I’ve encountered a similar situation in my work. Here’s an approach that might help:

You can use correlated subqueries to fetch the latest past event and the next upcoming event for each course. Something like this:

SELECT c.coursecode, c.coursetitle,
(SELECT TOP 1 e1.startdate FROM event e1 WHERE e1.coursecode = c.coursecode AND e1.startdate <= GETDATE() ORDER BY e1.startdate DESC) AS lastevent,
(SELECT TOP 1 e1.eventid FROM event e1 WHERE e1.coursecode = c.coursecode AND e1.startdate <= GETDATE() ORDER BY e1.startdate DESC) AS lasteventid,
(SELECT TOP 1 e2.startdate FROM event e2 WHERE e2.coursecode = c.coursecode AND e2.startdate > GETDATE() ORDER BY e2.startdate ASC) AS nextevent,
(SELECT TOP 1 e2.eventid FROM event e2 WHERE e2.coursecode = c.coursecode AND e2.startdate > GETDATE() ORDER BY e2.startdate ASC) AS nexteventid
FROM course c

This query should give you the desired output format. It’s not the most efficient for large datasets, but it’s straightforward and works well for moderate-sized tables. Let me know if you need any clarification or have performance concerns.