Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Selecting last sale of a product with two tables

Author  Topic 

unigee
Starting Member

4 Posts

Posted - 2014-07-16 : 04:36:11
Hi forum,

I wonder if you could help me. I have been struggling with this for months but have always found workarounds for what I need but it is coming evident I really need to get this solved.

Basically our database has two tables. A header and a sales table.

A typical example looks like this

header table

ORDER_NO, DATE_ENTERED
1001, 16/5/14
1002, 16/5/14
1003, 17/5/14
1004, 16/7/14

sales table

ORDER_NO, PRODUCT, QUANTITY
1001, Car, 100
1001, Bike, 50
1002, Van, 5
1003, Car, 50
1003, Plane, 2
1004, Car, 300


I have been trying to write a T-SQL query that will tell me for each distinct product, return the last date_entered and quantity.
For example, the T-SQL statement
SELECT sales.order_no, header.date_entered, sales.product, sales.quantity FROM
header, sales WHERE header.order_no = sales.order_no


gives the following result

ORDER_NO, DATE_ENTERED, PRODUCT, QUANTITY
1001, 16/5/14, Car, 100
1001, 16/5/14, Bike, 50
1002, 16/5/14, Van, 5
1003, 17/5/14, Car, 50
1003, 17/5/14, Plane, 2
1004, 16/7/14, Car, 300


However I only want the last date for each product. The result should be

ORDER_NO, DATE_ENTERED, PRODUCT, QUANTITY
1001, 16/5/14, Bike, 50
1002, 16/5/14, Van, 5
1003, 17/5/14, Plane, 2
1004, 16/7/14, Car, 300



For the life of me, I just can't work out how to get this result. My current workaround consists of listing everything and just ordering by date_entered DESC and using the first product I come across, but this is very slow, returns a massive dataset and causes the odd bug when a product is sold twice in one day (but this is not a priority at the moment as it happens rarely - it would be a bonus if the quantity could be summed up per day however).

Here is a SQL fiddle of the above tables (the Fiddle doesn't seem to like my dates but I hope you understand what I mean)
http://sqlfiddle.com/#!3/bbc43/3

Can someone please ease my pain?

Thanks


stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-16 : 05:07:36
[code]
SET DATEFORMAT dmy;
GO


IF OBJECT_ID('tempDB..#header') IS NOT NULL
DROP TABLE #header
IF OBJECT_ID('tempDB..#sales') IS NOT NULL
DROP TABLE #sales

CREATE TABLE #header
([order_no] int, [date_entered] varchar(10))
;

INSERT INTO #header
([order_no], [date_entered])
VALUES
(1001, '16/05/2014'),
(1002, '16/05/2014'),
(1003, '17/05/2014'),
(1004, '16/07/2014')
;

CREATE TABLE #sales
([order_no] int, [product] varchar(5), [quantity] int)
;

INSERT INTO #sales
([order_no], [product], [quantity])
VALUES
(1001, 'Car', 100),
(1001, 'Bike', 50),
(1002, 'Van', 5),
(1003, 'Car', 50),
(1003, 'Plane', 2),
(1004, 'Car', 300)
;


SELECT
order_no, date_entered, product, quantity
FROM
(
SELECT sales.order_no, header.date_entered, sales.product, sales.quantity
,Row_Number() OVER(Partition By sales.product Order by CONVERT(DATE,header.Date_entered) DESC) as RN
FROM #header AS header
INNER JOIN #sales AS sales
ON header.order_no = sales.order_no)A
WHERE
A.RN = 1
ORDER BY order_no
[/code]


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-16 : 05:07:54
output:

order_no date_entered product quantity
1001 16/05/2014 Bike 50
1002 16/05/2014 Van 5
1003 17/05/2014 Plane 2
1004 16/07/2014 Car 300



sabinWeb MCP
Go to Top of Page

unigee
Starting Member

4 Posts

Posted - 2014-07-16 : 05:32:58
oh my!

I just can't believe how someone could work that out for me so quickly.

Thank you so so much.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-16 : 06:32:29
Welcome!


sabinWeb MCP
Go to Top of Page
   

- Advertisement -