>Hello,
> In the following sql statement, I want to calculate total phone calls for each extension in a week. Can someone look at the sql statement and tell me why is the return value for each day is the same for each extension. For example, ext.# 224, the return value will be 200 calls for everyday, ext.# 500, the reurn value will be 10 calls for each day:
>
>select alltrim(f_name) + " " + alltrim(l_name) as staff, ext, DEPT, sum(iif(cdow(start)= "Monday",LENGTH,0)) AS MON,;
>sum(iif(cdow(start+1)= "Tuesday",LENGTH,0)) AS TUES, ;
>sum(iif(cdow(start+2)= "Wednesday",LENGTH,0)) AS WED, ;
>sum(iif(cdow(start+3)= "Thursday",LENGTH,0)) AS THU,;
>sum(iif(cdow(start+4)= "Friday",LENGTH,0)) AS FRI ;
>FROM STAFF, CALL_DET WHERE EXT == CALL_STN AND upper(alltrim(ACTION)) == "INCOMING CALL" GROUP BY EXT into cursor temp
>thanks
>sherry

I believce the problem is with the Group By. You really need to group by staff, ext and dept. The sequence of the group by items needs to be in the order to give the results you want. My guess would be Dept [least specific], Staff, Ext [most specific].

The next potential problem is the trimming of the names as STAFF. You field width definition will be determined by the values selected first. If the person's name is Pete Rose, your width could turn out to be 9. Not good for all the other names that will excced this width. YOu should pad the result to the width you want. Or, if you do not alltrim the L_NAME, your resulting field width will be at least the width of the L_NAME field.