SELECT
        r1.OrderNo,
        COUNT(r1.PartNo) AS [Pcs Complete]
    FROM OrderRouting r1
    WHERE r1.Status = 'Current'
        AND r1.WorkCntr = 'Shipping'
    GROUP BY r1.OrderNo

SELECT
    CAST(r.OrderNo AS INT) AS OrderNo,
        (
        SELECT
            r1.OrderNo,
            COUNT(r1.PartNo)
        FROM OrderRouting r1
        WHERE r1.Status = 'Current'
            AND r1.WorkCntr = 'Shipping'
        GROUP BY r1.OrderNo
        ) AS [Pcs Complete]
FROM OrderRouting r
    JOIN OrderDet od ON r.JobNo = od.JobNo
    JOIN Orders o ON od.OrderNo = o.Orderno
WHERE r.Status = 'Current'
        AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping')
GROUP BY CAST(r.OrderNo AS INT)

SELECT
    CAST(r.OrderNo AS INT) AS OrderNo,
        (
        SELECT
            COUNT(r1.PartNo)
        FROM OrderRouting r1
        WHERE r1.Status = 'Current'
            AND r1.WorkCntr = 'Shipping'
        ) AS [Pcs Complete]
FROM OrderRouting r
    JOIN OrderDet od ON r.JobNo = od.JobNo
    JOIN Orders o ON od.OrderNo = o.Orderno
WHERE r.Status = 'Current'
        AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping')
GROUP BY CAST(r.OrderNo AS INT)

SELECT
    CAST(r.OrderNo AS INT) AS OrderNo,
        (
        SELECT
            COUNT(r1.PartNo)
        FROM OrderRouting r1 JOIN OrderRouting r ON r1.OrderNo = r.OrderNo
        WHERE r1.Status = 'Current'
            AND r1.WorkCntr = 'Shipping'
        ) AS [Pcs Complete]
FROM OrderRouting r
    JOIN OrderDet od ON r.JobNo = od.JobNo
    JOIN Orders o ON od.OrderNo = o.Orderno
WHERE r.Status = 'Current'
        AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping')
GROUP BY CAST(r.OrderNo AS INT)

SELECT
    CAST(r.OrderNo AS INT) AS OrderNo
FROM OrderRouting r
    JOIN OrderDet od ON r.JobNo = od.JobNo
    JOIN Orders o ON od.OrderNo = o.Orderno
    JOIN
    (
            SELECT r1.OrderNo,
            COUNT(r1.PartNo)
        FROM OrderRouting r1 JOIN OrderRouting r ON r1.OrderNo = r.OrderNo
        WHERE r1.Status = 'Current'
            AND r1.WorkCntr = 'Shipping'
    ) AS [PCsComplete] ON PcsComplete.OrderNo = r.OrderNo
WHERE r.Status = 'Current'
        AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping')
GROUP BY CAST(r.OrderNo AS INT)