Selecting top n matches without matching the same rows twice

dsamuelHMD 0
Viewed 67

I am given two tables. Table 1 contains a list of appointment entries and Table 2 contains a list of date ranges, where each date range has an acceptable number of appointments it can be matched with.

I need to match an appointment from table 1 (starting with an appointment with the lowest date) to a date range in table 2. Once we've matched N appointments (where N = Allowed Appointments), we can no longer consider that date range.

Moreover, once we've matched an appointment from table 1 we can no longer consider that appointment for other matches.

Based on the matches I return table 3, with a bit column telling me if there was a match.

I am able to successfully perform this using a cursor, however this solution is not scaling well with larger datasets. I tried to match top n groups using row_count() however, this allows the same appointment to be matched multiple times which is not what I'm looking for.

Would anyone have suggestions in how to perform this matching using a set based approach?

Table 1

ApptID ApptDate
1 01-01-2022
2 01-04-2022
3 01-05-2022
4 01-20-2022
5 01-21-2022

Table 2

DateRangeId Date From Date To Allowed Num Appointments
1 01-01-2020 01-05-2020 2
2 01-06-2020 01-11-2020 1
3 01-12-2020 01-18-2020 2
4 01-20-2020 01-25-2020 1
5 01-20-2020 01-26-2020 1

Table 3 (Expected Output):

ApptID ApptDate Matched DateRangeId
1 01-01-2022 1 1
2 01-04-2022 1 1
3 01-05-2022 0 NULL
4 01-20-2022 1 4
5 01-21-2022 1 5
tsql sql-server sql
1 Answers

Here's a set-based, iterative solution. Depending on the size of your data it might benefit from indexing on the temp table. It works by filling in appointment slots in order of appointment id and range id. You should be able to adjust that if something more optimal is important.

declare @r int = 0;
create table #T3 (ApptID int, ApptDate date, DateRangeId int, UsedSlot int);
insert into #T3 (ApptID, ApptDate, DateRangeId, UsedSlot)
select ApptID, ApptDate, null, 0
from T1;
set @r = @@rowcount;

while @r > 0
begin
with ranges as (
    select r.DateRangeId, r.DateFrom, r.DateTo, s.ApptID, r.Allowed,
        coalesce(max(s.UsedSlot) over (partition by r.DateRangeId), 0) as UsedSlots
    from T2 r left outer join #T3 s on s.DateRangeId = r.DateRangeId
), appts as (
    select ApptID, ApptDate from #T3 where DateRangeId is null
), candidates as (
    select
        a.ApptID, r.DateRangeId, r.Allowed,
        UsedSlots + row_number() over (partition by r.DateRangeId
                                       order by a.ApptID) as CandidateSlot
    from appts a inner join ranges r
        on a.ApptDate between r.DateFrom and r.DateTo
    where r.UsedSlots < r.Allowed  
), culled as (
    select ApptID, DateRangeId, CandidateSlot,
        row_number() over (partition by ApptID order by DateRangeId)
            as CandidateSequence
    from candidates
    where CandidateSlot <= Allowed
)
update #T3
set DateRangeId = culled.DateRangeId,
    UsedSlot    = culled.CandidateSlot
from #T3 inner join culled on culled.ApptID = #T3.ApptID
where culled.CandidateSequence = 1;
set @r = @@rowcount;
end

select ApptID, ApptDate,
    case when DateRangeId is null then 0 else 1 end as Matched, DateRangeId
from #T3 order by ApptID;

https://dbfiddle.uk/-5nUzx6Q

It also has occurred to me that you don't really need to store the UsedSlot column. Since it's looking for the maximum in the ranges CTE you might as well just use count(*) over . But it might still have some benefit in making sense of what's going on.

shawnt000
Related