[Cialug] SQL Question

[Kendall Bailey]

On Tue, Oct 6, 2009 at 2:32 PM, Matthew Nuzum <newz at bearfruit.org> wrote:

> 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
>
>
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/cialug/attachments/20091006/e4726cff/attachment.html