<div class="gmail_quote">On Tue, Oct 6, 2009 at 2:32 PM, Matthew Nuzum <span dir="ltr"><<a href="mailto:newz@bearfruit.org">newz@bearfruit.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="im">On Tue, Oct 6, 2009 at 2:23 PM, Todd Walton <<a href="mailto:tdwalton@gmail.com">tdwalton@gmail.com</a>> wrote:<br>
> SELECT TOP (1) Date<br>
> FROM SALES.SaleDetails<br>
> WHERE ([Action ID] = 'CUST_CONTACT')<br>
> ORDER BY Date<br>
><br>
> I have the above basic query. When a sales person processes a lead<br>
> they put the information into the sales tracker program. So, the<br>
> "lead" is opened. I want my sales people contact the potential<br>
> customer within a certain time period after opening the lead. When<br>
> they do that they log it in the program, which creates a<br>
> "CUST_CONTACT" line in the database. This query pulls back the most<br>
> recent CUST_CONTACT date.<br>
><br>
> The problem is, the sales person may contact the customer, make the<br>
> sale, and then just close the lead and not bother to put in a contact<br>
> entry. So, I want to modify the above to ask, "What is the most<br>
> recent CUST_CONTACT date, or CLOSED date if there is no CUST_CONTACT<br>
> entries?" I want to use the CUST_CONTACT date if it's available, but<br>
> use CLOSED if there is no CUST_CONTACT.<br>
><br>
<br>
</div>You can union two selects into one result set and then query it like a table:<br>
<br>
select top (1) from<br>
( select 'closed' as status, closed_date as date from table where ...<br>
union<br>
select 'lead' as status, cust_contact_date as date from table where ...<br>
order by date )<br>
<br>
Just get the most recent one.<br>
<br>
This syntax is not right, I'm not in SQL mode currently, but hopefully<br>
it's clear what's going on here.<br>
<font color="#888888"><br>
--<br>
Matthew Nuzum<br>
newz2000 on freenode, skype, linkedin, <a href="http://identi.ca" target="_blank">identi.ca</a> and twitter<br>
</font><div><div></div><br></div></blockquote><div><br>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.<br><br>Kendall<br><br>
<br><br></div></div>