You’re offline. This is a read only version of the page.
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
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
Got it. I need to change RCRDTYPE = 2
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
of course every item has different date received. I want the first date received of the item.
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
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
Actually i want to show Date recieved, Last_Sale_Date & Last_Rcpt_Date.
I want to add Date recieved of every item number.
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);
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (