Announcements
No record found.
anyone remember the last sold update query to make it the value of last sold of itemdynamics table ?
*This post is locked for comments
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
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)
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
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.
getting this on running your update.
Warning: Null value is eliminated by an aggregate or other SET operation.
also
should be
SET LASTSOLD = V.LASTSOLD ,lastupdated=getdate()
right ?
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our 2026 Super Stars!
Thanks to all of our 2025 Community Spotlight stars!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
