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
LVL 7
mikeewaltonAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
I would do something like this with SQL 2008:
(ROW_NUMBER() in CTE)
;WITH cte AS (
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, ROW_NUMBER() OVER(ORDER BY PAY_PD_END_DT DESC) RN
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')
)
SELECT AVG(AAE) AvgAAE
FROM cte
WHERE RN BETWEEN 1 AND 52
;

Open in new window

0
 
Easwaran ParamasivamCommented:
select avg(col1)
from
(select top 52 col1 from table
order by date desc) a

0
 
sachinpatil10dCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sachinpatil10dCommented:
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

0
 
mikeewaltonAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
mikeewaltonAuthor Commented:
@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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.