CODEWITHSUNDEEP
sqlsql-server-2005
user460114
user460114

Reputation: 1848

Query assistance please

Given the following table (much simplified for the purposes of this question):

id   perPeriod       actuals      createdDate
---------------------------------------------------------
1    14              22           2011-10-04 00:00:00.000
2    14              9            2011-10-04 00:00:00.000
3    14              3            2011-10-03 00:00:00.000
4    14              5            2011-10-03 00:00:00.000

I need a query that gives me the average daily "actuals" figure. Note, however, that there are TWO RECORDS PER DAY (often more), so I can't just do AVG(actuals).

Also, if the daily "actuals" average exceeds the daily "perPeriod" average, I want to take the perPeriod value instead of the "average" value. Thus, in the case of the first two records: The actuals average for 4th October is (22+9) / 2 = 15.5. And the perPeriod average for the same day is (14 + 14) / 2 = 14. Now, 15.5 is greater than 14, so the daily "actuals" average for that day should be the "perPeriod" average.

Hope that makes sense. Any pointers greatly appreciated.

EDIT

I need an overall daily average, not an average per date. As I said, I would love to just do AVG(actuals) on the entire table, but the complicating factor is that a particular day can occupy more than one row, which would skew the results.

Upvotes: 1

Views: 47

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

Is this what you want?

First, if the second payperiod average needed to be the average across a different grouping (It doesn't in this case), then you would need to use a subquery like this:

Select t.CreatedDate,  
    Case When Avg(actuals) < p.PayPeriodAvg 
         Then Avg(actuals) Else p.PayPeriodAvg End Average 
From table1 t Join 
    (Select CreatedDate, Avg(PayPeriod) PayPeriodAvg 
     From table1 
     Group By CreatedDate) as p 
  On p.CreatedDate = t.CreatedDate 
Group By t.CreatedDate, p.PayPeriodAvg

or, in this case, since the PayPeriod Average is grouped on the same thing, (CreatedDate) as the actuals average, you don't need a subquery, so even easier:

Select t.CreatedDate,  
    Case When Avg(actuals) < Avg(PayPeriod) 
         Then Avg(actuals) Else Avg(PayPeriod) End Average 
From table1 t 
Group By t.CreatedDate

with your sample data, both of these return

CreatedDate             Average
----------------------- -----------
2011-10-03 00:00:00.000 4
2011-10-04 00:00:00.000 14

Upvotes: 1

user596075
user596075

Reputation:

Try this out:

select createdDate, 
    case
        when AVG(actuals) > max(perPeriod) then max(perPeriod)
        else AVG(actuals)
    end
from SomeTestTable
group by createdDate

Upvotes: 0

Dylan Smith
Dylan Smith

Reputation: 22245

SELECT DAY(createdDate), MONTH(createdDate), YEAR(createdDate), MIN(AVG(actuals), MAX(perPeriod))
FROM MyTable 
GROUP BY Day(createdDate, MONTH(createdDate), YEAR(createdDate)

Upvotes: 0

Related Questions