View - Last 12 Times On Promo - Averages

[anniecoops]

Hi, I am setting up a database in a chicken factory. I need to find an average case count of the the last 12 times a product was sold to a particular customer on every day of week. The following works but takes 1 min 15 secs, please help me to simplify.

ta heaps,
x

SELECT custId, ProdId, wholeBirdSize, DATEPART(dw, dateOfOrder) AS dayOfWeek, COUNT(*) AS NoDUPS,ROUND(AVG([Case Count]), 0) AS [Ave Non Prom Case Count],ROUND(AVG(wholeBirds * [Case Count]), 0)
AS [Ave Non Prom Bird Count]

FROM (SELECT *

FROM (SELECT dbo.tbOrder.id, dbo.tbOrder.customer AS custId, dbo.tbOrder.product AS ProdId, dbo.tbOrder.dateOfOrder, DATEPART (dw,dbo.tbOrder.dateOfOrder) AS dayOfWeek, dbo.tbOrder.casesSold AS [Case Count], dbo.tbProduct.wholeBirds * dbo.tbOrder.casesSold AS [Bird Count]

FROM dbo.tbProduct INNER JOIN
dbo.tbOrder ON dbo.tbProduct.id = dbo.tbOrder. product INNER JOIN
dbo.tbCustomer ON dbo.tbOrder.customer = dbo.tbCustomer.id

WHERE (ISNULL(dbo.tbOrder.estPromoCases, '0') = 0)) t1

WHERE (id IN

(SELECT TOP 12 t2.id
FROM
(SELECT dbo.tbOrder.id, dbo.tbOrder.customer AS custId, dbo.tbOrder. product AS ProdId, dbo.tbOrder.dateOfOrder, DATEPART(dw,
dbo.tbOrder.dateOfOrder) AS dayOfWeek,
dbo.tbOrder.casesSold AS [Case Count]

FROM dbo.tbOrder

WHERE (ISNULL(dbo.tbOrder.estPromoCases, '0') = 0)) t2

WHERE t2.custId = t1.custId AND
t2.ProdId = t1.ProdId AND
t2.dayOfWeek = t1.dayOfWeek
ORDER BY dateOfOrder DESC))) Last12TimesNonPromo

GROUP BY scaleNumber, custId, customerName, ProdId,
productName, wholeBirdSize, DATEPART(dw, dateOfOrder),
type

HAVING (COUNT(*) > 0)

 

[cmmrfrds]

Since this is a complex query, you will probably get a faster response by posting on the Microsoft SQL Server: Programming Forum. There are a lot of SQL experts that monitor that Forum.