web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Wanted SQL help to get...
Microsoft Dynamics RMS (Archived)

Wanted SQL help to get a report for all my old stock

(0) ShareShare
ReportReport
Posted on by Community Member

Looking at getting all my old stock on sale. Need some SQL help

Wanted all items not received this year and not sold this year.

select itemlookupcode, description, quantity from item where
(lastreceived  not between  '01/01/2011' and  '12/31/2011') and
(lastsold not between  '01/01/2011' and  '12/31/2011')

This partially works but does not capture some of the items that have a blank lastsold or lastreceived date!

Any ideas how I can fix this query.

Thanks

Danny

*This post is locked for comments

I have the same question (0)
  • Sad Profile Picture
    Sad 650 on at

    Hi Danny, try to use similar to this :

    Where  (LASTSOLD < '04-04-2011' or (lastreceived < '04-04-2011' AND LastSold is null))

    good luck

  • Nashat Profile Picture
    Nashat 45 on at

    select itemlookupcode, description, quantity

    from item where

    ((lastreceived  not between  '01/01/2011' and  '12/31/2011') or lastreceived is null)

    and ((lastsold not between  '01/01/2011' and  '12/31/2011') or lastsold is null)

  • Nashat Profile Picture
    Nashat 45 on at

    @Sad

    Your query wouldn't include the items with null LastReceived, seems you assumed that there no chance to sell an items that u haven't received which is not the case all the way thru, as u might sell a non-received items by mistake

  • Sad Profile Picture
    Sad 650 on at

    Hi ,Nashat

    Yes it is ,note that if item has null lastReceived then has no QTY ,with one exeption ( received by a  Physical Count ),

    on other hand your query has a problem that it will not show items with lastreceived is null and   lastsold (is) between  '01/01/2011' and  '12/31/2011'

    Best regards

  • Nashat Profile Picture
    Nashat 45 on at

    I think this is what Danny needs, he wants to know the items never sold AND never received in the intended period

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    Are you in a headquarters environment? If you are, it may be a good idea to copy the latest last sold and received dates from itemdynamic to item before running any updates based on them, just to ensure everything is up to date.

    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)

    and

    update item set item.lastreceived = itemdynamic.lastreceived

    from itemdynamic left join item on itemdynamic.itemid = item.id

    where itemdynamic.lastreceived in (select max(itemdynamic.lastreceived)

    from itemdynamic group by itemid)

  • Suggested answer
    Excel Vertical Profile Picture
    Excel Vertical 630 on at

    Hi Danny,

    It seems you know a bit about SQL. However, do you have any knowledge of using CASE statements in your SQL? If you need more help then I can give an idea of how to use CASE statements inside your SQL statement to solve your problem. You can also do Web search on it and there are plenty of examples on the Web.

    Happy Holidays!

    Paul

  • Suggested answer
    Excel Vertical Profile Picture
    Excel Vertical 630 on at

    Sorry Danny.

    I made a mistake of suggesting case statement. Here's the SQL statement to solve your problem. I tested this on my test database and seems to work. I hope it works for you as well.

    Select itemlookupcode, description, quantity

    from

    (SELECT ISNULL(lastsold,'01/01/2045') as lastsold, itemlookupcode, description, quantity

     FROM [data table]

     ) a

     where lastsold='01/01/2045' or (lastsold between '01/01/2011' and '12/31/2011')

    For last received you can add ISNULL(lastreceived,'01/01/2045') to the above statement with lastreceived conditions in where clause or simply make a separate SQL query for that.

    Paul

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans