Find last time on promotion based on criteria

[anniecoops]

I work on database in chicken factory….involved in predicting stock, promotions etc. Please help, the following code picks up on records where the product is on promotion sometime in the future, i.e. not have any casesSold yet……..

id cust cases birds prod estPromoCase dateOfOrder
670 70260 0 0 436 22 1/07/2003
671 70260 0 0 436 22 12/07/2003
672 39000 0 0 371 52 06/07/2003
673 39000 0 0 371 52 07/07/2003
674 39000 0 0 371 52 08/07/2003
675 39000 0 0 371 52 09/07/2003
676 39000 0 0 371 52 10/07/2003

The view shows estimated promotional figures as ‘company estimate’. It then finds the other times this particular product was sold to a particular customer on that certain day and when on promotion. It shows the number of casesSold from the previous time on promotion as previous cases, see table at bottom of page. I am trying to only show the one most previous time product/customer/day on promotion i.i. first record from each 'set' of cust/prod/day. At present it shows all the previous times, again, see table.

SELECT *
FROM (SELECT MAX(dbo.tbOrder.dateOfOrder) AS promotionalDate, dbo.tbOrder.customer AS customerid, dbo.tbOrder.product AS productid, DATEPART(dw,
dbo.tbOrder.dateOfOrder) AS daysOfWeek, ISNULL( dbo.tbOrder.casesSold, 0) as casesSold, dbo.tbOrder.estPromoCases AS CompanyEstimate
FROM dbo.tbOrder INNER JOIN
dbo.tbProduct ON dbo.tbOrder.product = dbo.tbProduct.id
WHERE (dbo.tbOrder.estPromoCases > 0) AND ISNULL(dbo.tbOrder.casesSold, 0) = 0
GROUP BY dbo.tbOrder.customer, DATEPART(dw, dbo.tbOrder.dateOfOrder), dbo.tbOrder.casesSold, dbo.tbOrder.estPromoCases, dbo.tbOrder.product)
newpromo

INNER JOIN
(SELECT MAX(dateOfOrder) AS olddate, customer, product, DATEPART(dw, dateOfOrder) AS dayOfWeek, casesSold AS previousCases, casesSold * wholeBirds AS previousBirds
FROM dbo.tbOrder INNER JOIN
dbo.tbProduct ON dbo.tbOrder.product = dbo.tbProduct.id
WHERE estPromoCases > 0 AND casesSold > 0
GROUP BY customer, product, DATEPART(dw, dateOfOrder), casesSold, wholeBirds) lastpurchase
ON
newpromo.customerid = lastpurchase.customer AND
newpromo.productid = lastpurchase.product AND
newpromo.daysOfWeek = lastpurchase.dayOfWeek
ORDER BY promotionalDate ASC


Date Cust Prod Day Cases CoEst PrCases PrBird Old Date
07/07/03 10000 225 2 0 1 1 8 2-Jun-03*
07/07/03 10000 225 2 0 1 2 16 5-May-03
07/07/03 33000 333 5 0 4 5 40 28-Apr-03*
07/07/03 33000 333 5 0 4 9 72 17-Mar-03
07/07/03 33000 333 5 0 4 7 56 24-Mar-03
07/07/03 33000 333 5 0 4 6 48 24-Feb-03