web
You’re offline. This is a read only version of the page.
close
Skip to main content
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

I have the same question (0)
  • 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);


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

    I want to add Date recieved of every item number.

  • 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.

  • 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

    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

  • 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

    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

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

    Got it.  I need to change RCRDTYPE = 2

  • 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

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

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans