Hi,
Just like table whinr110,
Item Warehouse Date Stock
===============================================
A A01 2006-12-30 100
A A01 2006-12-25 80
A A02 2006-12-27 20
B A01 2006-12-21 30
B A02 2006-12-28 25
B A02 2006-12-26 18

Is there an easy way to get the stock at a time point, like '2006-12-31'

result may be

Item Warehouse Stock
===================================================
A A01 100
A A02 20
B A01 30
B A02 25

Thank you.

Something like
select t_item, t_stoc from twhinr110700
where t_trdt = '2006-04-17 23:19:53.000'

If I understand correctly, you want per warehouse/item combination, the stock corresponding to the input date, based on whinr110.

So the following concept will do the job probably in the most efficient/performant way:



|****************************** DECLARATION SECTION ***************************
declaration:

table twhinr110

extern domain tcdate stoc.date |considering that user inputs requested date,
extern domain tcitem item.f, item.t |item range
extern domain tccwar cwar.f, cwar.t |and warehouse range

domain tcbool init
domain tcitem hold.item
domain tccwar hold.cwar
domain tcdate max.trdt
domain tcqiv1 stoc


|****************************** PROGRAM SECTION ***************************


|****************************** ZOOM FROM SECTION ***************************
|****************************** FORM SECTION ***************************
|****************************** CHOICE SECTION ***************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** FIELD SECTION ***************************
|****************************** MAIN TABLE SECTION ***************************
|****************************** FUNCTION SECTION ***************************

function read.main.table()
{
init = true

select whinr110.*
from whinr110
where whinr110._index1 inrange {:item.f, :cwar.f} and
{:item.t, :cwar.t}
and whinr110.trdt < :stoc.date
order by whinr110._index1
selectdo
if init then
init = false
hold.item = whinr110.item
hold.cwar = whinr110.cwar
max.trdt = whinr110.trdt
stoc = whinr110.stoc
endif

if whinr110.item <> hold.item or whinr110.cwar <> hold.cwar then
rprt_send()
hold.cwar = whinr110.cwar
hold.item = whinr110.item
max.trdt = whinr110.trdt
stoc = whinr110.stoc
else
if whinr110.trdt > max.trdt then
max.trdt = whinr110.trdt
stoc = whinr110.stoc
endif
endif
selecteos
rprt_send()
endselect
}


In the report the printed input fields should be hold.cwar, hold.item, stoc (and if you want to print the date of last transaction within selected date, you can add max.date).

Let us know if it works fine for you. Good luck!!


Regards,
Eli Nager

Sir i need also a help that i want to assign a specific date and time into whinr110.trdt..

ex of my codes..

domain tcqdhc jan
domain tctrns.date jandate

domain tcqdhc feb
domain tctrns.date febdate


|January date
jandate = 01-31-2010 23:59

select whinr110.qhnd,whinr110.trdt
from whinr110 |where whinr110.trdt < :trdt.f
where whinr110.trdt <= :jandate
and whinr110.cwar={:whwmd215.cwar}
and whinr110.item={:whwmd215.item}
selectdo
jan=whinr110.qhnd |ENDING INVENTORY of the Month January
endselect


|February date
febdate = 02-31-2010 23:59

select whinr110.qhnd,whinr110.trdt
from whinr110 |where whinr110.trdt < :trdt.f
where whinr110.trdt <= :febdate
and whinr110.cwar={:whwmd215.cwar}
and whinr110.item={:whwmd215.item}
selectdo
feb=whinr110.qhnd |ENDING INVENTORY of the Month February
endselect

the report is just like this

ENDING INVENTORY PER MONTH
|January|February|
| 2000 | 3000 |

Thnxs,,

The problem is my statement is not working..need help for the codes..

At first 02-31-2010 isn't a valid date perhaps this is the problem.

How you assign the dates to the fields? As they are utc dates you have to use the function date.to.utc().

i Want to equl my variable to the specific date for ex..
This variabl eis the ending inventory of the month,then i want my variable equal to my sql statement.


domain long UTCTime

UTCTime=inputstr.to.utc("1312010","%D(%1m%02d%04Y)","235959","%U(%02H%02m%02s)")


select whinr110.qhnd,whinr110.trdt
from whinr110
where whinr110.trdt <= :UTCTime
and whinr110.cwar={:whwmd215.cwar}
and whinr110.item={:whwmd215.item}
selectdo
jan=whinr110.qhnd / convertion
endselect


my layout look like this:

Ending inventory of the month:
January | February
3000 | 2000


can u give a sample codes for this??thnxs
and what domain that i must use in my layout for the UTCTime to see the value of my Variable??

U need to write only 1 SQL for entire year . Have a variable for month of record which in input var for report also . Use only one variable for 12 months as against 12 variables u have . In the report in aggregate condition , equate month variable to respective column month . This will give total of all months in separate column .

There is other method use arrays also if u r comfortable with arrays.