Dear All,

How can I sum the Quantity based on Month and Year by using select statement. please see the attached

I want to sum the quantity and insert lines to other table contains Month, Year and Quantity Sum.

Please advice

Hi,

You will need to play with the date using variables. You will need to use functions like utc.to.date() ,date.to.num() to convert the numbers to dates. Have you tried anything?

the problem that we cannot use variables in WHERE,
like where lval(sprintf$("%D(%Y)", timrp001.fdat)) = year.

Please advice

Hi,

What you can do is, you can declare a string variable which will have the combination of the year no & month no. Now, when this variable changes, you can print the quantity with the month number and year number & the quantity.

e.g.

extern domain tcmcs.str10 combine


on.change.check(combine)
select tfacr200.*
from tfacr200
where tfacr200.docd inrange {:date.f} and {:date.t}
order by tfacr200.docd
selectdo
combine = str$(month_num) & str$(year_num)

if changed(combine) then
print data...
endif
endselect

I guess you know how to take out month_num & year_num from date.

Also,

check this:-

Thread (http://www.baanboard.com/baanboard/showthread.php?p=73165#poststop)

option 1

I would really prefer sending the date to the report as jan - 2013 or may be ( 01-2013) to string variable ... and send the data.

then have blank detail layout

then layout - after.change.date: aggregate the quantity

probably you don't want it that way...!!!!

option 2)
Main range of date
date.f = jan -1-2013 to date.t lets say july -23-2013

temp.date.f = jan -1-2013
temp.date.t = july -23-2013

while temp.date.f <= date.f
set temp.date.t = last day of the month of (temp.date.f)
if temp.date.t > date.f then
temp.date.t = .date.f
end if

select tfacr200.docn,sum(tfacr200.rate) : sum
from tfacr200
where tfacr200.docd inrange {:date.f} and {:date.t}
group tfacr200.docn
selectdo
print data...

endselect

temp.date.f = 1st of next month

endwhile

( please check the loops and conditions as this is brief overview of the logic. )

Regards,
vamsi.