David I do not have the sample databases, so I improvised. I think this may be what you are looking to do? I am fairly sure this is *not* the way you want to do this because it creates a triangular join, but it works.

--Create test table and populate with data

IF OBJECT_ID('tblOrders','u') IS NOT NULL

DROP TABLE tblorders

CREATE TABLE tblOrders

(

ID INT IDENTITY(1,1),

ORDER_DT SMALLDATETIME,

)

INSERT INTO tblOrders (ORDER_DT)

SELECT '10/5/2007' UNION ALL

SELECT '10/5/2007' UNION ALL

SELECT '10/5/2007' UNION ALL

SELECT '10/5/2007' UNION ALL

SELECT '10/4/2007' UNION ALL

SELECT '10/4/2007' UNION ALL

SELECT '10/4/2007' UNION ALL

SELECT '10/3/2007' UNION ALL

SELECT '10/2/2007' UNION ALL

SELECT '10/2/2007' UNION ALL

SELECT '10/2/2007' UNION ALL

SELECT '10/1/2007' UNION ALL

SELECT '10/3/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/6/2007' UNION ALL

SELECT '10/3/2007' UNION ALL

SELECT '10/2/2007' UNION ALL

SELECT '10/1/2007' UNION ALL

SELECT '10/6/2007'

--Create temp table to hold total # orders per day

IF OBJECT_ID('TempDB..#Temp','u') IS NOT NULL

DROP TABLE #Temp

CREATE TABLE #Temp

(

ID INT IDENTITY(1,1),

ORDER_DT SMALLDATETIME,

DT_Total FLOAT,

)

--Populate temp table

INSERT INTO #Temp

SELECT

ORDER_DT,

DT_Total = COUNT(order_dt)

FROM tblOrders

GROUP BY order_dt

ORDER BY order_dt DESC

--Find 3 day average

SELECT

t1.ORDER_DT,

t1.DT_Total,

(

SELECT

ROUND(SUM(t2.dt_total)/3,1)

FROM #temp t2

WHERE t2.order_dt = t1.order_dt

OR t2.order_dt = t1.order_dt - 1

OR t2.order_dt = t1.order_dt -2

) AS Total

FROM #Temp t1