Dear All

I would like to sum Order Qty which group by item & delivery date from sales order line (tdsls401). However, this will involve timestamp which do NOT need to consider. Consider the following cases:-

Item Delivery Date QtyItemA 2006-12-01 12:53 500
ItemA 2006-12-01 12:53 300
ItemA 2006-12-01 16:53 400

If I use the following Baan SQL statement,
"Select item, ddta, sum(qoor) from tdsls400 group by item, ddta"
the result will become : -
ItemA 2006-12-01 12:53 800
ItemA 2006-12-01 16:53 400

This is not my desired result, I would like to have the following: -

Expected Results:-
ItemA 2006-12-01 1200

How can I do this ?

Thanks
Alvin

Hi,

You can write a condition inside the selectdo and then do te sum (without grouping). Here is a simple example:

select item, ddta, qoor
from tdsls400
where tdsls400.item = :curr.item | the wanted item
selectdo
if get.date.no.ts(tdsls400.ddta) = get.date.no.ts(curr.ddta) then
| get.date.no.tm function should get the date without the timestamp
| you should manually write it...
sum = sum + qoor
endif
endselect

This will give you the sum for one item (one day). You will need to run it on all items (with all dates)...

I hope it helps,

- George

Hi, George

Thanks for your solution firstly.

Your solution is work well. Thanks a lot.

However, it may have a little drawback that we need to read all the data from database server to application server, then do a loop by script, it may increase the network traff.

If we have a way to do a sum on database SQL level , it may be more better.

Thanks
Alvin

Hi,

You can use sum if you provided two dates (larger and smaller) than the one you want..

select item, ddta, sum(qoor)
from tdsls400
where ddta <= :to.date
and ddta >= :from.date
group by item, ddta
But still wou will need to run this on all dates.

- George