Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / inventory movement query
Microsoft Dynamics SL (Archived)

inventory movement query

(0) ShareShare
ReportReport
Posted on by Tom R. B. 55

I am trying to develop a query to tell me the last time an inventory item was acted upon in the system, then also tell me its total cost and total quantity on hand.  I want to use the intran and the itemsite tables, and use the field intvid as the key field.

So far, this query works for me and returns good results:

select [Inventory ID], [Total Cost], [Total Quantity]

from

(

 select itemsite.invtid as "Inventory ID",
  sum (itemsite.TotCost) as "Total Cost",
        sum (itemsite.QtyOnHand) as "Total Quantity"
      
      
       from ItemSite
       group by itemsite.invtid
       
  )
 
 itemsite

 

AND this query works for me

 

select [Inventory ID], [Last Touched]

from

(
select intran.invtid as "Inventory ID", MAX (intran.Perpost) as "Last Touched"

from Intran where

  intran.Rlsed = 1
  and intran.S4Future05 = 0
 
  group by intran.invtid
 
 )
 
  intran

 

But, I need to join them into one query to product a list of 4 fields and I can not get that to work.  I am wondering the proper format.  Any help would be appreciated

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: inventory movement query

    If you want LEFT OUTER JOIN

    select isite.[Inventory ID], isite.[Total Cost], isite.[Total Quantity], itran.[Last Touched]
    from
    (
    select itemsite.invtid as "Inventory ID",
    sum (itemsite.TotCost) as "Total Cost",
    sum (itemsite.QtyOnHand) as "Total Quantity"
    from ItemSite
    group by itemsite.invtid
    ) AS isite
    LEFT OUTER JOIN
    (
    select intran.invtid as "Inventory ID", MAX (intran.Perpost) as "Last Touched"
    from Intran where
    intran.Rlsed = 1
    and intran.S4Future05 = 0
    group by intran.invtid
    ) AS itran
    ON isite.[Inventory ID] = itran.[Inventory ID]

    If you want INNER JOIN,

    select isite.[Inventory ID], isite.[Total Cost], isite.[Total Quantity], itran.[Last Touched]
    from
    (
    select itemsite.invtid as "Inventory ID",
    sum (itemsite.TotCost) as "Total Cost",
    sum (itemsite.QtyOnHand) as "Total Quantity"
    from ItemSite
    group by itemsite.invtid
    ) AS isite,
    (
    select intran.invtid as "Inventory ID", MAX (intran.Perpost) as "Last Touched"
    from Intran where
    intran.Rlsed = 1
    and intran.S4Future05 = 0
    group by intran.invtid
    ) AS itran
    WHERE isite.[Inventory ID] = itran.[Inventory ID]

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: inventory movement query

    select ITM.*, [Last Touched] from

    (select [Inventory ID], [Total Cost], [Total Quantity]

    from

    (

    select itemsite.invtid as "Inventory ID",

     sum (itemsite.TotCost) as "Total Cost",

           sum (itemsite.QtyOnHand) as "Total Quantity"

          from ItemSite

          group by itemsite.invtid

     )

    itemsite) ITM

    JOIN

    (select [Inventory ID], [Last Touched]

    from

    (

    select intran.invtid as "Inventory ID", MAX (intran.Perpost) as "Last Touched"

    from Intran where

     intran.Rlsed = 1

     and intran.S4Future05 = 0

     group by intran.invtid

    )   intran) INT

     on ITM.[Inventory ID] = INT.[Inventory ID]

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans