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
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))
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)
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')
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,129 Most Valuable Professional
nmaenpaa 101,154