Below are a couple of sql statements and the output followed by my
question...
Select mpMrs,CLSUPR,A1,A2,A3,A4,A5
From
(SELECT MPMRS, CLSUPR,
ROUND(AVG(NULLIF(MPOVSA,0)),0) AS A1,
ROUND(AVG(NULLIF(MPI5TMS,0)),0) AS A2,
ROUND(AVG(NULLIF(MP#ATTS,0)),0) AS A3,
ROUND(AVG(NULLIF(MP#INSTS,0)),0) As A4,
ROUND(AVG(NULLIF(MP#SCPS,0)),0) AS A5
FROM MRSSTATSPF
JOIN CSRCLVLPF ON MPMRS = CLCSR AND MPPGRP = CLLGRP
WHERE MPDATE >= '2008-04-14' AND MPDATE <= '2008-04-19'
AND MPPGRP = 'LN' AND MPDETL = 'D'
AND (NOT ( MPCLVL = 4 AND MPRVIA = ' ') )
AND (NOT ( MPCLVL = 5 AND MPRVIA = ' ') )
AND CLTITC >= 10 AND CLTITC <= 30
AND CLTERMDATE IS NULL
Group By mpMrs,CLSUPR) As avgList
ORDER BY MPMRS
output....
MPMRS CLSUPR A1
A2
BILLIEO MELISSAJ 4.00000000000000000000000000000
3.00000000000000000
BOBBIB MELISSAJ 3.00000000000000000000000000000
4.00000000000000000
ELIZABETHM MELISSAJ 4.00000000000000000000000000000
3.00000000000000000
ERNESTOP MELISSAJ 3.00000000000000000000000000000
5.00000000000000000
ESTELAR MELISSAJ 4.00000000000000000000000000000
4.00000000000000000
LISAK MELISSAJ 4.00000000000000000000000000000
4.00000000000000000
OMAD MELISSAJ 5.00000000000000000000000000000
3.00000000000000000
SANDYS MELISSAJ 4.00000000000000000000000000000
5.00000000000000000
Select mpMrs,MPDATE,CLSUPR,A1,A2,A3,A4,A5
From
(SELECT MPMRS, MPDATE, CLSUPR,
ROUND(AVG(NULLIF(MPOVSA,0)),0) AS A1,
ROUND(AVG(NULLIF(MPI5TMS,0)),0) AS A2,
ROUND(AVG(NULLIF(MP#ATTS,0)),0) AS A3,
ROUND(AVG(NULLIF(MP#INSTS,0)),0) As A4,
ROUND(AVG(NULLIF(MP#SCPS,0)),0) AS A5
FROM MRSSTATSPF
JOIN CSRCLVLPF ON MPMRS = CLCSR AND MPPGRP = CLLGRP
WHERE MPDATE >= '2008-04-14' AND MPDATE <= '2008-04-19'
AND MPPGRP = 'LN' AND MPDETL = 'D'
AND (NOT ( MPCLVL = 4 AND MPRVIA = ' ') )
AND (NOT ( MPCLVL = 5 AND MPRVIA = ' ') )
AND CLTITC >= 10 AND CLTITC <= 30
AND CLTERMDATE IS NULL
Group By mpMrs,MPDATE,CLSUPR) As avgList
ORDER BY MPMRS, MPDATE
output...
MPMRS MPDATE CLSUPR A1
A2
BILLIEO 04/14/08 MELISSAJ 5.00000000000000000000000000000
3.0000000
BILLIEO 04/16/08 MELISSAJ 4.00000000000000000000000000000
4.0000000
BILLIEO 04/17/08 MELISSAJ 4.00000000000000000000000000000
4.0000000
BILLIEO 04/18/08 MELISSAJ 3.00000000000000000000000000000
3.0000000
BOBBIB 04/14/08 MELISSAJ 3.00000000000000000000000000000
5.0000000
BOBBIB 04/15/08 MELISSAJ 3.00000000000000000000000000000
4.0000000
BOBBIB 04/16/08 MELISSAJ 3.00000000000000000000000000000
4.0000000
BOBBIB 04/17/08 MELISSAJ 2.00000000000000000000000000000
4.0000000
BOBBIB 04/18/08 MELISSAJ 3.00000000000000000000000000000
4.0000000
ELIZABETHM 04/14/08 MELISSAJ 5.00000000000000000000000000000
4.0000000
ELIZABETHM 04/15/08 MELISSAJ 5.00000000000000000000000000000
3.0000000
ELIZABETHM 04/17/08 MELISSAJ 3.00000000000000000000000000000
2.0000000
ELIZABETHM 04/18/08 MELISSAJ 4.00000000000000000000000000000
3.0000000
ERNESTOP 04/14/08 MELISSAJ 4.00000000000000000000000000000
5.0000000
ERNESTOP 04/15/08 MELISSAJ 3.00000000000000000000000000000
5.0000000
ERNESTOP 04/16/08 MELISSAJ 3.00000000000000000000000000000
5.0000000
How come in the first sql statement the A2 average for BILLIEO isn't
calculated to be 4? When you add the date in the second sql statement and
manually calculate the average, and round, you get 4...????
Thanks,
Jay Vaughn
Senior I5 Programmer/Analyst
RateWatch
(P) 251.209.8968
www.rate-watch.com
midrange.com
