bisonn

Untitled

Oct 15th, 2018
569
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.08 KB | None | 0 0
  1. Select count(*) Value
  2. from
  3. (
  4.     Select distinct p1.OrderNO, p2.[State]
  5.     from
  6.     (
  7.         Select *
  8.         from
  9.         (
  10.             Select [OrderNO], [Host]
  11.             from [dbo].[cc_OrderHead](nolock)
  12.             where [OrderType]=21
  13.             and [OrderDate]>=getdate()-50
  14.        
  15.         ) t1
  16.         outer apply
  17.         (
  18.             Select [ItemId], [Quantity], CscuName, VariantCode
  19.             from [dbo].[cc_OrderItems](nolock)
  20.             where [OrderNo]=t1.OrderNO
  21.             and Host=t1.Host
  22.         ) t2
  23.     ) p1
  24.     left join
  25.     (
  26.         Select *
  27.         from
  28.         (
  29.             Select [OrderNO], [Host], [State]
  30.             from [dbo].[cc_OrderHead](nolock)
  31.             where [OrderType]=36
  32.             and [OrderDate]>=getdate()-50
  33.         ) t11
  34.         outer apply
  35.         (
  36.             Select [ItemId], [Quantity], CscuName, VariantCode
  37.             from [dbo].[cc_OrderItems](nolock)
  38.             where [OrderNo]=t11.OrderNO
  39.             and Host=t11.Host
  40.         ) t21
  41.     ) p2
  42.     on p1.OrderNO=p2.OrderNO and p1.ItemId=p2.ItemId and p1.CscuName=p2.CscuName and p1.VariantCode=p2.VariantCode
  43.     where p1.Quantity>p2.Quantity
  44.     and p2.[State]  not in (46,42,48,49,139,54)
  45.     and p2.[State] not in (select id from [dbo].[cc_Ref_OrderStates](nolock) where code in ('20','21','22','23'))
  46. ) w
Advertisement
Add Comment
Please, Sign In to add comment