The Code that Killed the Query

[mercwrought]

Hi all are you ready for some of the ugliest code that you have ever seen?

I need to re write this code but I can not figure out how to do it. Originally this code was just used so I could move on and finish the rest of the sp and now I optimizing it.

Thank you for any suggestions and help.

update s1
set f=  (Case when(SELECT COUNT(*)
	 FROM focompdaily 
	 WHERE ConfirmationNumber=s1.confirmation AND idnumber=s1.idnumber
				   AND ((transactiondate BETWEEN s1.arrivaldate AND s1.departuredate)
					OR (postingdate BETWEEN s1.arrivaldate AND s1.departuredate))
				   AND revenuecenter='RMS'  AND comptype IN ('RC','RF','FB','FC') )>0 then 1
				   else 0 end)
from #whatever s1

As you can see f in #whatever needs to be set to 1 if there are any records in focompdaily that have the same id number, and confirmation in #whatever and the transaction date or posting date fall between #whatever arrival date and departure date.

 

[tc3596]

try breaking up the code...

update s1
set f = 1
From #whatever s1, focompdaily s2
Where s2.ConfirmationNumber=s1.confirmation AND s2.idnumber=s1.idnumber AND s2.revenuecenter='RMS' AND s2.comptype IN ('RC','RF','FB','FC') AND
((s2.transactiondate BETWEEN s1.arrivaldate AND s1.departuredate) OR (s2.postingdate BETWEEN s1.arrivaldate AND s1.departuredate))

update s1
set f = 0
where f <> 1 or isnull(rtrim(f), '') = ''

 

[mercwrought]

that works very well thx for the info