Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment