Selecting last sale of a product with two tables

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

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

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.

stepson
Aged Yak Warrior

545 Posts

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

Welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources