|
|
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
Navigation:
[Reply to this message]
|