Get Row by MAX(sales_date) ???... Stumper...
-
I have a table that is keyed by emp_id, each employee my have up to 100 sales records in this table, I need to pull the entire row for the most recent sales date in 2006, the problem is that date could fall anywhere between 01/01/06 and 12/31/06 based on thier last sale. I've tried using MAX(sales_date), but it returns the data from all the rows. I just don't need the sales date, I need all the info for this person based on the MAX(sales_date).
Here's a sample of my table:
emp_id | company | sales_date | s_gross | PO_Num | S_gross_YTD
1831 ABC 01/10/06 100.00 456 100.00
1831 ABC 02/10/06 100.00 121 200.00
1831 ABC 04/10/06 100.00 122 300.00
1831 ABC 05/10/06 100.00 333 400.00
1831 ABC 06/10/06 100.00 433 500.00
1900 ABC 03/10/06 100.00 111 100.00
2200 ABC 10/11/06 100.00 444 100.00
2200 ABC 11/17/06 100.00 567 200.00
2200 ABC 12/26/06 100.00 009 300.00
I want to create view that would pull just 3 records from this table based on the greatest sales date. As illustrated below.
1831 ABC 06/10/06 100.00 433 500.00
1900 ABC 03/10/06 100.00 111 100.00
2200 ABC 12/26/06 100.00 009 300.00
Thanks in advance,
jim
-
join to itself as a subquery or join
select * from sales s1
join (
select emp_id, max(sales_date) as sales_date
from sales s2
group by emp_id
) s2 on s1.emp_id = s2.emp_id and s1.sales_date = s2.sales_date
-
However, if there are rows for the same emp_id and sales_date, you will still get multiple rows for a emp_id. For instance, if emp_id 1831 had two orders on 6/10/06:
emp_id | company | sales_date | s_gross | PO_Num | S_gross_YTD
1831 ABC 06/10/06 100.00 333 400.00
1831 ABC 06/10/06 200.00 433 600.00
In your case here, you may be able to get way with it by also using MAX(S_gross_YTD) (as I assume that will accumulated for each order?).
Otherwise, the only way I know of would be to build a temp table with primary keys that match the MAX(sales_date), then eliminate the unwanted rows using a TOP parameter.
Hope this helps
Mark -
In your case here, you may be able to get way with it by also using MAX(S_gross_YTD) (as I assume that will accumulated for each order?).Excellent point, Mark!
-
Thanks Everyone!!! I've got it working..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply