Here's a shorter version, but I know there is still room for improvement.

DECLARE @date smalldatetime,

@counter int

SELECT @date = '1998-06-11',

@counter = 1

DECLARE @Results TABLE (

t_RequiredDate smalldatetime,

t_count int

)

-- Select @counter to desired result set size

WHILE @counter < 10

BEGIN

WHILE (SELECT DISTINCT RequiredDate FROM Shipping.Orders WHERE RequiredDate = @date) IS NULL

BEGIN

SELECT @date = DATEADD(day, -1, @date)

END

INSERT @Results

SELECT RequiredDate,

(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 Shipping.Orders

WHERE RequiredDate = @date

SELECT @date = DATEADD(day, -1, @date)

SELECT @counter = @counter + 1

END

SELECT DISTINCT t_RequiredDate,

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

FROM @Results

ORDER BY 1 DESC