web
You’re offline. This is a read only version of the page.
close
Skip to main content
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)
  • Suggested answer
    Excel Vertical Profile Picture
    Excel Vertical 630 on at
    Re: Wanted SQL help to get a report for all my old stock

    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

  • Suggested answer
    Excel Vertical Profile Picture
    Excel Vertical 630 on at
    Re: Wanted SQL help to get a report for all my old stock

    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

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at
    Re: Wanted SQL help to get a report for all my old stock

    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)

  • Nashat Profile Picture
    Nashat 45 on at
    Re: Wanted SQL help to get a report for all my old stock

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

  • Sad Profile Picture
    Sad 650 on at
    Re: Wanted SQL help to get a report for all my old stock

    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
    Re: Wanted SQL help to get a report for all my old stock

    @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

  • Nashat Profile Picture
    Nashat 45 on at
    Re: Wanted SQL help to get a report for all my old stock

    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)

  • Sad Profile Picture
    Sad 650 on at
    Re: Wanted SQL help to get a report for all my old stock

    Hi Danny, try to use similar to this :

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

    good luck

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

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

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

Mansi Soni – Community Spotlight

We are honored to recognize Mansi Soni as our August 2025 Community…

Congratulations to the July 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