Posted by David on 04/26/06 15:01

Hi, I was hoping someone could confirm the following about the
following number 2 SQL query:

1. This extracts the data required given that there is a recording to
match the records

2. This should, but im not convinced extract all the remaining records
that do not have a matched recording, but fulfil the rest of the
criteria. If not please help...

3. As the two querys produce the same layout of tables if I did a UNION
ALL, this should work shouldnt it?

Query 1:
------------
SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile

FROM agent_input ai,
leads l,
recordings rec

WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
l.last_call_date between #04/24/2006 12:00 AM# and #04/25/2006
11:59 PM# AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign

ORDER BY ai.agent_login, ai.entry_date

Query 2.
-------------
SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
'' as AgreementRecordingFile
FROM agent_input ai,
leads l,
recordings rec

WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
l.last_call_date between #04/24/2006 12:00 AM# and #04/25/2006
11:59 PM# AND
count
(
SELECT rec.*
WHERE rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign
) < 1

ORDER BY ai.agent_login, ai.entry_date

Thanks in advance for any help, its greatly appreciated.

David

[Back to original message]