Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --For the initial screen, that shows orders, that were placed during the current week,
- --this query should be used:
- SELECT
- Vendor.DisplayName AS VendorDisplayName,
- MasterList.ItemNumber,
- MasterList.Description,
- OrderList.OrderQty,
- MasterList.Id,
- MasterList.VendorId
- FROM MasterList
- JOIN Vendor ON (MasterList.VendorId = Vendor.Id)
- JOIN OrderList ON (
- MasterList.VendorId = OrderList.VendorId AND
- MasterList.ItemNumber = OrderList.ItemNumber AND
- MasterList.Description = OrderList.Description AND
- OrderList.CreatedOn > dbo.weekStart()
- )
- WHERE OrderList.StoreId = ${action.storeId}
- --for the CombinedOrderList screen should be used another query.
- --The unfiltered (and limited) one:
- SELECT top(100)
- V.DisplayName AS VENDOR,
- ML.ItemNumber AS ITEM,
- ML.DESCRIPTION AS DESCRIPTION,
- OLL.OrderQty AS LAST_WEEK,
- OL.OrderQty AS THIS_WEEK,
- ML.ItemInventoryCount AS INVENTORY,
- OL.StoreId AS STORE_ID,
- OL.Id AS ORDER_ID,
- ML.Id AS MASTER_ID
- FROM dbo.MasterList ML JOIN dbo.Vendor V ON ML.VendorId = V.Id
- LEFT JOIN dbo.OrderList OLL ON
- ML.VendorId = OLL.VendorId
- AND ML.ItemNumber = OLL.ItemNumber
- AND ML.Description = OLL.Description
- AND OLL.CreatedOn <=dbo.weekStart()
- AND OLL.CreatedOn > dateadd(week, -1, dbo.weekStart())
- LEFT JOIN dbo.OrderList OL ON
- ML.VendorId = OL.VendorId
- AND ML.ItemNumber = OL.ItemNumber
- AND ML.Description = OL.Description
- AND OL.CreatedOn > dbo.weekStart()
- WHERE ( OL.StoreId IS NULL OR OL.StoreId = ${action.storeId} )
- AND ( OLL.StoreId IS NULL OR OLL.StoreId = ${action.storeId} )
- AND ML.Id > 0
- ORDER BY ML.Id
- --(initially ML.Id > 0, after next scrolling ML.Id > 100 …)
- -- CombinedOrderList screen with example of applied filters:
- SELECT top(100)
- V.DisplayName AS VENDOR,
- ML.ItemNumber AS ITEM,
- ML.DESCRIPTION AS DESCRIPTION,
- OLL.OrderQty AS LAST_WEEK,
- OL.OrderQty AS THIS_WEEK,
- ML.ItemInventoryCount AS INVENTORY,
- OL.StoreId AS STORE_ID,
- OL.Id AS ORDER_ID,
- ML.Id AS MASTER_ID
- FROM dbo.MasterList ML JOIN dbo.Vendor V ON ML.VendorId = V.Id
- LEFT JOIN dbo.OrderList OLL ON
- ML.VendorId = OLL.VendorId
- AND ML.ItemNumber = OLL.ItemNumber
- AND ML.Description = OLL.Description
- AND OLL.CreatedOn <=dbo.weekStart()
- AND OLL.CreatedOn > dateadd(week, -1, dbo.weekStart())
- LEFT JOIN dbo.OrderList OL ON
- ML.VendorId = OL.VendorId
- AND ML.ItemNumber = OL.ItemNumber
- AND ML.Description = OL.Description
- AND OL.CreatedOn > dbo.weekStart()
- WHERE ( OL.StoreId IS NULL OR OL.StoreId = ${action.storeId} )
- AND ( OLL.StoreId IS NULL OR OLL.StoreId = ${action.storeId} )
- AND V.DisplayName LIKE '%PD%'
- AND ML.ItemNumber LIKE '%1024%'
- AND ML.Description LIKE '%Glory%'
- AND ML.Id > 0
- ORDER BY ML.Id
- --(initially ML.Id > 0, after next scrolling ML.Id > 100 …)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement