SQL to identify out of tolerance values

Question

I have a database table with the following structure -

Week_End      Sales 
2009-11-01    43223.43 
2009-11-08     4324.23 
2009-11-15    64343.23 
... 

What I want is a SQL statement that will take a start and end date, and identify all rows within that period where the sales figure is more than 3% above or below the average sales figure for that period. So, run against the above data, it would return -

Week_End       Sales 
2009-11-08     4324.23 

I am using SQL Server 2008, btw.

Answer 1

Something like this should work, replacing with your dates or parameters as necessary

SELECT [Week_End]
      ,[Sales]
  FROM [MyTable]
  WHERE [Week_End] BETWEEN '2009-12-01' AND '2009-12-31' AND 
  Notional NOT BETWEEN 
  .97 *
      ( SELECT AVG(Sales) 
        FROM [MyTable]
        WHERE [Week_End] BETWEEN '2009-12-01' AND '2009-12-31')
  AND
  1.03 *(SELECT AVG(Sales) 
        FROM [MyTable]
        WHERE [Week_End] BETWEEN '2009-12-01' AND '2009-12-31')

Answer 2

note: the average in your example is 37297, so all three values in the table end up being outside the tolerance.

;with Weeks as --narrows the range to the provided dates
(
    select * from YourTable 
    where Week_End between @StartDate and @EndDate
),
PercentOff as 
(
    select week_end, sales, abs((a.avg - sales)/a.avg) as pct
    from Weeks
    cross join (select avg(sales) as avg from Weeks) a
)
select * from PercentOff where pct >= .03

Answer 3

I think you can do it without the self-join using analytic functions. I don't have SQL Server 2008 on hand to test it out with, though. Try this:

SELECT *
FROM (SELECT [Week_End]
           , [Sales]
           , AVG([Sales]) OVER() AS [Avg_Sales]
      FROM [MyTable]
      WHERE [Week_End] BETWEEN '2009-12-01' AND '2009-12-31') AS s
WHERE ABS(([Sales] - [Avg_Sales])/[Avg_Sales]) >= 0.03