|
|
Posted by Erland Sommarskog on 04/26/06 00:30
David (david.goodyear@gmail.com) writes:
> We are in the process of buying a new server to run mssql. However
> before this as a tempory fix to using a msaccess backend i believe
> through odbc i need to address the following issue:
>
> 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
> 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 AND
> last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
> ORDER BY ai.agent_login, ai.entry_date
>
> I want to make the recordings entry optional so the same results come
> out whether it matches a recording or not. If it does i want it to
> populate the AgreementRecordingFile column above, if not just put a ''
> as you would with '' as AgreementRecordFile.
If I understand this correcly you need an outer join:
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,
coalesec(rec.file_name, '') as AgreementRecordingFile
FROM agent_input ai
JOIN leads l ON ai.whole_phone_number = l.whole_phone_number
LEFT JOIN recordings rec ON
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
WHERE l.call_status = 1110 AND
last_call_date >= '20060424' AND
last_call_date < '20060426'
ORDER BY ai.agent_login, ai.entry_date
Two disclaimers:
1) I don't know from which table last_call_date comes from. I've assumed
that it comes from agent_input or leads. If it comes from recordings,
the above query is not likely to be correct.
2) The syntax works on SQL Server, because that is all I know. If you want
syntax that works on Access, ask comp.databases.ms-access.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|