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 AX (Archived) / Join InventSum to Cust...
Microsoft Dynamics AX (Archived)

Join InventSum to Custinvoicetrans in MSSQL

(0) ShareShare
ReportReport
Posted on by Community Member

Good day,

I am trying to join the custinvoicetrans(itemid) to the inventsum table(itemid)

However I am not getting the correct Dim (Config/Color) when returning, which results in the incorrect inventory quantity.

My current query is :

SELECT c.ITEMID,

i2.CONFIGID,

i2.INVENTCOLORID,

SUM(c.inventqty),

SUM(i.AVAILPHYSICAL)

FROM dbo.INVENTSUM i

INNER JOIN dbo.INVENTDIM i2 ON i2.INVENTDIMID = i.INVENTDIMID

INNER JOIN dbo.CUSTINVOICETRANS c ON c.ITEMID = i.ITEMID AND c.DATAAREAID = i.DATAAREAID AND c.PARTITION = i.PARTITION

WHERE DATEDIFF(m,c.INVOICEDATE,GETDATE())=0

GROUP BY c.ITEMID,

i2.CONFIGID,

i2.INVENTCOLORID;

 

 

 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    Why are you joining CUSTINVOICETRANS at all?  You haven't used any fields from that table at all, in a WHERE clause or in the SELECT field list.  As written, you're almost certainly just getting a 1:n explosion of your data which multiplies the numbers.

    Also, the ON clause in your joins should include DATAAREAID at the very least, and [PARTITION] if you're using R2 or R3.

    If you only want to show the AVAILPHYSICAL for the ITEMID on the CUSTINVOICETRANS record, then you should be considering the INVENTDIMID field on the CUSTINVOICETRANS record itself as well.  Furthermore you must deal with the fact that you will practically always have more than 1 record in INVENTSUM and/or CUSTINVOICETRANS that will explode your join results unintentionally.

    What is it you are trying to do exactly?

  • Community Member Profile Picture
    Community Member on at

    Thanks for your reply Brandon.

    I have updated my answer above.

    We Only have 1 company in AX.

    I am trying to get the qty of sales from the invoicetrans, and the combined current on hand from inventsum to determine stock cover for the month.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    So you want the total sales for the month, and the current on-hand for the item/dimension combination along with it?

    Even though you only have 1 company, you should always join with DATAAREAID, and [PARTITION] where present.  SQL Server will not properly utilize indexes unless you do, and over time the performance of the query will be terrible.

  • Community Member Profile Picture
    Community Member on at

    Thats correct Brandon,

    Thanks for the info of datareadid and partition. I will definately use this going forward.

  • Community Member Profile Picture
    Community Member on at

    As the above query stands,

    Should this return the correct information?

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I think this is probably closer to what you want.

    The CustInvoiceTrans table is summarized into 1 record for each ItemId, InventLocationId, ConfigId, and InventColorId combination, and then the InventSum table is summarized the same, and then they are joined, providing only 1 record per that combination of fields.  Note that date range on CustInvoiceTrans that should be adjusted per your fiscal periods.

    with t as (

  select 
      t.[PARTITION], 
      t.DATAAREAID, 
      t.ITEMID, 
      d.INVENTLOCATIONID, d.CONFIGID, d.INVENTCOLORID,
      SUM(t.INVENTQTY) as INVENTQTY
    from CUSTINVOICETRANS t
    join INVENTDIM d on d.[PARTITION] = t.[PARTITION] 
                    and d.DATAAREAID  = t.DATAAREAID 
                    and d.INVENTDIMID = t.INVENTDIMID
    where t.INVOICEDATE BETWEEN N'2016-01-01' AND N'2016-01-31'
    group by t.[PARTITION], t.DATAAREAID, t.ITEMID,
      d.INVENTLOCATIONID, d.CONFIGID, d.INVENTCOLORID

), s as (

  select
      s.[PARTITION],
      s.DATAAREAID,
      s.ITEMID,
      d.INVENTLOCATIONID, d.CONFIGID, d.INVENTCOLORID,
      SUM(s.POSTEDQTY + s.RECEIVED - s.DEDUCTED) as ONHANDQTY
    from INVENTSUM s
    join INVENTDIM d on d.[PARTITION] = s.[PARTITION]
                    and d.DATAAREAID  = s.DATAAREAID
                    and d.INVENTDIMID = s.INVENTDIMID
    group by s.[PARTITION], s.DATAAREAID, s.ITEMID,
      d.INVENTLOCATIONID, d.CONFIGID, d.INVENTCOLORID

)
select 
    t.*, 
    COALESCE(s.ONHANDQTY, 0) as ONHANDQTY
  from t
  left join s on s.[PARTITION] = t.[PARTITION]
             and s.DATAAREAID  = t.DATAAREAID
             and s.ITEMID      = t.ITEMID
             and s.INVENTLOCATIONID = t.INVENTLOCATIONID
             and s.CONFIGID         = t.CONFIGID
             and s.INVENTCOLORID    = t.INVENTCOLORID
  order by t.[PARTITION], t.DATAAREAID, t.ITEMID, 
    t.INVENTLOCATIONID, t.CONFIGID, t.INVENTCOLORID
  • Community Member Profile Picture
    Community Member on at

    Thanks so much Brandon.

    I have a much better understanding now.

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 AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans