--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() AND
             OrderList.StoreId = ${action.storeId}
        )
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 <=dbo2.weekStart()
        AND OLL.CreatedOn > dateadd(week, -1, dbo2.weekStart())
         AND OLL.StoreId = ${action.storeId}
    LEFT JOIN dbo.OrderList OL ON
            ML.VendorId = OL.VendorId
        AND ML.ItemNumber = OL.ItemNumber
        AND ML.Description = OL.Description
        AND OL.CreatedOn > dbo2.weekStart()
         AND OLL.StoreId = ${action.storeId}
WHERE
     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 <=dbo2.weekStart()
        AND OLL.CreatedOn > dateadd(week, -1, dbo2.weekStart())
         AND OLL.StoreId = ${action.storeId}
    LEFT JOIN dbo.OrderList OL ON
            ML.VendorId = OL.VendorId
        AND ML.ItemNumber = OL.ItemNumber
        AND ML.Description = OL.Description
        AND OL.CreatedOn > dbo2.weekStart()
         AND OL.StoreId = ${action.storeId}

WHERE
         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 …)