asked on

SQL Average last 52 emtries

Ewangoya wrote this for me yesterday, and it works great, now I need to write another query that returns one number from this one. What I need is to be able to do is average the last 52 (52 most current from date, based on the PAY_PD_END_DATE, and return the Average of the AAE number for the newest 52.

The query:
SELECT PAY_PD_END_DT, COUNT(EMPL_ID) AS ACT, PAY_PD_CD,
(SELECT COUNT(EMPL_ID) AS Expr1
FROM DELTEK.EMPL
WHERE (ORIG_HIRE_DT < EARNINGS.PAY_PD_END_DT) AND (TERM_DT IS NULL OR
TERM_DT > EARNINGS.PAY_PD_END_DT)) AS AAE
FROM DELTEK.EMPL_EARNINGS AS EARNINGS
WHERE (S_PAYCHK_TYPE = 'R')
GROUP BY PAY_PD_END_DT, PAY_PD_CD
HAVING (PAY_PD_CD = 'W')
ORDER BY PAY_PD_END_DT

Returns Example:
PAY_PD_END_DT ACT PAY_PD_CD AAE
2000-04-02 44 W 97

Easwaran Paramasivam

select avg(col1)
from
(select top 52 col1 from table
order by date desc) a

sachinpatil10d

yep, EaswaranP is right
your query

select * from (
SELECT   top 52  PAY_PD_END_DT, COUNT(EMPL_ID) AS ACT, PAY_PD_CD,
                          (SELECT     COUNT(EMPL_ID) AS Expr1
                            FROM          DELTEK.EMPL
                            WHERE      (ORIG_HIRE_DT < EARNINGS.PAY_PD_END_DT) AND (TERM_DT IS NULL OR
                                                   TERM_DT > EARNINGS.PAY_PD_END_DT)) AS AAE
FROM         DELTEK.EMPL_EARNINGS AS EARNINGS
WHERE     (S_PAYCHK_TYPE = 'R')
GROUP BY PAY_PD_END_DT, PAY_PD_CD
HAVING      (PAY_PD_CD = 'W')
ORDER BY PAY_PD_END_DT desc)t

Open in new window

sachinpatil10d

select PAY_PD_END_DT, ACT, PAY_PD_CD, AVG(AAE)   from (
SELECT   top 52  PAY_PD_END_DT, COUNT(EMPL_ID) AS ACT, PAY_PD_CD,
                          (SELECT     COUNT(EMPL_ID) AS Expr1
                            FROM          DELTEK.EMPL
                            WHERE      (ORIG_HIRE_DT < EARNINGS.PAY_PD_END_DT) AND (TERM_DT IS NULL OR
                                                   TERM_DT > EARNINGS.PAY_PD_END_DT)) AS AAE
FROM         DELTEK.EMPL_EARNINGS AS EARNINGS
WHERE     (S_PAYCHK_TYPE = 'R')
GROUP BY PAY_PD_END_DT, PAY_PD_CD
HAVING      (PAY_PD_CD = 'W')
ORDER BY PAY_PD_END_DT desc)t
Group by PAY_PD_END_DT, ACT, PAY_PD_CD

Open in new window

mikeewalton

ASKER

I don't think you guys are quite understanding, I should only be returning one #, these queries actually return the same results I already have.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

First suggestion should work by the way, if you put your original query in the derived table labeled a http:#37004629 - I think the second poster was trying to say that, but forgot the AVG() function.

I like the ROW_NUMBER() OVER(ORDER BY PAY_PD_END_DT DESC) method more when needing to say get average by some other column, for example by PAY_PD_CD.

ROW_NUMBER() OVER(PARTITION BY PAY_PD_CD ORDER BY PAY_PD_END_DT DESC)

Otherwise, if just using a normal ORDER BY, the result should be consistent with SELECT TOP(x) ... ORDER BY col DESC.

Hope that helps!

mikeewalton

ASKER

@MWVISA1

You query works perfect, ran a manual average of the #'s and compared to the query result to double check.

Thank you for the assistance.