Query Help - Left Outer Join, last date added

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

SQL Server Development (2000)

Query Help - Left Outer Join, last date added

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-03-11 : 15:36:34

I would like to display only 1 record and the last CS_MeetMRP.AdjustedMRPDate date that was added. I tried a MAX(CS_MeetMRP.AdjustedMRPDate) and then used GROUP BY, and was almost able to achieve the results I wanted. It selected the MAX Date and not the LAST date added.

SELECT CS_OnOrderReport.PN, CS_OnOrderReport.PO, CS_OnOrderReport.BUYER, CS_OnOrderReport.SUPPNO, CS_OnOrderReport.QTY,
CS_OnOrderReport.SUPPLIER, CS_OnOrderReport.MRPNEED, CS_OnOrderReport.ORGINAL, CS_OnOrderReport.LATEST, CS_OnOrderReport.STATUS,
CS_MeetMRP.AdjustedMRPDate
FROM CS_OnOrderReport LEFT OUTER JOIN
CS_MeetMRP ON CS_OnOrderReport.PN = CS_MeetMRP.PN COLLATE Latin1_General_BIN AND
CS_OnOrderReport.PO = CS_MeetMRP.PO COLLATE Latin1_General_BIN
WHERE (CS_OnOrderReport.SUPPNO = '0946')
ORDER BY CS_OnOrderReport.LATEST

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-11 : 16:45:23

hmm - if last date <> max date, then you need to know some kind of sequencing - I guess you don't have a created date column, or a sequential identity column?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-03-11 : 18:06:55

Yes, I do have a column CS_MeetMRP.MeetMRPID
So the highest number would be displayed first.

When I say "one record" I was meaning no duplicates...make sense?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-11 : 18:25:45

try

select top 1 ...
from   ...
order by AdjustedMRPDate DESC

----------------------------------
'KH'

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-03-11 : 19:15:16

That will only return 1 record all together. What I was trying to explain was 1 record per join. I hope that makes sense. My fault for not explaining well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-11 : 22:18:05

can you post some sample data & expected result ?

----------------------------------
'KH'

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-13 : 10:45:50

quote:
Originally posted by jrockfl

Yes, I do have a column CS_MeetMRP.MeetMRPID
So the highest number would be displayed first.

When I say "one record" I was meaning no duplicates...make sense?

hmm - ok, does this give you a list of the most recent rows, per order?


select
	CS_OnOrderReport.SUPPNO,
	max(CS_MeetMRP.MeetMRPID)
from
	CS_OnOrderReport
	LEFT OUTER JOIN
	CS_MeetMRP 
		ON CS_OnOrderReport.PN = CS_MeetMRP.PN COLLATE Latin1_General_BIN 
		AND CS_OnOrderReport.PO = CS_MeetMRP.PO COLLATE Latin1_General_BIN

Does this give you a list that seems to have the right (most recent) in it? It's a bit difficult trying to guess the meaning of your column names - is SuppNo the "order number" that you want the most recent date for?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-03-13 : 11:48:37

Wanderer

Suppno stands for the supplier no. The supplier will be unique and have multiple PN (part number) and PO (purchase order)

The AdjustedMRPDate is a date the Supplier will provide. I am trying to display the last date that was entered.

Your query returns the last id that was entered for the Supplier.
I need the last date entered for each PN PO combination.

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-03-13 : 12:01:24

Maybe this will help..
http://rockenbach.net/1.jpg
On the very right it shows 6/6, but it should be 6/3
http://rockenbach.net/2.jpg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources