Performing an operation on the result of a query, without saving a table

Question

I'm very new to SQL and T-SQL, so please forgive any mistakes in terminology or if the answer is obvious - I don't even know where to start googling for the solution to this problem.

I only have SELECT permission on a T-SQL server. I have a query that pulls a bunch of information based on an event with a Start Date (DATETIME) and End Date (DATETIME). I want to have a calculated column in my query result that indicates whether the person was just involved in back-to-back events.

So, something like this, with "Back2Back" being the desired column:

PersonID    LastName    StartDate                EndDate                    Back2Back
006         Trevelyan   2019-12-01 09:30:00.000  2019-12-02 06:15:00.000    No
007         Bond        2019-12-01 12:15:00.000  2019-12-01 12:16:00.000    No
006         Trevelyan   2019-12-02 06:15:00.000  2019-12-02 15:15:00.000    Yes

The ORDER BY will be by StartDate, if that helps or hinders at all.

score 0 · Accepted Answer · answered Dec 23 '19 at 18:15

You can use the LAG function to get the end time of each person's previous event and compare that to the start time for the current event.

SELECT
  *
 ,CASE
    WHEN StartDate = LAG(EndDate) OVER (PARTITION BY PersonID ORDER BY StartDate)
      THEN 'Yes'
    ELSE 'No'
  END AS Back2Back
FROM
  @mytable
ORDER BY
  StartDate;

Results:

+----------+-----------+-------------------------+-------------------------+-----------+
| PersonID | LastName  |        StartDate        |         EndDate         | Back2Back |
+----------+-----------+-------------------------+-------------------------+-----------+
|        6 | Trevelyan | 2019-12-01 09:30:00.000 | 2019-12-02 06:15:00.000 | No        |
|        7 | Bond      | 2019-12-01 12:15:00.000 | 2019-12-01 12:16:00.000 | No        |
|        6 | Trevelyan | 2019-12-02 06:15:00.000 | 2019-12-02 15:15:00.000 | Yes       |
+----------+-----------+-------------------------+-------------------------+-----------+

This assumes that "back to back" is pretty literal. If you wanted to build some fluff time into that, you could use a DATEDIFF comparison instead of a straight equality.

Performing an operation on the result of a query, without saving a table

1 Answers1

Linked