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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy