Hi,
I would like to download inventory balance at specific range date.
From query i created, for items that has transaction within the date range, it is printed in system.
But if the transaction only happen before the date, and not hv any transaction during the date range, the data is not printed.
so may i know how i can still get the inventory balance eventhough there is no transaction during the date.
i'm using table/field whinr110.qhnd

thank you.

Hi,

Say for an item X, there is no transaction between date.f and date.t. You can take the last record in whinr110 table for that item before to.date.

select whinr110.*
from whinr110
where whinr110._index1 = {:your_item,:your_warehouse}
and whinr110.trdt <= :date.t
order by whinr110.trdt desc,whinr110.seqn desc
as set with 1 rows
selectdo
on.hand = :whinr110.qhnd
endselect

If you want on.hand quantity even if it doesn't have any transaction before date.t i.e. if it has transaction in some future date, remove the condition of checking date.

The script should write at my report or query?

i hv put my query:
select
whinr110.item, | Item
tcibd001.dsca, | Description
tcibd001.citg, | Item Group
whinr110.qstk, | Quantity (Inventory Unit)
whinr110.kost, | Transaction Type
whinr110.qhnd, | Inventory After Transaction
whinr110.trdt, | Transaction Date
whinr110.cwar | Warehouse
from
tcibd001, | Items - General
whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
tcibd001.citg >= tcibd001.citg.f and
tcibd001.citg <= tcibd001.citg.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whinr110.item = tcibd001.item

Hi,

You need to do it in your report script.