item list based on max date in another table

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

Transact-SQL (2005)

item list based on max date in another table

Author

Topic

ianr
Starting Member

2 Posts

Posted - 2008-08-12 : 08:59:34

I have two tables - items and inventorychanges. The key is item_pk. I need to get a list of items where the current amount on hand (onhand) doesn't equal the latest quantity recorded in inventorychanges (newquantity). The inventorychanges table has a column (changedate) that records the date that inventory changed.

I have tried select item_fk, max(changedate), onhand from inventorychanges join items on item_pk = item_Fk where newquantity <> onhand group by item_fk, onhand order by item_fk

but it isn't checking against the most recent changedate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 16:56:49

See this blog post
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

ianr
Starting Member

2 Posts

Posted - 2008-08-12 : 23:41:57

Thanks for the post about how to ask a better question. I read through it and then read through my question again and am having a hard time finding the application value.

My question told what results I want to get, information about the table data I have, my attempted solution and the problem I am experiencing with the code. Perhaps you could point out the details that I overlooked in my question in order for you to provide an answer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 00:23:34

May be this:-

select *
from items i
join inventotychanges ic1
on i.item_pk = ic1.item_Fk
join (select item_Fk,max(changedate) as maxdate 
      from inventorychanges 
      group by item_Fk)ic2
on ic2.item_Fk=ic1.item_Fk
and ic2.maxdate=ic1.changedate
where i.onhand<>ic1.newquantity

if still doesnot give result post some sample data and explain what you want.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 04:57:36

quote:
Originally posted by ianr

Thanks for the post about how to ask a better question. I read through it and then read through my question again and am having a hard time finding the application value.

My question told what results I want to get, information about the table data I have, my attempted solution and the problem I am experiencing with the code. Perhaps you could point out the details that I overlooked in my question in order for you to provide an answer.

Hi Ianr, welcome to SQL Team,

Your question is very good compared to a lot that get that response! I see visakh16 has allready posted a possible solution but if that doesn't do it for you, the best thing to give us is a sample dataset for your tables and the expected result for that dataset. These were the only things of consequence missing from your question I think.

Regards,

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources