Turn several queries into one in SQL Server-CodePudding

I have a table in SQL Server called schedule that has the following columns (and others not listed):

scheduleId	roomId	dateRegistered	dateFreed
4564	2	2022-12-25	2022-12-26
4565	3	2022-12-25	2022-12-27
4566	15	2022-12-26	2022-12-27
4567	2	2022-12-28	2022-12-31
4568	3	2022-12-28	2022-12-30

In some part of my app I need to show all the rooms occupied at a certain date.

Currently I run a query like this:

SELECT TOP (1) * 
FROM schedule 
WHERE roomId = [theNeededRoom] AND dateFreed < [providedDate] 
ORDER BY dateFreed DESC

The thing is that I have to run that query in a for loop so that I get the information for every room.

I'm sure there has to be a better way to do this in a single query that returns a row for each of the different roomIds possible, how can I go about this?

Also, when the room is first registered, the dateFreed column has a null value, if I wanted to take this into account, how can I make the query so that, in the case the dateFreed value is null, that is the row that gets chosen?

CodePudding user response：

You can use TOP(1) WITH TIES, while ordering on the last "dateFreed" date.

In order to have a "tied" value to match on, instead of ordering on "dateFreed DESC" we can use the ROW_NUMBER window function to generate a ranking on the same field (which will store 1 for each most recent "dateFreed" value, per "roomId").

SELECT TOP (1) WITH TIES * 
FROM schedule 
WHERE dateFreed < [providedDate] 
ORDER BY ROW_NUMBER() OVER(PARTITION BY roomId ORDER BY dateFreed DESC)

CodePudding user response：

SELECT
    t.*
FROM
    (
    SELECT
        roomId AS rId,
        max(dateFreed) AS dateFreedMax
    FROM
        schedule s
    GROUP BY
        s.roomId
) AS t
WHERE
    t.dateFreedMax < [providedDate] 
    OR t.dateFreedMax IS NULL

SELECT roomId
    FROM
        schedule s
    GROUP BY s.roomId, dateFreed 
HAVING
    max(dateFreed)<[providedDate]  OR dateFreed IS NULL