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