Advertisement
MaximErofeev

Untitled

Apr 7th, 2020
285
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.80 KB | None | 0 0
  1. --For the initial screen, that shows orders, that were placed during the current week,
  2. --this query should be used:
  3.  
  4.  
  5. SELECT
  6.     Vendor.DisplayName AS VendorDisplayName,
  7.     MasterList.ItemNumber,
  8.     MasterList.Description,
  9.     OrderList.OrderQty,
  10.     MasterList.Id,
  11.     MasterList.VendorId
  12. FROM MasterList
  13.         JOIN Vendor ON (MasterList.VendorId = Vendor.Id)
  14.         JOIN OrderList ON (
  15.             MasterList.VendorId = OrderList.VendorId AND
  16.             MasterList.ItemNumber = OrderList.ItemNumber AND
  17.             MasterList.Description = OrderList.Description AND
  18.             OrderList.CreatedOn > dbo.weekStart()
  19.         )
  20. WHERE OrderList.StoreId = ${action.storeId}
  21.  
  22.  
  23.  
  24. --for the CombinedOrderList screen should be used another query.
  25. --The unfiltered (and limited) one:
  26.  
  27.  
  28. SELECT top(100)
  29.     V.DisplayName AS VENDOR,
  30.     ML.ItemNumber AS ITEM,
  31.     ML.DESCRIPTION AS DESCRIPTION,
  32.     OLL.OrderQty AS LAST_WEEK,
  33.     OL.OrderQty AS THIS_WEEK,
  34.     ML.ItemInventoryCount AS INVENTORY,
  35.     OL.StoreId AS STORE_ID,
  36.     OL.Id AS ORDER_ID,
  37.     ML.Id AS MASTER_ID
  38. FROM dbo.MasterList ML JOIN dbo.Vendor V ON ML.VendorId = V.Id
  39.     LEFT JOIN dbo.OrderList OLL ON
  40.             ML.VendorId = OLL.VendorId
  41.         AND ML.ItemNumber = OLL.ItemNumber
  42.         AND ML.Description = OLL.Description
  43.         AND OLL.CreatedOn <=dbo.weekStart()
  44.         AND OLL.CreatedOn > dateadd(week, -1, dbo.weekStart())
  45.  
  46.     LEFT JOIN dbo.OrderList OL ON
  47.             ML.VendorId = OL.VendorId
  48.         AND ML.ItemNumber = OL.ItemNumber
  49.         AND ML.Description = OL.Description
  50.         AND OL.CreatedOn > dbo.weekStart()
  51.  
  52. WHERE ( OL.StoreId IS NULL OR OL.StoreId = ${action.storeId} )
  53.     AND ( OLL.StoreId IS NULL OR OLL.StoreId = ${action.storeId} )
  54.     AND ML.Id > 0
  55. ORDER BY ML.Id
  56. --(initially ML.Id > 0, after next scrolling ML.Id > 100 …)
  57.  
  58.  
  59.  
  60. -- CombinedOrderList screen with example of applied filters:
  61.  
  62.  
  63. SELECT top(100)
  64.         V.DisplayName AS VENDOR,
  65.         ML.ItemNumber AS ITEM,
  66.         ML.DESCRIPTION AS DESCRIPTION,
  67.         OLL.OrderQty AS LAST_WEEK,
  68.         OL.OrderQty AS THIS_WEEK,
  69.         ML.ItemInventoryCount AS INVENTORY,
  70.         OL.StoreId AS STORE_ID,
  71.         OL.Id AS ORDER_ID,
  72.         ML.Id AS MASTER_ID
  73. FROM dbo.MasterList ML JOIN dbo.Vendor V ON ML.VendorId = V.Id
  74.     LEFT JOIN dbo.OrderList OLL ON
  75.             ML.VendorId = OLL.VendorId
  76.         AND ML.ItemNumber = OLL.ItemNumber
  77.         AND ML.Description = OLL.Description
  78.         AND OLL.CreatedOn <=dbo.weekStart()
  79.         AND OLL.CreatedOn > dateadd(week, -1, dbo.weekStart())
  80.     LEFT JOIN dbo.OrderList OL ON
  81.             ML.VendorId = OL.VendorId
  82.         AND ML.ItemNumber = OL.ItemNumber
  83.         AND ML.Description = OL.Description
  84.         AND OL.CreatedOn > dbo.weekStart()
  85.  
  86. WHERE ( OL.StoreId IS NULL OR OL.StoreId = ${action.storeId} )
  87.     AND ( OLL.StoreId IS NULL OR OLL.StoreId = ${action.storeId} )
  88.  
  89.     AND V.DisplayName LIKE '%PD%'
  90.     AND ML.ItemNumber LIKE '%1024%'
  91.     AND ML.Description LIKE '%Glory%'
  92.  
  93.     AND ML.Id > 0
  94. ORDER BY ML.Id
  95. --(initially ML.Id > 0, after next scrolling ML.Id > 100 …)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement