SQL Server Select currently active and immediately subsequent booking

x33g5p2x 于 2023-06-04 发布在其他

关注(0)|答案(2)|浏览(110)

I have a table that contains bookings for borrowing assets:

--------
Bookings
--------

BookingID      AssetNo   Start                      End                       Status
---------      -------   -----------------------    -----------------------   ------
1              1         2023-05-31 00:00:00.000    2023-05-31 01:00:00.000   Overdue
2              1         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   Booked
3              2         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   InUse
4              2         2023-05-31 02:00:00.000    2023-05-31 03:00:00.000   Booked
5              2         2023-05-31 03:00:00.000    2023-05-31 04:00:00.000   Booked

For each asset, I want to select the booking that the asset is currently being borrowed against, at the current time, and the next booking that exists for the asset after that, in time order (if present).

For example, using the data above, when the current time is 2023-05-31 01:30:00.000 :

AssetNo 1 is currently being borrowed against BookingID 1 , as the asset wasn't returned in time, and is Overdue against the booking (which is in the past).
AssetNo 2 is currently being borrowed against BookingID 3 , as the asset is InUse against the booking (which is at the present time)

I want a query that will select the following from the example data set, when current time = 2023-05-31 01:30:00.000 :

BookingID      AssetNo   Start                      End                       Status   Active
---------      -------   -----------------------    -----------------------   ------   ------
1              1         2023-05-31 00:00:00.000    2023-05-31 01:00:00.000   Overdue  True
2              1         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   Booked   False
3              2         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   InUse    True
4              2         2023-05-31 02:00:00.000    2023-05-31 03:00:00.000   Booked   False

I can select the currently active bookings like this:

DECLARE @UTCNow DATETIME;
SET @UTCNow = '2023-05-31 01:30:00.000';

SELECT * FROM (
    SELECT
        b.*
        ,RANK() OVER (PARTITION BY b.AssetNo ORDER BY [Start] ASC) AS time_order
    FROM Bookings b
    WHERE (b.[End] < @UTCNow AND b.Status = 'Overdue') -- past booking that started but hasn't completed
    OR (b.[Start] <= @UTCNow AND b.[End] > @UTCNow) -- booking that occurs at the present time
    ) AS b2
WHERE b2.time_order = 1

How can I also select the booking that follows the currently active booking, and for each asset, set which one is the active booking ( Active = True ), and which is a subsequent booking for the same Asset ( Active = False )? I can use transact SQL but would prefer to use ANSI.

Update

I've created the following SQL Fiddle that sets up the example data set. I've begun outlining a different possible approach i.e. selecting the start time of the currently active booking for each asset, and selecting the first 2 bookings for each asset from that time, ordered by StartASC :

http://sqlfiddle.com/#!18/f51f5/7

sql-server

来源：https://stackoverflow.com/questions/76369610/select-currently-active-and-immediately-subsequent-booking

2条答案

按热度按时间

xoefb8l81#

I solved it like this:

DECLARE @UTCNow DATETIME;
SET @UTCNow = '2023-05-31 01:30:00.000';

WITH activeBookings AS (
  SELECT b.[AssetNo], MIN(b.[Start]) AS activeBookingStart
  FROM Bookings b
            WHERE (b.[End] <= @UTCNow AND b.[Status] = 'Overdue') -- past booking that started but hasn't completed
            OR (b.[Start] <= @UTCNow AND b.[End] > @UTCNow) -- booking that occurs at the present time
            GROUP BY b.[AssetNo]
)
SELECT 
  b2.[BookingID]
  , b2.[AssetNo]
  , b2.[Start]
  , b2.[End]
  , b2.[Status]
  , CASE
      WHEN b2.[time_order] = 1 THEN 'True'
      WHEN b2.[time_order] = 2 THEN 'False'
    END AS 'Active'
FROM (
  SELECT
    b.*
    ,RANK() OVER (PARTITION BY b.[AssetNo] ORDER BY [Start] ASC) AS time_order
    FROM Bookings b
    INNER JOIN activeBookings ab
    ON ab.[AssetNo] = b.[AssetNo]
    WHERE b.[Start] >= ab.[activeBookingStart]
) AS b2
WHERE b2.[time_order] IN (1,2);

http://sqlfiddle.com/#!18/f51f5/32

赞(0）回复(0）举报 2023-06-04

mlnl4t2r2#

Not completely clear on your expectation of the final output. Perhaps you'd like columns BookingID, AssetNo, Start, End, Status, Active, Start_Next, (and End_Next), and BookingID_Next in each output row? And I assume booking IDs are unique.

Some questions I have:

Why was RANK() used for the task of selecting the currently active bookings? The way I see it, just your WHERE clauses suffice for your purpose.
Have you considered using LEAD(), which looks at records that appear after the current record (offset is omitted here because it is optional, and the default is 1.)

Following (based on aforementioned assumptions) is my suggestion:

DECLARE @UTCNow DATETIME;
    SET @UTCNow = '2023-05-31 01:30:00.000';

    SELECT 
    bookingid, assetno, [Start], [End], status, active, 
    LEAD ([Start]) OVER (PARTITION BY assetno ORDER BY [Start] ASC) AS start_next,
    LEAD (bookingid) OVER (PARTITION BY assetno ORDER BY [Start ASC]) AS bookingid_next
    FROM Bookings b
    WHERE (b.[End] < @UTCNow AND b.Status = 'Overdue')
    OR (b.[Start] <= @UTCNow AND b.[End] > @UTCNow)

You can add additional data points in SELECT, and ORDER BY at the end to further specify how you'd like the output to be arranged.

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Select currently active and immediately subsequent booking

Update

2条答案

相关问题

热门标签

最新问答