Hello,

I need the help of this user board.
I'm very new to BaaN IV. I want to create a new query about the table tdinv700.
Result of the query should be a report about the transcation datas for the different articles in which the amounts of every possible movement is totalized per month.
It should be like this:

Article movement amount date
12345 Sales order 5000 2022/01
12345 Sales oder 200 2022/02
15555 Sales order 300 2022/01
14444 Sales order 400 2022/02

With my already created query i'm getting the movement totalized per day and not per month.

My problem is the field tdinv700.trdt. I would like to change the format of this field or shorten the filed.

The code I tried for shorten this field is

Select
Left (tdinv700.trdt,6)......and so on.


When I'm trying to execute this query, I'm allways getting the error message that there is no table 'Left(tdinv700.trdt,6)'

What I have to do, that i can trim a field in baan, or better what I have to write in my SQL code to get a result like I wrote in my example.

Thanks in advance.

Is this a simple easy sql query? Or is this a new reporting session you are working?

Also going to delete the other post. Looks like they are the same. The first post requires a moderator to approve. Looks like we were all off or busy last couple of days.

Thanks for your answer. You are right, the other posts are the same. I didn't know that the first post has to be appoved and I was wondering what's wrong with my internet conection, and so I posted again and again. Sorry for that.

I would like to create a new reporting session.

I know you are new to baan but I am going to assume you know how to created a session and a report. There might be a better way to do this in 4c4, but I never looked after I got this working in 2000.

This is one way I solved this type problem - mine was for work orders. I created a session that the users gave me a date range for example 1/2021 to 12/2022. I would translate these to 1/1/2021 and my enddate would be like 12/31/2021. I would then essentially go thru the queries for each month - so I start with 1/1/2021 to 1/31/2021 for the records I needed. I would query and output the workcenter and hours for that month. Then I would increment 1 to 2 and query for February, so on and so fourth until the end of the range. You can checkout sprintf for how to get the month and years using something like date.f = val(sprintf$("%D(%m)",date.num())).

| some other examples.
year = sprintf$("%D(%Y)",somedate)
month = sprintf$("%D(%02m)",somedate)
weekn = sprintf$("%D(%02W)",somedate)

I actually let them pick how the report printed by year/month or year/wk. It is (or was) a lot more complicated that what I outlined.


function extern set_month_dates(domain tcyrno year.f,
domain tcweek month.f,
domain tcyrno year.t,
domain tcweek month.t)
{
domain tcyrno hold.year
domain tcweek hold.month

| Get start/stop dates for reporting range.
start.date = date.to.num(year.f,month.f,1)

| To get to to month add 1 to the input to month. This was
| you get the 1st of the next month and can query for
| less than the stop date.
hold.month = month.t + 1
hold.year = year.t
if(hold.month>12) then
hold.month = 1
hold.year = hold.year + 1
endif
stop.date = date.to.num(hold.year,hold.month,1)
}
| Below a couple of the sample queries where I would run it by month.
| Sum all the hours.
select sum(tihra100.hrea):demo.cap
from tihra100
where tihra100._index3 between {:begin.date} and
{:end.date}
and tihra100.cwoc inrange :sub.from and :sub.to
and tihra100.cwoc refers to tirou001
and tirou001.mnwc inrange :main.from and :main.to
selectdo
endselect
cnt.days = 0
| How many work days WERE there.
select count(tirou420.pcwt):cnt.days
from tirou400,tirou420
where tirou400.cwoc = "ZZZ"
and (tirou400.date >= :begin.date and tirou400.date<:end.date)
and tirou400.ctod refers to tirou420
and tirou420.pcwt > 0
selectdo
endselect


PS - the code comes from multiple routines in a library I created for a couple of other sessions that I needed to report sfc hours in different formats using different calculations.