SQL Server Performance Forum – Threads Archive
Case?
Im in some trouble!<br /><br />I have a CRM-Like system witch decides witch customers to call again when a certin amount of time has passed. Im storing all answers from those customers called and the date the call was made. <br /><br />Then i decide what customers to call by taking the last call made checking what they replyed (Yes, No, Not Intressted etc). Each reply has a value that represent how long we must wait until we call again stored in a table.<br /><br />That makes three tables to join together. The problem is that some of the customers has never been called. So they should be allowed calls but they dont get included in my search <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /> <br /><br />The Queriy looks like this:<br /><br />SELECT dbo.Customer.*<br />FROM dbo.ReturnCalls RIGHT OUTER JOIN<br /> dbo.Reply ON dbo.ReturnCalls.Answer = dbo.Reply.Reply AND dbo.ReturnCalls.Answer = dbo.Reply.Reply <br />RIGHT OUTER JOIN<br /> dbo.Customer ON dbo.Reply.CustomerID = dbo.Customer.CustomerID<br />WHERE (dbo.Reply.ReplyDate =<br /> (SELECT MAX(replydate)<br /> FROM reply<br /> WHERE Reply.customerid = customer.customerid)) AND <br />Customer.VisitAdressCode BETWEEN 70100 AND 70200 AND<br />(DATEADD(day, dbo.ReturnCalls.Days, dbo.Reply.ReplyDate) < GETDATE()) <br /><br />Reply: Contains what the customer has answerd every time we called and what date that was.<br />Customer: Adress-stuff on the customer<br />ReturnCalls: Rules for how many days we must wait before we call again. If the customer says no we must wait for 45 days.<br /><br />I want to include those customers who has never been called. As it is now the query sorts them out. That is those customers who has no rows in the Reply table gets sorted out. How can i avoid this?<br /><br />Perhaps i could use CASE statments? <br /><br />Any ideas?<br /><br />Thanks
Well u can try using full outer join and check for the null values which will be
your no replies customers i guess.
]]>