T-SQL Row Aggregation

Question

The structure of the table I'm working with: BoM table (which product is required to make the finished product): Here is the @BomList table

╔══════════════╦═════════════╦══════════════╗
║ ParentPartId ║ ChildPartId ║ ChildPartQty ║
╠══════════════╬═════════════╬══════════════╣
║ MCD1         ║  2000416027 ║            2 ║
║ MCD1         ║  2000316029 ║            1 ║
║ MCD1         ║  2001020022 ║            1 ║
╚══════════════╩═════════════╩══════════════╝

Work orders table (what you need to create):

╔═════════════╦═════════════════╦══════════════╗
║ WorkOrderId ║ WorkOrderItemId ║ ParentPartId ║
╠═════════════╬═════════════════╬══════════════╣
║        1234 ║            6735 ║ MCD1         ║
╚═════════════╩═════════════════╩══════════════╝

How much has been done for the order. The following is the version of the t_WorkCenterRouting table for cutting.

╔═════════════════╦═════════════╦═════╗
║ WorkOrderItemId ║ ChildPartId ║ Qty ║
╠═════════════════╬═════════════╬═════╣
║            6735 ║  2000316029 ║   1 ║
║            6735 ║  2001020022 ║   1 ║
║            6736 ║  2000416027 ║  10 ║
║            6736 ║  2000316029 ║   3 ║
║            6736 ║  2001020022 ║   3 ║
║            6737 ║  2000416027 ║   1 ║
║            6737 ║  2000316029 ║   1 ║
║            6737 ║  2001020022 ║   1 ║
╚═════════════════╩═════════════╩═════╝

The result I'm looking for Thus, from the above table it can be seen that WorkOrderItem 6735 cannot make a complete product, because it is missing 2000416027 x2. Therefore, I do not want them to be displayed.

We can also see that WorkOrderItem 6737 cannot make a complete product because it is missing 2000416027 x1. Therefore, I do not want them to be displayed.

We also see that WorkOrderItem 6736 can make a total of x3 products.

. , :

╔═════════════════╦═════╗
║ WorkOrderItemId ║ Qty ║
╠═════════════════╬═════╣
║            6737 ║   3 ║
╚═════════════════╩═════╝

, :

SELECT twcr.WorkOrderItemId, 
        bl.ChildPartQty, 
        SUM( ISNULL( twcr.Qty, 0 ) ) / bl.ChildPartQty AS 'TotalQty'
FROM @BomList bl
LEFT JOIN niko.t_WorkCenterRouting twcr ON twcr.ChildCode = bl.ChildPartId AND twcr.WorkCenterId = 4
WHERE (twcr.WorkCenterRoutingId IS NULL OR twcr.ParentCode = @ProductCode)
GROUP BY twcr.WorkOrderItemId, twcr.ChildCode, bl.ChildPartQty

, , 6735 2000416027. , , qty.

- , . .

....................

    SELECT wcr.WorkOrderItemId,
                ( SELECT TOP 1 ROUND( SUM( ISNULL( twcr.Qty, 0 ) / bl.ChildPartQty ), 0, 1 )
                                        FROM #BomList bl
                                        LEFT JOIN niko.t_WorkCenterRouting twcr ON twcr.ChildCode = bl.ChildPartId AND twcr.WorkCenterId = 4 AND twcr.ParentCode = @WorkOrderCode AND  wcr.WorkOrderItemId = twcr.WorkOrderItemId
                                        GROUP BY twcr.WorkOrderItemId, bl.ChildPartId, bl.ChildPartQty
                                        ORDER BY SUM( ISNULL( twcr.Qty, 0 ) / bl.ChildPartQty ) ) AS 'Qty'
    FROM niko.t_WorkCenterRouting wcr
    WHERE wcr.WorkOrderItemId IN ( SELECT twcr.WorkOrderItemId
                                    FROM #BomList bl
                                    LEFT JOIN niko.t_WorkCenterRouting twcr ON twcr.ChildCode = bl.ChildPartId AND twcr.WorkCenterId = 4 AND twcr.ParentCode = @WorkOrderCode
                                    WHERE (twcr.WorkCenterRoutingId IS NULL OR twcr.ParentCode = @WorkOrderCode )
                                    GROUP BY twcr.WorkOrderItemId, bl.ChildPartId, bl.ChildPartQty ) AND
                ( SELECT TOP 1 SUM( ISNULL( twcr.Qty, 0 ) / bl.ChildPartQty )
                        FROM #BomList bl
                        LEFT JOIN niko.t_WorkCenterRouting twcr ON twcr.ChildCode = bl.ChildPartId AND twcr.WorkCenterId = 4 AND twcr.ParentCode = @WorkOrderCode
                        GROUP BY twcr.WorkOrderItemId, bl.ChildPartId, bl.ChildPartQty
                        ORDER BY SUM( ISNULL( twcr.Qty, 0 ) / bl.ChildPartQty ) ) >= 1
    GROUP BY wcr.WorkOrderItemId

. , -. , .

• WorkOrder
• WorkOrderItem
• t_WorkCenterRouting
• t_WorkCenterProductRoute
• ProductFF
• BoM

Answer 1

, SELECT :

SELECT two.WorkOrderItemId, MIN(COALESCE(twcr.Qty/b.ChildPartQty, 0)) as N
FROM t_WorkOrder two
INNER JOIN BOM b on b.ParentpartId = two.ParentPartId
LEFT JOIN t_WorkCenterRouting twcr ON b.ChildPartId = twcr.ChildPartId AND two.WorkOrderItemId = twcr.workOrderItemId
GROUP BY two.WorkOrderItemId
HAVING MIN(COALESCE(twcr.Qty/b.ChildPartQty, 0)) > 0
ORDER BY two.WorkOrderItemId