Bookings to take precidence over availability

Transact-SQL
1

Afternoon all,

A while back a very kind member of this forum wrote a query that i understand the gist of, but not well enough to (sucessfully) modify it... I've been pouring over this for a few days and haven't got any further. I'm wondering if anyone would be good enought to cast an eye over it. Basically bookings should take presidence over availability...

For instance if 'Table Availability' contains
A_TID 1000
A_AvailDate 20/07/2021
A_Num 4

and

'Table Bookings' contains
A_TID 1000
A_AvailDate 20/07/2021
A_Num 3 (hardcoded line 9, bookings always have an A_Num of 3)

then it would return the 'Bookings' record.

WITH tallyno10 (n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS tallyno10(n)) 
,tallyno1000 (n) AS 
(SELECT row_number() OVER (ORDER BY (SELECT NULL)) - 1 FROM tallyno10 AS a, tallyno10 AS b, tallyno10 AS c), 
					CTE AS ( 
							  SELECT A_TID, A_AvailDate, A_Num 
							  FROM availability 
							  WHERE a_availdate >= cast(GETDATE() AS DATE) 
							  UNION ALL 
							  SELECT b.b_tid,dateadd(day, a.n, b.b_startdate),3 
							  FROM tallyno1000 AS a 
							  CROSS APPLY Bookings AS b 
							  WHERE dateadd(day, a.n, b.b_startdate) >= cast(GETDATE() AS DATE) 
							  AND dateadd(day, a.n, b.b_startdate) <= b.b_enddate AND B_TID > 0
							  ), 
					CTE_2 AS( 
							 SELECT A_TID, A_AvailDate, A_Num 
							 , ROW_NUMBER() OVER(PARTITION BY A_TID, A_AvailDate ORDER BY A_Num DESC) AS RowNum 
							 FROM CTE
							 ) 
					SELECT A_TID, A_AvailDate, A_Num
					FROM CTE_2
					WHERE RowNum = 1 
					ORDER BY A_TID, A_AvailDate, A_Num
2

I think typing it all out helped me think about it in a slightly different way... I believe I can just reverse the ordering ie

SELECT A_TID, A_AvailDate, A_Num 
, ROW_NUMBER() OVER(PARTITION BY A_TID, A_AvailDate ORDER BY A_Num DESC) AS RowNum 
FROM CTE

Would become:

SELECT A_TID, A_AvailDate, A_Num 
, ROW_NUMBER() OVER(PARTITION BY A_TID, A_AvailDate ORDER BY A_Num) AS RowNum 
FROM CTE

Hmmmm, i'll do some testing tomorrow.....