CODEWITHSUNDEEP
sql
user3642531
user3642531

Reputation: 319

Only joining rows where the date is less than the max date in another field

Let's say I have two tables. One table containing employee information and the days that employee was given a promotion:

Emp_ID     Promo_Date
1          07/01/2012
1          07/01/2013
2          07/19/2012
2          07/19/2013
3          08/21/2012
3          08/21/2013

And another table with every day employees closed a sale:

Emp_ID     Sale_Date
1          06/12/2013
1          06/30/2013
1          07/15/2013
2          06/15/2013
2          06/17/2013
2          08/01/2013
3          07/31/2013
3          09/01/2013

I want to join the two tables so that I only include sales dates that are less than the maximum promotion date. So the result would look something like this

Emp_ID     Sale_Date     Promo_Date
1          06/12/2013    07/01/2012
1          06/30/2013    07/01/2012
1          06/12/2013    07/01/2013
1          06/30/2013    07/01/2013

And so on for the rest of the Emp_IDs. I tried doing this using a left join, something to the effect of

left join SalesTable on PromoTable.EmpID = SalesTable.EmpID and Sale_Date 
< max(Promo_Date) over (partition by Emp_ID)

But apparently I can't use aggregates in joins, and I already know that I can't use them in the where statement either. I don't know how else to proceed with this.

Upvotes: 4

Views: 8768

Answers (2)

FutbolFan
FutbolFan

Reputation: 13713

Gordon's answer is right on! Alternatively, you could also do a inner join to a subquery to achieve your desired output like this:

SELECT s.emp_id
      ,s.sales_date
      ,t.promo_date
FROM sales s
INNER JOIN (
    SELECT emp_id
          ,max(promo_date) AS promo_date
    FROM promotions
    GROUP BY emp_id
    ) t ON s.emp_id = t.emp_id
    AND s.sales_date < t.promo_date;

SQL Fiddle Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The maximum promotion date is:

select emp_id, max(promo_date)
from promotions
group by emp_id;

There are various ways to get the sales before that date, but here is one way:

select s.*
from sales s
where s.sales_date < (select max(promo_date)
                      from promotions p
                      where p.emp_id = s.emp_id
                     );

Upvotes: 7

Related Questions