Pasted

Select count(*) Value
from
(
    Select distinct p1.OrderNO, p2.[State]
    from
    (
        Select *
        from
        (
            Select [OrderNO], [Host]
            from [dbo].[cc_OrderHead](nolock)
            where [OrderType]=21
            and [OrderDate]>=getdate()-50
        
        ) t1
        outer apply
        (
            Select [ItemId], [Quantity], CscuName, VariantCode
            from [dbo].[cc_OrderItems](nolock)
            where [OrderNo]=t1.OrderNO
            and Host=t1.Host
        ) t2
    ) p1
    left join
    (
        Select *
        from
        (
            Select [OrderNO], [Host], [State]
            from [dbo].[cc_OrderHead](nolock)
            where [OrderType]=36
            and [OrderDate]>=getdate()-50
        ) t11
        outer apply
        (
            Select [ItemId], [Quantity], CscuName, VariantCode
            from [dbo].[cc_OrderItems](nolock)
            where [OrderNo]=t11.OrderNO
            and Host=t11.Host
        ) t21
    ) p2
    on p1.OrderNO=p2.OrderNO and p1.ItemId=p2.ItemId and p1.CscuName=p2.CscuName and p1.VariantCode=p2.VariantCode
    where p1.Quantity>p2.Quantity
    and p2.[State]  not in (46,42,48,49,139,54)
    and p2.[State] not in (select id from [dbo].[cc_Ref_OrderStates](nolock) where code in ('20','21','22','23'))
) w