Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / inventory items and da...
Microsoft Dynamics GP (Archived)

inventory items and dates SQL

(0) ShareShare
ReportReport
Posted on by SandeepReddyChaganti 110 
SELECT I.ITEMNMBR Item_Number,
       I.ITEMDESC Item_Description,
       Q.QTYONHND Quantity_on_Hand,
       U.BASEUOFM U_of_M,
       CASE I.ITEMTYPE
          WHEN 1 THEN 'Sales Inventory'
          WHEN 2 THEN 'Discontinued'
          WHEN 3 THEN 'Kit'
          WHEN 4 THEN 'Misc Charges'
          WHEN 5 THEN 'Services'
          WHEN 6 THEN 'Flat Fee'
          END Item_Type,
       I.CURRCOST Current_Cost,
       I.ITMCLSCD Item_Class,
       coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,
       coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,
       coalesce(Q.LSORDVND,'') Last_Vendor

FROM IV00101 I

LEFT OUTER JOIN
     (SELECT ITEMNMBR, MAX(DOCDATE) LastSale
      FROM IV30300
      WHERE DOCTYPE = 6
      GROUP BY ITEMNMBR) S
     ON I.ITEMNMBR = S.ITEMNMBR

INNER JOIN
     IV00102 Q
     ON I.ITEMNMBR = Q.ITEMNMBR
     AND RCRDTYPE = 1

INNER JOIN
     IV40201 U
     ON U.UOMSCHDL = I.UOMSCHDL

WHERE Q.QTYONHND <> 0

i want to add date received of every item.I tried to join IV10200. I'm having weird results.
i tried Inner join IV10200 D on I.ITEMNMBR = D.ITEMNMBR.  But some thing is wrong with my join.



*This post is locked for comments

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: inventory items and dates SQL

    Record Type = 2 will only work if you have a single location. Otherwise, you will have to have a subquery to sort by the latest receipt date

  • Verified answer
    SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    Got it.  I need to change RCRDTYPE = 2

  • SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    Actually I modified like this. But i'm missing some rows after joining first receipt date

    SELECT I.ITEMNMBR Item_Number,

          I.ITEMDESC Item_Description,

          Q.QTYONHND Quantity_on_Hand,

          CASE I.ITEMTYPE

             WHEN 1 THEN 'Sales Inventory'

             WHEN 2 THEN 'Discontinued'

             WHEN 3 THEN 'Kit'

             WHEN 4 THEN 'Misc Charges'

             WHEN 5 THEN 'Services'

             WHEN 6 THEN 'Flat Fee'

             END Item_Type,

          I.CURRCOST Current_Cost,

          I.ITMCLSCD Item_Class,

      D.FIRSTSale Date_received,

          coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,

          coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,

          coalesce(Q.LSORDVND,'') Last_Vendor

    FROM IV00101 I

    LEFT OUTER JOIN

        (SELECT ITEMNMBR, MAX(DOCDATE) LastSale

         FROM IV30300

         WHERE DOCTYPE = 6

         GROUP BY ITEMNMBR) S

        ON I.ITEMNMBR = S.ITEMNMBR

     left outer join

    (Select ITEMNMBR, MIN(DATERECD) FIRSTSale

    from  IV10200

    Group BY ITEMNMBR)D

    on I.ITEMNMBR = D.ITEMNMBR

    INNER JOIN

        IV00102 Q

        ON I.ITEMNMBR = Q.ITEMNMBR

        AND RCRDTYPE = 1

    INNER JOIN

        IV40201 U

        ON U.UOMSCHDL = I.UOMSCHDL

    WHERE Q.QTYONHND <> 0

  • SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    of course every item has different date received. I want the first date received of the item.

  • SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    I might confused you. I'm using below script. I want to add one more coulmn First Receipt date(Date Recieved). I want to show Date recieved, Last_Sale_Date & Last_Rcpt_Date by each item number as below.

    I tried IV10200(Purchase reciepts header). But i haven't recieved better results from it.

    SELECT I.ITEMNMBR Item_Number,

          I.ITEMDESC Item_Description,

          Q.QTYONHND Quantity_on_Hand,

          U.BASEUOFM U_of_M,

          CASE I.ITEMTYPE

             WHEN 1 THEN 'Sales Inventory'

             WHEN 2 THEN 'Discontinued'

             WHEN 3 THEN 'Kit'

             WHEN 4 THEN 'Misc Charges'

             WHEN 5 THEN 'Services'

             WHEN 6 THEN 'Flat Fee'

             END Item_Type,

          I.CURRCOST Current_Cost,

          I.ITMCLSCD Item_Class,

          coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,

          coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,

          coalesce(Q.LSORDVND,'') Last_Vendor

    FROM IV00101 I

    LEFT OUTER JOIN

        (SELECT ITEMNMBR, MAX(DOCDATE) LastSale

         FROM IV30300

         WHERE DOCTYPE = 6

         GROUP BY ITEMNMBR) S

        ON I.ITEMNMBR = S.ITEMNMBR

    INNER JOIN

        IV00102 Q

        ON I.ITEMNMBR = Q.ITEMNMBR

        AND RCRDTYPE = 1

    INNER JOIN

        IV40201 U

        ON U.UOMSCHDL = I.UOMSCHDL

    WHERE Q.QTYONHND <> 0

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: inventory items and dates SQL

    You cannot show Date Received without defining what you mean by that. If you list all items in your inventory and your expectations are to show just one item per line, then you cannot have more than one Date Received, hence Last_Rcpt_Date

  • SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    Actually i want to show Date recieved, Last_Sale_Date & Last_Rcpt_Date.

  • SandeepReddyChaganti Profile Picture
    SandeepReddyChaganti 110 on at
    RE: inventory items and dates SQL

    I want to add Date recieved of every item number.

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: inventory items and dates SQL

    Try this:

    with SalesInfo as (
	select ITEMNMBR, DOCDATE as Last_Sale_Date, ROW_NUMBER() over (partition by ITEMNMBR order by DOCDATE desc) as LINE_NO 
	from IV30300 
	where DOCTYPE = 6
),
ReceiptInfo as (
	select ITEMNMBR, DATERECD as Last_Rcpt_Date, ROW_NUMBER() over (partition by ITEMNMBR order by DATERECD desc) as LINE_NO 
	from IV10200 
),
ItemTypeDesc as (
	select 1 as ITEMTYPE, 'Sales Inventory' as ItemTypeDescription
	union
	select 2, 'Discontinued'
	union
	select 3, 'Kit'
	union
	select 4, 'Misc Charge'
	union
	select 5, 'Services'
	union
	select 6, 'Flat Fee'
)
select a.ITEMNMBR as Item_Number
	, a.ITEMDESC as Item_Description
	, b.QTYONHND as Quantity_On_Hand
	, u.BASEUOFM as U_of_M
	, c.ItemTypeDescription as Item_Type
	, a.CURRCOST as Current_Costt
	, a.ITMCLSCD as Item_Class
	, ISNULL(d.Last_Sale_Date, '19000101') as Last_Sale_Date
	, ISNULL(e.Last_Rcpt_Date, '19000101') as Last_Rcpt_Date
	, b.LSORDVND as Last_Vendor
from IV00101 a 
	INNER JOIN IV00102 b on (a.ITEMNMBR = b.ITEMNMBR) and (b.RCRDTYPE = 1)
	LEFT OUTER JOIN IV40201 u on (a.UOMSCHDL = u.UOMSCHDL)
	INNER JOIN ItemTypeDesc c on (a.ITEMTYPE = c.ITEMTYPE)
	LEFT OUTER JOIN SalesInfo d on (a.ITEMNMBR = d.ITEMNMBR) and (d.LINE_NO = 1)
	LEFT OUTER JOIN ReceiptInfo e on (a.ITEMNMBR = e.ITEMNMBR) and (e.LINE_NO = 1);


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

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Almas Mahfooz Profile Picture

Almas Mahfooz 3 User Group Leader

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans