March 9, 2023 at 11:14 pm
SO I need to sum for each Individual Part that is need each day. You can order Parts individually or in a KIT.
For instance a 455-TP kit includes (455-01,455-02,455-05,455-12)
So and order might be 2 of a 455-TP and 2 of a 455-02 so for this order I would need 2 each of 455-01,455-05,455-12 and 4 of 455-02
Salesorderlines is the order table with the Part id (omlpartid) and Quantity (omlorderquantity).
I get the Individual Parts sum from there without any trouble but I must look in the PartMaterials table to get what parts are in the KIT and that is where I get lost. I tried a case statement but is not giving me the correct numbers. I hope this is understandable.
select case when omlPartID in('455-TP','470-TP','450-TP') then immPartID else omlpartid end as omlpartid
, imrQuantityOnHand,imrQuantityAllocated
,day0 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-10-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day1 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-13-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day2 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-14-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day3 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-15-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day4 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-16-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day5 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-17-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day6 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-20-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day7 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-21-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day8 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-22-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day9 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-23-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,day10 = ISNULL( SUM( CASE WHEN ompRequestedShipDate = '03-24-2023' THEN omlorderquantity ELSE 0 END ), 0 )
,(select sum(jmpProductionQuantity-jmpQuantityCompleted)
from M1_KF.dbo.jobs
where jmppartid=omlPartID and jmpProductionComplete !=-1 and jmpJobDate >'01-01-2023'
group by jmpPartID) as job
from m1_kf.dbo.SalesOrders
left outer join M1_KF.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID
left outer join M1_KF.dbo.parts on impPartID=omlPartID
left outer join M1_KF.dbo.PartRevisions on imrPartID=omlPartID
left outer join PartMaterials on immPartID=omlPartID
where ompClosed !=-1 and UOMPTRUCKNUMBER !='' and impPartClassID in ('FGM' ,'KT')
group by omlPartID,immPartID, imrQuantityOnHand,imrQuantityAllocated
order by omlPartID, immPartID,imrQuantityOnHand,imrQuantityAllocated
March 11, 2023 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 12, 2023 at 5:20 pm
CREATE TABLE and INSERT scripts?
Wouldn't you have to "explode" the kits so that they return their component parts and union that with the non-kit records, and finally sum that? Or if you just want the number of parts from kits, join to a KitComponents table and multiply KitComponents.Quantity * OrderDetails.OrderQuantity?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply