Probably a relatively simple JOIN...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Probably a relatively simple JOIN...

Author

Topic

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-05 : 12:08:41

Hello,

What I have currently is two views - one containing a list of customers and a second containing a list of calls that have been completed (ie the customer actually answered the phone)

I need to generate from these a list of customers to call.

I would prefer to do this with a JOIN rather than a nested query due to performance.

I WAS using the following...

SELECT listToCall.*
FROM listToCall ltc LEFT JOIN completedCalls c ON ltc.orderNo = c.orderNo
WHERE c.orderNo IS NULL

This worked great except for the case that I also need to account for people that may have been called on some day other than today. To try to accommodate that problem, I tried the following...

SELECT listToCall.*
FROM listToCall ltc LEFT JOIN completedCalls c ON ltc.orderNo = c.orderNo
WHERE (c.orderNo IS NULL OR NOT c.callDate BETWEEN @startDate AND @endDate)

This did not work because if a customer had several calls, but one was NOT in the date range and one WAS in the date range, it would select the first one it encountered. That would then cause the customer to continue to receive calls on the given day no matter how many times they confirmed (and customers don't care for that).
One customer can have multiple calls for the same order on multiple days (in the case of rescheduling the order). How can I construct a join to essentially say select if it's not there OR if the date is not correct? This is keeping in mind that there may be 10 different dates that they have received and completed calls.

I am currently doing it as follows, but would like to eliminate the nested query if at all possible...


SELECT *
FROM listToCall
WHERE orderNo NOT IN (
    SELECT orderNo FROM completedCalls WHERE callDate BETWEEN 
    @startDate AND @endDate)

This gives me the results I want, but I would like to do this with a join. If there is a way to do this to keep from having to use a nested query, pointing me in the direction would be greatly appreciated. In fact, any help at all will be greatly appreciated !

I would imagine constructing the join to select only the max date from the completed calls table per order number may be the way to go, but I have no idea how to go about that...

Thanks !

--Edit: I had made a mistake in the posting of my working query.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-05 : 12:32:40

Without test data and expected results it is difficult to tell, but maybe you just want:


SELECT L.*
FROM listToCall L
	LEFT JOIN completedCalls C
		ON L.orderNo = C.orderNo
			AND C.callDate BETWEEN @startDate AND @endDate
WHERE C.orderNo IS NULL

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-05 : 12:54:02

I'll try to put together a sample that is similar to what I want... the actual views are pretty large and contain a lot of data, but a lot of it is completely irrelevant to the logic...

Let's say the listToCall view has the following fields:
orderNo, firstName, lastName, primaryPhone, secondPhone, delDate

completedCalls view has:
orderNo, delDate, callTime, callDate, callResult, callLength

If listToCall shows
1, John, Doe, 5551123456, 5551234567, 07/06/2010
2, Jane, Doe, 5551234645, 5552342019, 07/06/2010
3, Jim, James, 5543453453, 1239143029, 07/06/2010

and completedCalls shows
1, 05/06/2010, 12:34 PM, 05/05/2010, Live Answer, 50
1, 06/07/2010, 12:35 PM, 06/06/2010, Live Answer, 92
2, 06/05/2010, 12:30 PM, 06/04/2010, Confirmed, 85
2, 07/06/2010, 1:35 PM, 07/05/2010, Live Answer, 103

expected result here would be:
1, John, Doe, 5551123456, 5551234567, 07/06/2010
3, Jim, James, 5543453453, 1239143029, 07/06/2010

If I have those two views with that information being returned, I want this query to return the records for John Doe and Jim James. John has been called and answered before but not about delivery on this day. Jane has answered her about the delivery on this day, so she does not need another call.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-05 : 20:05:36

Just an FYI to anyone that may be having the same problem I was having... or at least something similar... after searching for a while, a friend suggested a solution that worked.

Since the completedCalls view was only one used to eliminate calls to NOT make, I changed it slightly to make it show only the most recent delivery date.

completedCalls then became...


SELECT orderNo, max(delDate) AS delDate
FROM Calls
GROUP BY orderNo

The final query would then be...


SELECT l.*
FROM listToCall l LEFT JOIN completedCalls c ON l.orderNo = c.orderNo
WHERE l.delDate BETWEEN @startDate AND @endDate AND (
     c.orderNo IS NULL OR NOT c.delDate BETWEEN @startDate AND 
     @endDate)

These changes have fixed the problem to help generate the list I needed.

Thanks to everyone who read and to everyone who tried to help :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources