Results 1 to 5 of 5

Thread: sql confusion

  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: sql confusion

    hey yall,
    I've gotten kinda confused about how I can achieve a result in my query.
    my tables:

    tblOrders(id, employeeID, boxes, factor, listprice, requestShipDate,confirmed)

    tblEmployee(employeeID, name)

    SELECT
    e.name AS FullName,
    DATEPART('m',o.requestedShipWeek) AS Month,
    SUM(o.boxes) AS AllBoxes,
    SUM(o.factor*o.listprice) AS SellPrice,
    AVG(o.factor*o.listprice) AS AvgSellPrice,
    AVG(o.factor) AS AvgFactor

    FROM tblWorkOrder AS wo
    LEFT JOIN tblEmployee AS e ON o.salesID=e.employeeID

    WHERE o.requestedShipWeek>=#8/14/2003#
    AND o.requestedShipWeek<=#12/20/2003#
    GROUP BY e.name, DATEPART('m',o.requestedShipWeek),

    So this query displays information about user performance monthly.
    Now, what I can't figure out is how to display total confirmed boxes. Right now, it just displays total boxes for every employee. Confirmed is a boolean. So if an order is confirmed, I need all the confirmed boxes added up for that employee.
    Does this involve a sub query?

    Thx in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    SELECT
    e.name AS FullName,
    DATEPART('m',o.requestedShipWeek) AS Month,
    SUM(o.boxes) AS AllBoxes,
    SUM(IIf(confirmed,o.boxes,0)) AS ConfirmedBoxes,
    SUM(o.factor*o.listprice) AS SellPrice,
    AVG(o.factor*o.listprice) AS AvgSellPrice,
    AVG(o.factor) AS AvgFactor

    FROM tblWorkOrder AS wo
    LEFT JOIN tblEmployee AS e ON o.salesID=e.employeeID

    WHERE o.requestedShipWeek>=#8/14/2003#
    AND o.requestedShipWeek<=#12/20/2003#
    GROUP BY e.name, DATEPART('m',o.requestedShipWeek),

    -----
    The "IIf" function will exclude boxes that aren't confirmed.

  3. #3
    Join Date
    Oct 2003
    Posts
    73

    Thumbs up

    thx! You know, i was actually really close to that yesterday! Guess thats how you learn. This turned out to be a very simple query. ha.

    BTW: do you know of any good sql tutorial sites that deal with more complex joins and such? Cause the books that i have only go through basic concepts and let you tackle the harder concepts yourself.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    No, sorry I don't really know of any good resource to recommend (other than dbforums )

  5. #5
    Join Date
    Oct 2003
    Posts
    73
    thats fine. Was planning on coming back here anyways

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •