I'll cut to the chase, I have a CTE query that I just about understand but not well enough to modify it. Would some one be good enough to have a look over it and point me in the right direction... The query is:
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 cross apply tallyno10 as b cross apply tallyno10 as c) 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 ORDER BY A_TID, A_AvailDate, A_Num
On the test database its result is:
|A_TID|A_AvailDate|A_Num| |11003|2020-01-24|5| |11003|2020-01-27|3| |11003|2020-01-28|3| |11003|2020-01-28|5| |11003|2020-01-29|3| |11003|2020-01-30|3| |11003|2020-01-31|3|
If you look at lines 3 & 4 the dates (A_AvailDate) are the same but the A_Num is different. A '5' needs to replace a '3' if present for the same date but i'm really not sure how to go about this... Any ideas??