[Cialug] SQL Question

[Matthew Nuzum]

On Tue, Oct 6, 2009 at 2:23 PM, Todd Walton <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 and twitter