Possible in SQL?

["Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

I've been asked to provide monthly wages for an employee for a two-year
period. Since I consider this to be a one-shot deal, SQL seemed like the
quickest way to grab the data.

The SQL I'm trying to use is

select a.emply,
year(date(substr(digits(a.dtechk),1,4)||'-'||
          substr(digits(a.dtechk),5,2)||'-'||
          substr(digits(a.dtechk),7,2))),
month(date(substr(digits(a.dtechk),1,4)||'-'||
           substr(digits(a.dtechk),5,2)||'-'||
           substr(digits(a.dtechk),7,2))),
sum( b.amtdol )
from pylookup/prtranh a
join pylookup/prtrand b on (a.refer=b.refer)
where a.emply='9360'
and a.dtechk between 20000101 and 20011231
and payid <= 3000
group by a.emply, a.dtechk
order by 1

I have also used the variation in the select with the same result.

select a.emply,
substr(digits(a.dtechk),1,4) as year,
substr(digits(a.dtechk),5,2) as month,
...


Received output:

EMPLOYEE        YEAR    MONTH    SUM ( B . AMTDOL )
NUMBER
9360           2,000        1                999.99
9360           2,000        1                999.99
9360           2,000        2                999.99
9360           2,000        2                999.99
9360           2,000        2                999.99
9360           2,000        3                999.99
...

Expected (desired) output:

EMPLOYEE        YEAR    MONTH    SUM ( B . AMTDOL )
NUMBER
9360           2,000        1              1,999.98
9360           2,000        2              1,999.98
9360           2,000        3              1,999.98
...


The problem is I get a row for each check date, not summarized for the month
and year. The check date is a number in the format YYYYMMDD, and not a true
date. Is this even possible in SQL, or must I resort to writing a program?

Thanks,
Loyd


(This is historical data from our "old" Software Plus system. We migrated to
ADP/HRizon payroll in 2001 and have given up the license for Software Plus.)

--
Loyd Goodbar
Programmer/Analyst
BorgWarner Incorporated
Air/Fluid Systems, Water Valley, MS