[Cialug] SQL Question

[David Champion]

Kendall Bailey wrote:
> On Tue, Oct 6, 2009 at 2:32 PM, Matthew Nuzum <newz at bearfruit.org 
> <mailto:newz at bearfruit.org>> wrote:
>
>     On Tue, Oct 6, 2009 at 2:23 PM, Todd Walton <tdwalton at gmail.com
>     <mailto:tdwalton at gmail.com>> wrote:
>     > SELECT          TOP (1) Date
>     > FROM            SALES.SaleDetails
>     > WHERE           ([Action ID] = 'CUST_CONTACT')
>     > ORDER BY        Date
>     >
>     > I have the above basic query.  When a sales person processes a lead
>     > they put the information into the sales tracker program.  So, the
>     > "lead" is opened.  I want my sales people contact the potential
>     > customer within a certain time period after opening the lead.  When
>     > they do that they log it in the program, which creates a
>     > "CUST_CONTACT" line in the database.  This query pulls back the most
>     > recent CUST_CONTACT date.
>     >
>     > The problem is, the sales person may contact the customer, make the
>     > sale, and then just close the lead and not bother to put in a
>     contact
>     > entry.  So, I want to modify the above to ask, "What is the most
>     > recent CUST_CONTACT date, or CLOSED date if there is no CUST_CONTACT
>     > entries?"  I want to use the CUST_CONTACT date if it's
>     available, but
>     > use CLOSED if there is no CUST_CONTACT.
>     >
>
>     You can union two selects into one result set and then query it
>     like a table:
>
>     select top (1) from
>      ( select 'closed' as status, closed_date as date from table where ...
>       union
>       select 'lead' as status, cust_contact_date as date from table
>     where ...
>       order by date )
>
>     Just get the most recent one.
>
>     This syntax is not right, I'm not in SQL mode currently, but hopefully
>     it's clear what's going on here.
>
>     --
>     Matthew Nuzum
>     newz2000 on freenode, skype, linkedin, identi.ca
>     <http://identi.ca> and twitter
>
>
> Might need a "where not exists" subquery in the first part of the 
> union to eliminate cases where there's both a closed and contact 
> date.  Sounds like you want to ignore the closed date if a contact 
> date exists.
>
> Kendall
You could also address that issue with an ORDER BY, if you make it so 
the contact sorts before the closed record, and the LIMIT will make it 
only show the first record. That's going to execute a lot faster, and 
will be a lot easier work with.

-dc