Hi all,
I have two tables with the following structure
TABLE1 NAME==> EMPLYOEE_score
EMP_ID--Period_id-score
1------------1----------2
1------------3----------1
2------------1----------3
2------------2----------2
2------------3----------0
3 3 3

TABLE2 NAME==> Periods

Period_id
1
2
3

All I want is, I need to join these two tables such a way that my resultset should look like this(need to show all the Period_id for each emp_id and add the values of scores for each emp_id )

EMP_ID Period_id score
1 ----------1----------2
1 ---------- 2----------2
1 ---------- 3----------3
2 ---------- 1----------3
2 ---------- 2----------5
2 ----------3----------5
3 ---------- 1----------0
3 ---------- 2----------0
3 ---------- 3----------3

I was trying with outer joins but didnt get the expected result.

Thanks
krish.

WebFOCUS 7.6.5 UNIX EXCEL

This message has been edited. Last edited by: krish,

Are you joing single or multiple?

Hi Kfr104

I Tried with SQL and focus multiple joins but no luck...

Try left outer join and the LAST function to calculate the score in a compute:

NEW_SCORE/I4=IF EMP_ID NE LAST EMP_ID THEN SCORE ELSE LAST NEW_SCORE+SCORE;

HI GINNY,
thanks for your reply... i got your soution for accumulating the score value.

left outer join won't work for this case because in the periods table i dont have all the emp_id's of employee_score.

what is expectation is for every emplyee_id i need all the period_id (with the score eq 0 for missing record)

Thanks
krish

Krish,
You may want to look up the MATCH command. I'm confidant that you will accomplish what you need if you use the MATCH command to collect the data into one intermediate hold file. Once that is done, use Ginny's method of calculating the scores.

Hope this helps ...