Minimum and maximum from ReqItemTable

(0) Share

Report

Posted on by faiz7049

1,757

Hi Experts,

Customer requirement to get item minimum and maximum from ReqItemTable with Availablephysical and Ordered quantities

The AvilablePhysical and Ordered quantity not respecting inventDimId. It is giving me all AvailablePhysical and Ordered quantities for an itemid.

SELECT 
    R.ITEMID,
    INVENTDIM.CONFIGID,
    INVENTDIM.INVENTSTYLEID,
    INVENTDIM.INVENTSIZEID,
    INVENTDIM.INVENTCOLORID,
    INVENTDIM.INVENTSITEID,
	INVENTDIM.INVENTLOCATIONID,
    R.COVINVENTDIMID,
    R.MININVENTONHAND,
    R.MAXINVENTONHAND,
    SUM(ISUM.AvailPhysical) AS TotalAvailPhysical,
	SUM(ISUM.ORDERED) AS TotalAvailORDER
FROM 
    REQITEMTABLE R
JOIN 
    INVENTDIM ON R.COVINVENTDIMID = INVENTDIM.INVENTDIMID
JOIN 
    INVENTSUM ISUM ON R.ITEMID = ISUM.ITEMID 
WHERE 
    R.COVRULE = 2
   
GROUP BY
    R.ITEMID,
    INVENTDIM.CONFIGID,
    INVENTDIM.INVENTSTYLEID,
    INVENTDIM.INVENTSIZEID,
    INVENTDIM.INVENTCOLORID,
    INVENTDIM.INVENTSITEID,
	INVENTDIM.INVENTLOCATIONID,
    R.COVINVENTDIMID,
    R.MININVENTONHAND,
    R.MAXINVENTONHAND