×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Find last time on promotion based on criteria

Find last time on promotion based on criteria

Find last time on promotion based on criteria

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close