Problems creating View
-
DB: SQL Server 2000 I am having trouble writing a SQL statement to pull the most current data about a record out of a table and would appreciate if someone could shed some light on the situation. Example tables below:
**tbl_Item ItemID** Color Type CreatedDate CreatedBy **tbl_ItemValue ValueID** ItemID Value UpdatedDate UpdatedByEverytime the value changes for an item, as new record is created in the tbl_ItemValue table. I need to create a query that joins the two tables and shows the most current value for each item in the tbl_Item table. Most current value would be the record with the most current UpdatedDate that has the same ItemID in tbl_ItemValue. Therefore, my view should look something like this:**vw_Item_Value_Current ItemID** Color Type CreatedDate CreatedBy Value UpdatedDate UpdatedByAny help would greatly be appreciated. -
DB: SQL Server 2000 I am having trouble writing a SQL statement to pull the most current data about a record out of a table and would appreciate if someone could shed some light on the situation. Example tables below:
**tbl_Item ItemID** Color Type CreatedDate CreatedBy **tbl_ItemValue ValueID** ItemID Value UpdatedDate UpdatedByEverytime the value changes for an item, as new record is created in the tbl_ItemValue table. I need to create a query that joins the two tables and shows the most current value for each item in the tbl_Item table. Most current value would be the record with the most current UpdatedDate that has the same ItemID in tbl_ItemValue. Therefore, my view should look something like this:**vw_Item_Value_Current ItemID** Color Type CreatedDate CreatedBy Value UpdatedDate UpdatedByAny help would greatly be appreciated.Here's one way:
select * from tbl_Item itm
inner join tbl_ItemValue val on itm.itemid = val.itemid
inner join (Select max(updateddate) as MaxDate, itemid
from tbl_itemValue
group by itemid) as val2 on val.UpdatedDate = val2.MaxDate and val.ItemID = val2.ItemID--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Here's one way:
select * from tbl_Item itm
inner join tbl_ItemValue val on itm.itemid = val.itemid
inner join (Select max(updateddate) as MaxDate, itemid
from tbl_itemValue
group by itemid) as val2 on val.UpdatedDate = val2.MaxDate and val.ItemID = val2.ItemID--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters