Select within grouped query to return last record of that group

I have a table which stores events along with event type and booking ID’s, my goal is to group the BookingID and return the first EventDate, the last EventDate and the last BC_EventType

| ID | BookingID | VenueID | BC_EventType |        EventDate        |
+----+-----------+---------+--------------+-------------------------+
| 12 |   1468656 |      94 |            1 | 2020-10-20 12:06:27.027 |
| 13 |   1468656 |      94 |            3 | 2020-10-20 12:06:27.060 |
| 14 |   1468656 |      94 |            4 | 2020-10-20 12:06:43.923 |
| 15 |   1468656 |      94 |            5 | 2020-10-20 12:06:49.603 |
| 16 |   1468656 |      94 |            6 | 2020-10-20 12:06:56.523 |
| 17 |   1468656 |      94 |            8 | 2020-10-20 12:07:09.203 |
| 18 |   1468656 |      94 |           12 | 2020-10-20 12:07:21.287 |
| 19 |   1468656 |      94 |           13 | 2020-10-20 12:07:26.167 |
| 20 |   1468656 |      94 |           17 | 2020-10-20 12:07:36.337 |
| 21 |   1468657 |      94 |            7 | 2020-10-20 13:54:48.697 |
| 22 |   1468657 |      94 |            1 | 2020-10-20 13:53:56.297 |
| 23 |   1468657 |      94 |            3 | 2020-10-20 13:53:56.330 |
| 24 |   1468657 |      94 |            4 | 2020-10-20 13:54:38.257 |
| 25 |   1468657 |      94 |            5 | 2020-10-20 13:54:40.333 |
| 26 |   1468657 |      94 |            6 | 2020-10-20 13:54:40.540 |
| 27 |   1468657 |      94 |            8 | 2020-10-20 13:54:51.193 |
| 28 |   1468657 |      94 |           12 | 2020-10-20 13:55:13.650 |
| 29 |   1468657 |      94 |           13 | 2020-10-20 13:55:13.727 |
| 30 |   1468657 |      94 |           14 | 2020-10-20 13:55:26.933 |
| 31 |   1468665 |      94 |            8 | 2020-10-20 15:00:41.043 |
| 32 |   1468665 |      94 |            9 | 2020-10-20 15:00:41.073 |
| 33 |   1468665 |      94 |            8 | 2020-10-20 15:00:41.090 |
| 34 |   1468665 |      94 |            9 | 2020-10-20 15:00:41.120 |
| 35 |   1468665 |      94 |            7 | 2020-10-20 15:00:41.137 |
| 36 |   1468665 |      94 |            1 | 2020-10-20 15:00:20.687 |
| 37 |   1468665 |      94 |            3 | 2020-10-20 15:00:20.703 |
| 38 |   1468665 |      94 |            4 | 2020-10-20 15:00:28.560 |
| 39 |   1468665 |      94 |            5 | 2020-10-20 15:00:32.617 |
| 40 |   1468665 |      94 |            6 | 2020-10-20 15:00:32.663 |
| 41 |   1468665 |      94 |           12 | 2020-10-20 15:00:48.680 |
| 42 |   1468665 |      94 |           15 | 2020-10-20 15:00:48.743 |
| 43 |   1468665 |      94 |           14 | 2020-10-20 15:00:56.247 |
| 44 |   1468665 |      94 |           17 | 2020-10-20 15:00:56.527 |
| 45 |   1468676 |      94 |            8 | 2020-10-20 15:35:14.870 |
| 46 |   1468676 |      94 |            9 | 2020-10-20 15:35:14.887 |
| 47 |   1468676 |      94 |            8 | 2020-10-20 15:35:14.917 |
| 48 |   1468676 |      94 |            9 | 2020-10-20 15:35:14.933 |
| 49 |   1468676 |      94 |            7 | 2020-10-20 15:35:14.947 |
| 50 |   1468676 |      94 |            1 | 2020-10-20 15:35:13.927 |
| 51 |   1468687 |      94 |           23 | 2020-10-20 16:11:38.820 |
| 52 |   1468687 |      94 |            8 | 2020-10-20 16:11:39.837 |
| 53 |   1468687 |      94 |            9 | 2020-10-20 16:11:39.853 |
| 54 |   1468687 |      94 |            8 | 2020-10-20 16:11:39.870 |
| 55 |   1468687 |      94 |            9 | 2020-10-20 16:11:39.883 |
| 56 |   1468687 |      94 |            7 | 2020-10-20 16:11:39.900 |
| 57 |   1468687 |      94 |            1 | 2020-10-20 16:11:39.493 |
| 58 |   1468687 |      94 |            8 | 2020-10-20 16:12:47.077 |
| 59 |   1468687 |      94 |            9 | 2020-10-20 16:12:47.093 |
| 60 |   1468687 |      94 |            8 | 2020-10-20 16:12:47.110 |
| 61 |   1468687 |      94 |            9 | 2020-10-20 16:12:47.123 |
| 62 |   1468687 |      94 |            7 | 2020-10-20 16:12:47.150 |
| 63 |   1468687 |      94 |            1 | 2020-10-20 16:12:36.270 |
+----+-----------+---------+--------------+-------------------------+

At the moment I have this SQL:

SELECT
[BookingID] 
,min(EventDate) as Min_Date
,max(EventDate) as Max_Date
FROM [dbo].[BC_Event]
Group By [BookingID]

Which returns something along the lines of:

| BookingID |        Min_Date         |        Max_Date         |
+-----------+-------------------------+-------------------------+
|   1468656 | 2020-10-20 12:06:27.027 | 2020-10-20 12:07:36.337 |
|   1468657 | 2020-10-20 13:53:56.297 | 2020-10-20 13:55:26.933 |
|   1468665 | 2020-10-20 15:00:20.687 | 2020-10-20 15:00:56.527 |
|   1468676 | 2020-10-20 15:35:13.927 | 2020-10-20 15:35:14.947 |
|   1468687 | 2020-10-20 16:11:38.820 | 2020-10-20 16:12:47.150 |
|   1468688 | 2020-10-20 16:13:53.390 | 2020-10-20 16:19:02.777 |
+-----------+-------------------------+-------------------------+

This is great, however I need a column which displays the LAST BC_EventType ID, so in theory it would be where [BC_Event] = Max_Date. How would I do this?

Answer

One method is conditional aggregation:

SELECT [BookingID], min(EventDate) as Min_Date, max(EventDate) as Max_Date,
       MAX(CASE WHEN seqnum_asc = 1 THEN BC_EventType END) as first_BC_EventType,
       MAX(CASE WHEN seqnum_desc = 1 THEN BC_EventType END) as last_BC_EventType
FROM (SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY BookingID ORDER BY EventDate ASC) as seqnum_asc,
             ROW_NUMBER() OVER (PARTITION BY BookingID ORDER BY EventDate DESC) as seqnum_desc
      FROM [dbo].[BC_Event] e
     ) e
GROUP BY [BookingID]

Advertisement

Answer