Summing up daily values to get a daily total?

[datanibbler]

Hi,

in principle I know what to do:

- I am dealing with personell data and I want to build a diagram based on the nr. of hrs. of overtime.

- I have a table where there is a value per day - the individual overtime for a certain emp_no on that day.

=> By summing that up until today without any timely restrictions, I have established that I can get a pretty accurate value.

- I just want to do this in the script now and have one cumulative value per day.

<=> Somehow, that does not work: I get only the individual day-values, never a total.

My code currently is this:

ÜS:

LOAD

     Datum_ÜS,

     Pers_Nr,

     IF(Pers_Nr=previous('Pers_Nr'), (Previous('ÜS_akt')+ÜS_akt), ÜS_akt) as ÜS_akt_summ,

     ÜS_akt as ÜS_ind_Vgl

RESIDENT Überstd;

(where Überstd is already sorted first  by emp_no (Pers_Nr) asc, then by date (Datum_ÜS) asc.)

Instead of a running total, I in the field labeled "ÜS_akt_summ", I get the same as in the field "ÜS_ind_Vgl" which tells me that the PEEK fct. is probybly never recognizing one emp_no as being exactly the same as in the record before.

Can anybody help me get this straight? It can only be some little thing that I'm doing wrong I guess?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

can anyone help me once more?

- I have now used an employee_masterdata table with an entry_date and a leaving_date

=> I have enlarged that so there is now one record per PN for every day inbetween.

- I have joined the PN and the date from that table with what I had before (overtime data).

=> There were then a lot of empty rows on the days that were not originally present in the overtime_table.

=> These I filled up using an an IF-clause:

     >>  IF(ISNULL(date)=-1, 0, date)  <<

- I then ordered the entire table by PN (asc.) and then by date (asc.) and used the RangeSum function like Rainer proposed.

Now, however, I get way to high values. There must be something wrong.

For illustration, this is the code I'm currently testing:

Überstd_akt:
LOAD
    (RIGHT(trim(datumn), 2) & '.' & MID(trim(datumn), 5, 2) & '.' & LEFT(trim(datumn), 4)) as Datum_ÜS,
    datumn as Datum_ÜS_orig,
    pnr as Pers_Nr5,
    num( ((LEFT(wertakt, (Len(wertakt)-2)))/100), '#.##0,00') as ÜS_akt
WHERE  (RIGHT(datumvon, 2) & '.' & MID(datumvon, 5, 2) & '.' & LEFT(datumvon, 4)) <= TODAY()
AND   (RIGHT(datumbis, 2) & '.' & MID(datumbis, 5, 2) & '.' & LEFT(datumbis, 4)) >= TODAY()
//  and   (RIGHT(datumn, 2) & '.' & MID(datumn, 5, 2) & '.' & LEFT(datumn, 4)) >= YearStart(TODAY())
;

SQL SELECT

[database query]

;

// Joining with a table where there is every date (between joining_date and leaving_date) for every employee

LEFT JOIN (Überstd_akt)

LOAD
    Pers_Nr as Pers_Nr5,
    Datum_HR as Datum_ÜS
Resident MA_Daten_tgl
;

// Then I filled up the NULL values created by that JOIN with 0

ÜS_akt:
LOAD
    Pers_Nr5,
    Datum_ÜS,
    IF(ISNULL(ÜS_akt)=-1, 0, ÜS_akt) as ÜS_ind
Resident Überstd_akt;

DROP TABLE Überstd_akt;

// This is where I was planning to use the PEEK function

ÜS_ges:
LOAD
    Pers_Nr5,
    Datum_ÜS,
    IF(Pers_Nr5=Peek('Pers_Nr5'), RangeSum(Peek(ÜS_sum), ÜS_ind), ÜS_ind) as ÜS_sum,
    ÜS_ind as ÜS_Vgl
Resident ÜS_akt
ORDER BY Pers_Nr5 asc, Datum_ÜS asc
;

Thanks a lot!

Best regards,

DataNibbler

P.S.: I looked at the table in the table_viewer and it is obvious that I get a cartesian product through the JOIN: Every date from my "enlarged_masterdata" table is joined to every date (within the same PN) in that time-values table. That way, instead of the 600k or 700k that I would expect, I get 32mio records.

Only I don't understand why, the dates are all derived in the same manner - they all come out of the database as a STRING and I rearrange them to get the german date format.