[Cialug] SQL Question

[Matthew Nuzum]

On Tue, Oct 6, 2009 at 3:13 PM, David Champion <dave at dchamp.net> wrote:
> 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 )
>>

> 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.

    select top (1) from
    ( select 'closed' as status, closed_date as date, 2 as rank from
table where ...
      union
      select 'lead' as status, cust_contact_date as date 1 as rank from table
    where ...
      order by rank asc, date desc )

-- 
Matthew Nuzum
newz2000 on freenode, skype, linkedin, identi.ca and twitter