Max get the last date in an update query

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

General SQL Server Forums

New to SQL Server Programming

Max get the last date in an update query

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-09-08 : 10:04:28

how would i get the following code to max (take the last inventory ship date, and last inventory received date)

UPDATE [Shipments]
SET [Shipments].[invoice] = [stock].[invoice]
[Shipments].[inventory] = [stock inbound].[inventory ship date],
[Shipments].[outbound] = [stock inbound].[inventory received date],
FROM [Shipments]
INNER JOIN [stock]
ON [Shipments].[Load] =[stock].[load]

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-09-08 : 10:43:16

What sort of output are you expecting and which version of SQL Server are you using?

===
http://www.ElementalSQL.com/

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-09-08 : 10:45:49

I'm using SQL Server 2005
and i'm just looking to get the last inventory ship date and last inventory received date (so if two dates were entered into the data base 9/1/2010 and 9/8/2010 I only want to get 9/8/2010

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-08 : 10:52:01

Not tested


	UPDATE Sh
	   SET  -- Sh.[invoice]   = St.[invoice] 
		   Sh.[inventory] = MAX(St.[inventory ship date])
		 , Sh.[outbound]  = MAX(St.[inventory received date]) 
	  FROM [Shipments]	Sh
INNER JOIN [stock]		St
		ON [Shipments].[Load] =[stock].[load]
  GROUP BY Sh.invoice

I'm assuming inoice is they key...if it is....you don't need to do the update

And when you say "Last" do you mean max? or the last one added.

If that is the case, the do you have a add_tys column with a default of datetime for when the row was added?

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

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-08 : 10:53:43

wait...what's the table [stock inbound]?

That needs to be part of the join as well

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

Add yourself!
http://www.frappr.com/sqlteam

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-09-08 : 11:20:56

thanks that worked :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources