select ReferenceNumber, DiscountCustomerType  into #temp  from reservations r (nolock)
where GuestNumber = 0 and ReferenceNumber > 0 and ISNULL(DiscountCustomerType, 0) > 0 and
Exists ( select * from reservations r1 (nolock) where r1.ReferenceNumber = r.ReferenceNumber and r1.GuestNumber > 0)
and ISNULL(DiscountCustomerType, 0) not in 
(select ISNULL(DiscountCustomerType, 0) from reservations r2 (nolock) where r2.GuestNumber > 0 and r2.ReferenceNumber = r.ReferenceNumber)
order by ReferenceNumber

update reservations
Set DiscountCustomerType = #temp.DiscountCustomerType
FROM #TEMP 
INNER join Reservations r on r.ReferenceNumber = #temp.ReferenceNumber and r.GuestNumber > 0

DROP TABLE #TEMP


SELECT referencenumber, addresstype, RoomDiscountRate into #temp2 from RESERVATIONADDRESSXTAB rax  (nolock)
inner join RESERVATIONADDRESS ra (nolock) on rax.ADDRESSNUMBER = ra.AddressNumber 
where rax.REFERENCENUMBER > 0 and
 rax.GUESTNUMBER = 0 and 
 ISNULL(ra.RoomDiscountRate, 0) > 0 and
 Exists ( select * from RESERVATIONADDRESSXTAB rax1 (nolock) where rax1.ReferenceNumber = rax.ReferenceNumber and rax1.GuestNumber > 0) and
 ra.RoomDiscountRate not in
(select RoomDiscountRate from RESERVATIONADDRESSXTAB rax2 (nolock)
inner join RESERVATIONADDRESS ra2 (nolock) on rax2.ADDRESSNUMBER = ra2.AddressNumber 
and rax2.REFERENCENUMBER = rax.REFERENCENUMBER 
and ra2.ADDRESSTYPE = ra.ADDRESSTYPE
and rax2.GUESTNUMBER > 0)

update reservationaddress
set RoomDiscountRate = #temp2.RoomDiscountRate
from #temp2 
inner join reservationAddressXTAB rax on #temp2.REFERENCENUMBER = RAX.REFERENCENUMBER AND RAX.GUESTNUMBER > 0
INNER JOIN ReservationAddress ra on rax.ADDRESSNUMBER = ra.AddressNumber and ra.ADDRESSTYPE = #temp2.ADDRESSTYPE

DROP TABLE #TEMP2

SELECT referencenumber, addresstype, MeetingDiscountRate into #temp3 from RESERVATIONADDRESSXTAB rax  (nolock)
inner join RESERVATIONADDRESS ra (nolock) on rax.ADDRESSNUMBER = ra.AddressNumber 
where rax.REFERENCENUMBER > 0 and
 rax.GUESTNUMBER = 0 and 
 ISNULL(ra.MeetingDiscountRate, 0) > 0 and
 Exists ( select * from RESERVATIONADDRESSXTAB rax1 (nolock) where rax1.ReferenceNumber = rax.ReferenceNumber and rax1.GuestNumber > 0) and
 ra.MeetingDiscountRate not in
(select MeetingDiscountRate from RESERVATIONADDRESSXTAB rax2 (nolock)
inner join RESERVATIONADDRESS ra2 (nolock) on rax2.ADDRESSNUMBER = ra2.AddressNumber 
and rax2.REFERENCENUMBER = rax.REFERENCENUMBER 
and ra2.ADDRESSTYPE = ra.ADDRESSTYPE
and rax2.GUESTNUMBER > 0)

update reservationaddress
set RoomDiscountRate = #temp3.MeetingDiscountRate
from #temp3 
inner join reservationAddressXTAB rax on #temp3.REFERENCENUMBER = RAX.REFERENCENUMBER AND RAX.GUESTNUMBER > 0
INNER JOIN ReservationAddress ra on rax.ADDRESSNUMBER = ra.AddressNumber and ra.ADDRESSTYPE = #temp3.ADDRESSTYPE

DROP TABLE #TEMP3

SELECT referencenumber, addresstype, HallDiscountRate into #temp4 from RESERVATIONADDRESSXTAB rax  (nolock)
inner join RESERVATIONADDRESS ra (nolock) on rax.ADDRESSNUMBER = ra.AddressNumber 
where rax.REFERENCENUMBER > 0 and
 rax.GUESTNUMBER = 0 and 
 ISNULL(ra.HallDiscountRate, 0) > 0 and
 Exists ( select * from RESERVATIONADDRESSXTAB rax1 (nolock) where rax1.ReferenceNumber = rax.ReferenceNumber and rax1.GuestNumber > 0) and
 ra.HallDiscountRate not in
(select HallDiscountRate from RESERVATIONADDRESSXTAB rax2 (nolock)
inner join RESERVATIONADDRESS ra2 (nolock) on rax2.ADDRESSNUMBER = ra2.AddressNumber 
and rax2.REFERENCENUMBER = rax.REFERENCENUMBER 
and ra2.ADDRESSTYPE = ra.ADDRESSTYPE
and rax2.GUESTNUMBER > 0)

update reservationaddress
set RoomDiscountRate = #temp4.HallDiscountRate
from #temp4 
inner join reservationAddressXTAB rax on #temp4.REFERENCENUMBER = RAX.REFERENCENUMBER AND RAX.GUESTNUMBER > 0
INNER JOIN ReservationAddress ra on rax.ADDRESSNUMBER = ra.AddressNumber and ra.ADDRESSTYPE = #temp4.ADDRESSTYPE

DROP TABLE #TEMP4

SELECT referencenumber, addresstype, DiscountRate into #temp5 from RESERVATIONADDRESSXTAB rax  (nolock)
inner join RESERVATIONADDRESS ra (nolock) on rax.ADDRESSNUMBER = ra.AddressNumber 
where rax.REFERENCENUMBER > 0 and
 rax.GUESTNUMBER = 0 and 
 ISNULL(ra.DiscountRate, 0) > 0 and
 Exists ( select * from RESERVATIONADDRESSXTAB rax1 (nolock) where rax1.ReferenceNumber = rax.ReferenceNumber and rax1.GuestNumber > 0) and
 ra.DiscountRate not in
(select DiscountRate from RESERVATIONADDRESSXTAB rax2 (nolock)
inner join RESERVATIONADDRESS ra2 (nolock) on rax2.ADDRESSNUMBER = ra2.AddressNumber 
and rax2.REFERENCENUMBER = rax.REFERENCENUMBER 
and ra2.ADDRESSTYPE = ra.ADDRESSTYPE
and rax2.GUESTNUMBER > 0)

update reservationaddress
set RoomDiscountRate = #temp5.DiscountRate
from #temp5 
inner join reservationAddressXTAB rax on #temp5.REFERENCENUMBER = RAX.REFERENCENUMBER AND RAX.GUESTNUMBER > 0
INNER JOIN ReservationAddress ra on rax.ADDRESSNUMBER = ra.AddressNumber and ra.ADDRESSTYPE = #temp5.ADDRESSTYPE

DROP TABLE #TEMP5

SELECT referencenumber, addresstype, PHONEDISCOUNTRATE into #temp6 from RESERVATIONADDRESSXTAB rax  (nolock)
inner join RESERVATIONADDRESS ra (nolock) on rax.ADDRESSNUMBER = ra.AddressNumber 
where rax.REFERENCENUMBER > 0 and
 rax.GUESTNUMBER = 0 and 
 ISNULL(ra.PHONEDiscountRate, 0) > 0 and
 Exists ( select * from RESERVATIONADDRESSXTAB rax1 (nolock) where rax1.ReferenceNumber = rax.ReferenceNumber and rax1.GuestNumber > 0) and
 ra.PHONEDiscountRate not in
(select PHONEDiscountRate from RESERVATIONADDRESSXTAB rax2 (nolock)
inner join RESERVATIONADDRESS ra2 (nolock) on rax2.ADDRESSNUMBER = ra2.AddressNumber 
and rax2.REFERENCENUMBER = rax.REFERENCENUMBER 
and ra2.ADDRESSTYPE = ra.ADDRESSTYPE
and rax2.GUESTNUMBER > 0)

update reservationaddress
set RoomDiscountRate = #temp6.PHONEDiscountRate
from #temp6 
inner join reservationAddressXTAB rax on #temp6.REFERENCENUMBER = RAX.REFERENCENUMBER AND RAX.GUESTNUMBER > 0
INNER JOIN ReservationAddress ra on rax.ADDRESSNUMBER = ra.AddressNumber and ra.ADDRESSTYPE = #temp6.ADDRESSTYPE

DROP TABLE #TEMP6