Here's one solution but it is quite lengthy. I'm sure there is a more efficient answer. So please show me your wisdom!

I used a different column though - RequiredDate instead of OrderDate.

Here's the query to check the result set against. And below that - my inefficient, but working solution.

--Check Table

USE Northwind

GO

SELECT RequiredDate, COUNT(RequiredDate)

FROM Shipping.Orders

WHERE RequiredDate Between '1998-05-01' AND '1998-06-11'

GROUP BY RequiredDate

ORDER BY 1 DESC

------- Inefficient Solution --------

DECLARE @counter int,

@counter2 int,

@date smalldatetime,

@scope int

SELECT @counter = 1,

@counter2 = 1

DECLARE @Time TABLE (

ID int IDENTITY (1,1),

timex smalldatetime

)

--Gather dates to report on

-- Note: not all dates have a RequiredDate

INSERT @Time

(timex)

SELECT DISTINCT RequiredDate

FROM Shipping.Orders

WHERE RequiredDate BETWEEN '1998-05-01' AND '1998-06-11'

ORDER BY 1 DESC

-- Select 1st Date

SELECT @date = (SELECT timex FROM @Time WHERE ID = 1)

-- Create Results table

DECLARE @Results TABLE (

t_RequiredDate smalldatetime,

t_count int

)

SELECT @scope = SCOPE_IDENTITY()

-- Set @scope to number of RequiredDates in @Time

WHILE @counter < @scope

BEGIN

INSERT @Results

SELECT timex,

(SELECT COUNT(RequiredDate)

FROM Shipping.Orders

WHERE RequiredDate IN (SELECT distinct TOP 3 RequiredDate -- Only want count of last three RequiredDates

FROM Shipping.Orders

WHERE RequiredDate

BETWEEN DATEADD(day, -10, @date) AND @date order by 1 desc) --Goes back 10 because not all dates have values

)

FROM @Time

WHERE ID = @counter2

SELECT @counter2 = @counter2 + 1

SELECT @date = (SELECT timex FROM @Time WHERE ID = @counter2)

SELECT @counter = @counter + 1

END

-- Retrieve Results

SELECT t_RequiredDate,

LTRIM(STR(CAST(t_count as float)/ 3, 4,2)) --obtain average

FROM @Results

ORDER BY 1 DESC