Get Serial No. Information Records, which were in stock on a given date

Hi,

I am working on a project where the Serial No. Information table (Table 6504) has been customised, and is used quite heavily.
I need to be able to find what items from this table, had a quantity in stock > 0 at a given date.

So to do this I am trying to use a NAV query.

In the past this was done using some flowfields and flowfilters, but this is quite slow to return the results.
Our hope was we would be able to reproduce this using a NAV Query to speed the process up, however we are having some trouble.

I can create this fine in SQL itself, and this is essentially what we are trying to reproduce:

SELECT sni.[Serial No_],
       sub.Qty_in_Stock
FROM dbo.[SFS$Serial No_ Information] AS sni WITH (NOLOCK)
    OUTER APPLY
(
    SELECT SUM(ile.Quantity) AS Qty_in_Stock
    FROM dbo.[SFS$Item Ledger Entry] AS ile WITH (NOLOCK)
    WHERE ile.[Posting Date] <= '28 Aug 2018'
          AND ile.[Serial No_] = sni.[Serial No_]
          AND ile.[Variant Code] = sni.[Variant Code]
          AND ile.[Item No_] = sni.[Item No_]
) sub
WHERE sub.Qty_in_Stock = 1;

The NAV code used a FlowField "Inventory at Date" which is almost identical to the standard field "Inventory", however it uses an extra filter for "Date Filter" on "Posting Date" in Item Ledger Entry. So in code we could write

SerialNoInformation.SETRANGE("Date Filter", 0D, ParamDate);
SerialNoInformation.SETRANGE("Inventory at Date", 1);
SerialNoInformation.FINDSET;

So we created a Query, which joins on "Item No", "Variant Code" & "Serial No." to "Item Ledger Entry" and looks like this:



Then in the code we would set our filters on the query to filter for:

Query.SETFILTER("Posting_Date",  '..%1', ParamDate);
Query.SETFILTER("Sum_Quantity", '>%1', 0);

However we get way more entries returned than we expect, and it is quite slow.
We know the SQL is correct, and the old code returns the same amount of records.

I have tried using some of the different Join options in the Query, but they do not seem to help.
It just seems like this is not possible to do with a NAV Query.
I was hoping someone may have some advice?