anyone remember the last sold update query to make it the value of last sold of itemdynamics table ?
*This post is locked for comments
getting this on running your update.
Warning: Null value is eliminated by an aggregate or other SET operation.
also
UPDATE ITEM
SET LASTSOLD = V.LASTSOLD
FROM ITEM LEFT JOIN VVLASTSOLD V ON ITEM.ID = V.ITEMID
should be
UPDATE ITEM
SET LASTSOLD = V.LASTSOLD ,lastupdated=getdate()
FROM ITEM LEFT JOIN VVLASTSOLD V ON ITEM.ID = V.ITEMID
right ?
I think I see a problem using the "where itemdynamic.lastsold in (select max(itemdynamic.lastsold) from itemdynamic group by itemid)" method to filter, as a time may appear in this selection list but not actually be the latest time an item was sold.
For example, if Item A and Item B were last sold at Store 1 together on a transaction rung up on 01/01/2013, but Item A was last sold at Store 2 on 02/01/2013, the itemdynamic.lastsold value for Item A at store 1 is going to appear in the "Select max(itemdynamic.lastsold) from itemdynamic group by itemid" results due to being the latest date Item B was sold, even though it is not the latest date Item A was sold.
Fernando's method won't run into this problem, though if you're not comfortable creating views you could accomplish the same thing with a nested select statement.
yes I did a variant, in php...
$sql="SELECT ItemDynamic.LastSold,ItemID,item.Description,item.ItemLookupCode,item.Inactive,item.Quantity FROM [hqq].[dbo].[ItemDynamic] left join [hqq].[dbo].item on ItemDynamic.ItemID = item.id where itemdynamic.lastsold in (select max(itemdynamic.lastsold) from hqq.dbo.itemdynamic group by itemid) and item.Quantity=0 and (CONVERT(varchar(10), itemdynamic.lastsold , 111)) like '2012%' and item.Inactive=0 order by hqq.dbo.itemdynamic.LastSold";
for each year I want to disactivated
that gives me a list of ILC that I set to inactive with
update item set inactive=1 , lastupdated=getdate() where itemlookupcode='{$row['ItemLookupCode']}
looped these and I get around 4k skus out of the way... 6k still on
Try this Query:
update item
set item.lastsold = itemdynamic.lastsold
from itemdynamic
left join item on itemdynamic.itemid = item.id
where itemdynamic.lastsold in (select max(itemdynamic.lastsold)
from itemdynamic group by itemid)
Hello FCASES - remember that what SQL giveth, SQL taketh away; data manipulation queries really should be a last resort - you can always join the itemdynamic table on a report if you must see this information; but if you must update the item table because your users JUST HAVE TO see that date in the GUI....
I would create a view to group by itemid first, then update the item table based on that view
CREATE VIEW VVLASTSOLD
AS
SELECT ITEMID, MAX(LASTSOLD) LASTSOLD
FROM ITEMDYNAMIC
GROUP BY ITEMID
Then you can schedule a job to update this field in the item table; run it when stores are not connecting with HQ
UPDATE ITEM
SET LASTSOLD = V.LASTSOLD
FROM ITEM LEFT JOIN VVLASTSOLD V ON ITEM.ID = V.ITEMID
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,133 Most Valuable Professional
nmaenpaa 101,154