Список партий, по которым есть остатки (на дату)

[Ace of Database]

В примере в качестве параметров используется _itemId (если не задан - то по всем номенклатурам), _dateFrom - дата, на которую нужны остатки, _inventLocationId - склад.

    while select
    sum(Qty), sum(costAmountPosted), sum(costAmountAdjustment) from InventTrans
    group by ItemId
    where
    (
        !_itemId || InventTrans.ItemId == _itemId
    )
    &&
    (
        InventTrans.DatePhysical < _dateFrom
    )
    &&
    (
        InventTrans.StatusReceipt == StatusReceipt::Purchased ||
        InventTrans.StatusReceipt == StatusReceipt::Received ||
        InventTrans.StatusIssue == StatusIssue::Sold ||
        InventTrans.StatusIssue == StatusIssue::Deducted
    )
    join inventDim group by InventBatchId
    where inventDim.inventDimId == inventTrans.inventDimId &&
    (!_inventLocationId || inventDim.InventLocationId == _inventLocationId)
    {
    }