Skip to main content

Notifications

Microsoft Dynamics RMS forum

last sold update

Posted on by Microsoft Employee

anyone remember the last sold update query to make it the value of last sold of itemdynamics table ?

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: last sold update

    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 ?

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: last sold update

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: last sold update

    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

  • Sad Profile Picture
    Sad 650 on at
    RE: last sold update

    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)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: last sold update

    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

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.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans