Skip to main content

Notifications

Microsoft Dynamics RMS forum

How can we change the price of items in a Category based on last sold date.

Posted on by 650

HI ,

We Need to set a SalaPrice for Items in a Category Which did not Sold Since X Period.

Do any body have a tested SQL query can do it .

Thanks

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: How can we change the price of items in a Category based on last sold date.

    Well,

    first of all, i assume that u need this on HQ then downloaded to stores, so u should keep ',lastupdated=getdate()' to be able to create a worksheet

    secondly, the query u wrote looks ok, but i changed as follows coz i'm not sure of the date format u used (i used to use'yyyy-mm-dd', and i added the lastupdated field, and i changed the where clause to be better readable

    Update Item

    set SalePrice = 90 ,SaleStartDate = '2011-01-01' ,SaleEndDate = '2011-01-31' ,SaleType = 1

    ,lastupdated=getdate()

    Where CATEGORYID =555 AND QUANTITY > 0

    and (LASTSOLD < '01-01-2011' or (lastreceived < '01-01-2011' AND LastSold is null))

  • Sad Profile Picture
    Sad 650 on at
    Re: How can we change the price of items in a Category based on last sold date.

    Hi, Thank Ahmed

    Actualy we need to change the Item`s SalePrice in a Category based on LastSold date.

    I suggested this SQL statment but i am not sure if it`s work, can you please send me your ideas.

    Update Item

    set SalePrice = XXX ,SaleStartDate = '01-01-2011' ,SaleEndDate = '01-06-2011' ,SaleType = 1

    Where (CATEGORYID =xxxx AND LASTSOLD < '01-01-2011' AND QUANTITY > 0) or ( CATEGORYID =xxx AND lastreceived < '01-01-2011' AND LastSold is null AND QUANTITY > 0)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: How can we change the price of items in a Category based on last sold date.

    This is to run in HQ and will affect creating "update inventory" worksheet by date: -

    u need to change the saleprice, category code, and the date

    update item

    set saleprice=xx

    ,lastupdated=getdate()

    where categoryid in (select id from category where code='xxxx')

    and item.id not in

    (select itemid from [transaction] inner join transactionentry on [transaction].transactionnumber=transactionentry.transactionnumber

    and [transaction].storeid=transactionentry.storeid where time >= '2011-01-01')

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,129 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans