How do I retrieve two different conditional sets of data from the same column?

Success Criteria:

  • retrieve Employees who started after 2006
  • for employees with a sale provide number of days from start date to sale date
  • for employees without a sale, provide number of days from start date to current date and emphasize no sale to date

NOTE there are multiple sales per employee in the actual data set thats why i've used the aggregate MIN

What I have so far in code:

    MIN(DATEDIFF(DAY,Dates.Startdate,Dates.Salesdate)) as 'Days to First Sale'    
FROM People
LEFT JOIN Dates ON People.ID = Dates.ID
    AND YEAR(CASE WHEN Dates.SalesDate > Dates.DeliveryDate THEN Dates.SalesDate ELSE 
    Sales.DeliveryDate END) > (2006)

Current Code Output:

Employee Days to First Sale
Brandi 33
Mark 18

Table #1 "People"

ID Employee
301 John
557 Brandi
221 Sheila
489 Mark

Table #2 "Dates"

ID StartDate SaleDate DeliveryDate
301 11/24/2005 3/1/2006 4/1/2006
557 12/3/2007 1/6/2008 2/1/2008
221 9/7/2006 NULL NULL
489 8/13/2006 9/1/2006 10/1/2006

Desired Outcome:

Employee Days to First Sale
Brandi 33
Sheila 5,475 Days with no sale
Mark 18

Read more here:

Content Attribution

This content was originally published by shade206 at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: