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

Table 2

Table 3 (Expected Output):

CodePudding user response：

try below Query

declare @Table1 as table
(
ApptID int, ApptDate date
)
insert into @Table1(ApptID,ApptDate)
values
(1,  '01-01-2022'),
(2, '01-04-2022'),
(3, '01-05-2022'),
(4, '01-20-2022'),
(5, '01-21-2022')

declare @Table2 as table
(
DateRangeId int, DateFrom date,DateTo date ,
AllowedNumAppointments int
)
insert into @Table2(DateRangeId,DateFrom,DateTo,AllowedNumAppointments)
values
(1, '01-01-2022','01-05-2022',2),
(2, '01-06-2022','01-11-2022',1),
(3, '01-12-2022','01-18-2022',2),
(4, '01-20-2022','01-25-2022',1),
(5, '01-20-2022','01-26-2022',1)

             


             select a1.ApptID,a1.ApptDate,
             Case When a1.ApptDate between a2.DateFrom and a2.DateTo
              then 1 else 0 end Matched,CASE WHEN a1.ApptDate between a2.DateFrom and
              a2.DateTo then 
              a2.DateRangeId else null end DateRangeId
             from @Table1 a1 join @Table2 a2 on a1.ApptID=a2.DateRangeId

Query Output

|ApptID |     ApptDate    |Matched      |DateRangeId|
|:----  |:----:           |:----:       |:----:     |
|1      |     2022-01-01  |1            |1          |
|2      |     2022-01-04  |0            |NULL       |
|3      |     2022-01-05  |0            |NULL       |
|4      |     2022-01-20  |1            |4          |
|5      |     2022-01-21  |1            |5          |

CodePudding user response：

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