Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Average last 52 emtries

Posted on 2011-10-20
7
Medium Priority
?
365 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:mikeewalton
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 37004629
select avg(col1)
from
(select top 52 col1 from table
order by date desc) a

0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37004768
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37004776
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Author Comment

by:mikeewalton
ID: 37005504
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
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 37005993
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 37006018
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
 
LVL 7

Author Comment

by:mikeewalton
ID: 37006042
@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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question