-- Find the difference qty between Netsuite server and Drax server --

WITH cte_1 AS (
    SELECT
        ns.container,
        SUM(items_quantity_received) AS ns_total
    FROM
        whsestage..stage_inbound_kt1 ns
    GROUP BY ns.container
),

cte_2 AS (
    SELECT
        dr.user_def2,
        CAST(SUM(total_qty) AS INT) AS drax_total,
        dr.receipt_id
    FROM
        [WMS-DRAX].[ILS].[dbo].[UPLOAD_RECEIPT_DETAIL] dr
    GROUP BY
        dr.user_def2,
        dr.receipt_id
)

SELECT
    cte_2.receipt_id
    cte_1.*,
    cte_2.user_def2,
    cte_2.drax_total,
    (drax_total-ns_total), AS difference
FROM      cte_1 JOIN cte_2
ON           cte_1.container COLLATE DATABASE_DEFAULT = cte_2.user_def2 COLLATE DATABASE_DEFAULT >
AND         cte_1.ns_total != cte_2.drax_total
WHERE    LEN(cte_2.receipt_id)<5
AND         (drax_total-ns_total)>0
ORDER BY
                 cte_2.receipt_id,
                 (drax_total-ns_total) DESC