most recent order date - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

most recent order date

Author

Topic

dporter
Starting Member

9 Posts

Posted - 2005-09-06 : 13:23:01

I have two tables each with an ITEM column that is used to join them.
The second table has a PARCEL# and a SHIPMENT_DATE column. I need to be able to return the parcel# and most recent shipment_date for each item. Each parcel# can have multiple shipment_dates.

Any help you can provide would be greatly appreciated.

Thanks,
Dennis

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-06 : 13:40:46

[code]SELECT Parcel#, MAX(Shipment_Date) As Shipment_Date
FROM MyTable
WHERE conditions go here
GROUP BY Parcel#[/code]

dporter
Starting Member

9 Posts

Posted - 2005-09-07 : 15:08:49

quote:

Originally posted by SamC

SELECT Parcel#, MAX(Shipment_Date) As Shipment_Date
  FROM MyTable
  WHERE conditions go here
  GROUP BY Parcel#

How is this getting information from two tables based on an item number?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-07 : 15:12:15

dporter, please show us an example of the data in both tables and the expected result set.

Tara

dporter
Starting Member

9 Posts

Posted - 2005-09-07 : 16:14:54

quote:
Originally posted by tduggan

dporter, please show us an example of the data in both tables and the expected result set.

Tara

Table 1
ITEM ItemName Col3
1 x x
2 x x

Table 2
ITEM Parcel# ShipDate Col4 Col5
1 100 4/12/05 x x
1 100 6/2/05 x x
1 101 3/22/05 x x
1 101 6/9/05 x x
2 150 1/1/05 x x
2 150 6/1/05 x x

Result would be
Parcel# Most recent shipdate
100 6/2/05
101 6/9/05
150 6/1/05

I am trying to find the last ship date for the parcel number.
For clarification:
I feel that I have to access both tables for the following reason. I am pulling a subset of values from the first table based on the item number. Based on the item numbers I am retrieving, I am then looking to join or subquery the second table, get the parcel # and then find the most recent ship date. Therefore, I can see the item number, item name, etc from the first table, as well as the last ship date, etc from the second table in my end result.
Does that make sense?

Thank you for taking the time to look into this for me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-07 : 17:21:41

Based on your expected result set, you only need what Sam has posted as your expected result set in the previous post doesn't include anything from the other table. But I think this is what you want:



SET NOCOUNT ON

CREATE TABLE Table1 (ITEM int NOT NULL, ItemName varchar(50) NOT NULL)
CREATE TABLE Table2 (ITEM int NOT NULL, Parcel# int NOT NULL, ShipDate datetime NOT NULL)

INSERT INTO Table1 VALUES(1, 'Item1')
INSERT INTO Table1 VALUES(2, 'Item2')

INSERT INTO Table2 VALUES(1, 100, '04/12/2005')
INSERT INTO Table2 VALUES(1, 100, '06/02/2005')
INSERT INTO Table2 VALUES(1, 101, '03/22/2005')
INSERT INTO Table2 VALUES(1, 101, '06/09/2005')
INSERT INTO Table2 VALUES(1, 150, '01/01/2005')
INSERT INTO Table2 VALUES(1, 150, '06/01/2005')
INSERT INTO Table2 VALUES(2, 250, '06/01/2005')
INSERT INTO Table2 VALUES(2, 250, '06/07/2005')
INSERT INTO Table2 VALUES(2, 232, '04/07/2005')

SELECT t1.ITEM, t1.ItemName, t2.Parcel#, t2.ShipDate
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ITEM = t2.ITEM
INNER JOIN
(
	SELECT Parcel#, MAX(ShipDate) AS ShipDate
	FROM Table2
	GROUP BY Parcel#
) d
ON t2.Parcel# = d.Parcel# AND t2.ShipDate = d.ShipDate

DROP TABLE Table1, Table2

Tara

dporter
Starting Member

9 Posts

Posted - 2005-09-08 : 12:05:08

quote:

Originally posted by tduggan

Based on your expected result set, you only need what Sam has posted as your expected result set in the previous post doesn't include anything from the other table. But I think this is what you want:



SET NOCOUNT ON

CREATE TABLE Table1 (ITEM int NOT NULL, ItemName varchar(50) NOT NULL)
CREATE TABLE Table2 (ITEM int NOT NULL, Parcel# int NOT NULL, ShipDate datetime NOT NULL)

INSERT INTO Table1 VALUES(1, 'Item1')
INSERT INTO Table1 VALUES(2, 'Item2')

INSERT INTO Table2 VALUES(1, 100, '04/12/2005')
INSERT INTO Table2 VALUES(1, 100, '06/02/2005')
INSERT INTO Table2 VALUES(1, 101, '03/22/2005')
INSERT INTO Table2 VALUES(1, 101, '06/09/2005')
INSERT INTO Table2 VALUES(1, 150, '01/01/2005')
INSERT INTO Table2 VALUES(1, 150, '06/01/2005')
INSERT INTO Table2 VALUES(2, 250, '06/01/2005')
INSERT INTO Table2 VALUES(2, 250, '06/07/2005')
INSERT INTO Table2 VALUES(2, 232, '04/07/2005')

SELECT t1.ITEM, t1.ItemName, t2.Parcel#, t2.ShipDate
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ITEM = t2.ITEM
INNER JOIN
(
	SELECT Parcel#, MAX(ShipDate) AS ShipDate
	FROM Table2
	GROUP BY Parcel#
) d
ON t2.Parcel# = d.Parcel# AND t2.ShipDate = d.ShipDate

DROP TABLE Table1, Table2

Tara

Thank you Tara! I believe that gives me just what I need. You were right in your assumption that I did need the items from the first table. Not sure how I left that out of my desired result set that I posted.
If I'm not pressing my luck too much, may I ask one more thing?
When the results are returned, there are some ties in shipment date (for example, 2 records with parcel# 100 and 6/1/05 as the shipment date). The logical tiebreaker to be able to choose just one of these is an AGE column in table 1. So what I would be going for is when a parcel # has a tie, I would only like to return the record whose age is the oldest. Age is an integer value representing the number of days something has been in inventory (ex: 87, 119). I would like to return the appropriate info. from table 1 and table 2 for the record whose age is 119 (see above ex:).

Can this be done?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-08 : 13:18:20

Could you post sample data which illustrates your problem? Please post it in the format that I have so that we can copy this into our own environments for testing. Also, please post the expected result set using that sample data.

It's hard to understand what is needed without seeing the data in Query Analyzer.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-08 : 13:20:43

Follow the hint link below for an expiditious answer.

Ya gotta meet us half way....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dporter
Starting Member

9 Posts

Posted - 2005-09-12 : 12:42:13

The decision maker decided to go a different way with this query.
Tara, thank you very much for your prompt reponse and help.
Brett, thank you for the reminder of how to post. I will be sure to do that next time.

quote:
Originally posted by X002548

Follow the hint link below for an expiditious answer.

Ya gotta meet us half way....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources