# Bookings to take precidence over availability

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
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``````

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.....