Re: Can I do this Query?

From: Sarah (skingswell_at_donotreply.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 17:23:08 +0100

By Order yes but not for each individual Item. The capacity will never exceed 1 for any item.
  "Andrew Madsen" <andrew.madsen@harley-davidson.com> wrote in message news:OS1BnjrXEHA.2844@TK2MSFTNGP12.phx.gbl...
  Will there be any instance that capacity will exceed 1?
  --
  Andrew C. Madsen
  Information Architect
  Harley-Davidson Motor Company
    "Sarah" <skingswell@donotreply.com> wrote in message news:utXgjPrXEHA.2868@TK2MSFTNGP09.phx.gbl...
    Andrew and Uri

    Your suggestions don't appear to work using the following example of data

    ID No 1 Order No 1 Line No 1 Capacity 0.75
    ID No 2 Order No 1 Line No 2 Capacity 0.75
    ID No 3 Order No 1 Line No 3 Capacity 0.34999999
     = 3 Boxes Required
    ID No 4 Order No 2 Line No 1 Capacity 1.0
    ID No 5 Order No 2 Line No 2 Capacity 0.25
     = 2 Boxes Required
    ID No 6 Order No 3 Line No 1 Capacity 0.30000001
    ID No 7 Order No 3 Line No 2 Capacity 0.20000000
     = 1 Box Required

    INSERT INTO Test VALUES (1,1,1,.75)
    INSERT INTO Test VALUES (2,1,2,.75)
    INSERT INTO Test VALUES (3,1,3,.35)
    INSERT INTO Test VALUES (4,2,1,1)
    INSERT INTO Test VALUES (5,2,2,.25)
    INSERT INTO Test VALUES (6,3,1,.30)
    INSERT INTO Test VALUES (7,3,2,.20)

    If you have any other suggestions, they are very welcome :-)

     

      "Andrew Madsen" <andrew.madsen@harley-davidson.com> wrote in message news:#tYbqhqXEHA.3888@TK2MSFTNGP10.phx.gbl...
      Why not use CEILING()? Such as:

      SELECT OrderID, CEILING(SUM(Capacity))as Quantity
      FROM #test
      GROUP BY OrderID

      --
      Andrew C. Madsen
      Information Architect
      Harley-Davidson Motor Company
        "Uri Dimant" <urid@iscar.co.il> wrote in message news:uX9VCVpXEHA.2364@TK2MSFTNGP12.phx.gbl...
        Sarah
        SELECT D.Orderid,MAX(CASE WHEN F <4 THEN D.line END) line
          FROM #Test JOIN
        (
         SELECT Orderid,MAX(Line)Line ,
           1000/CAST(Capacity/0.1*100 AS INT) AS F FROM #Test GROUP BY Orderid,Capacity
        ) AS D ON #Test.Orderid=D.Orderid AND #Test.Line=D.Line
        GROUP BY D.Orderid

          "Sarah" <skingswell@donotreply.com> wrote in message news:uJXJ2toXEHA.2664@TK2MSFTNGP09.phx.gbl...
          Thanks Uri.

          I can see where you are coming from now. I'll explain in some more detail because my original request may be a bit misleading.

          If you look at the lines for Order 1 there are 3 in total.

          Order No Line No Box Capacity
          1 1 .75
          1 2 .75
          1 3 .35

          For the first item on the order (Line 1) the box capacity is 75%. This basically means that the item will fill 75% of 1 box. The same applies to Line 2. Line 3 takes up 35% of a box. Therefore to ship this order I need to get 3 boxes (all boxes are the same size).

          But if the Order details were as follows

          Order No Line No Box Capacity
          1 1 .75
          1 2 .75
          1 3 .25

          I could fit all three products into 2 boxes. Therefore my query needs to display 2.

          Can I get the query to return this information?

          Thanks again for your help
            "Uri Dimant" <urid@iscar.co.il> wrote in message news:OZbV7doXEHA.2216@TK2MSFTNGP10.phx.gbl...
            Sarah
            SELECT D.Orderid,D.Line FROM #Test JOIN
            (
             SELECT Orderid,MAX(Line)Line FROM #Test GROUP BY Orderid
            ) AS D ON #Test.Orderid=D.Orderid AND #Test.Line=D.Line

              "Sarah" <skingswell@donotreply.com> wrote in message news:OHOviXoXEHA.1048@tk2msftngp13.phx.gbl...
              Thanks Uri for your suggestion only I don't appear to get the right results from the query. When I run the query I am getting

              Order 2 Capacity 0.25
              Order 1 Capacity 0.34999999

              Order 2 needs to return a value of 2 and Order 1 needs to return a value of 3. I am doing something wrong here

                "Uri Dimant" <urid@iscar.co.il> wrote in message news:uAfx7pnXEHA.2908@TK2MSFTNGP10.phx.gbl...
                Sarah
                CREATE TABLE #Test
                (
                 [id]INT NOT NULL PRIMARY KEY,
                 Orderid INT NOT NULL,
                 Line INT NOT NULL,
                 Capacity REAL
                )
                GO

                INSERT INTO #Test VALUES (1,1,1,.75)
                INSERT INTO #Test VALUES (2,1,2,.75)
                INSERT INTO #Test VALUES (3,1,3,.35)
                INSERT INTO #Test VALUES (4,2,1,1)
                INSERT INTO #Test VALUES (5,2,2,.25)

                SELECT D.Orderid,Capacity FROM #Test JOIN
                (
                 SELECT Orderid,MAX(Line)Line FROM #Test GROUP BY Orderid
                ) AS D ON #Test.Orderid=D.Orderid AND #Test.Line=D.Line

                  "Sarah" <skingswell@donotreply.com> wrote in message news:%23obihgnXEHA.3044@TK2MSFTNGP09.phx.gbl...
                  I need to create a query that returns the number of boxes required per customer order. The following is an example of what I am trying to achieve

                  Order No Line No Box Capacity
                  1 1 .75
                  1 2 .75
                  1 3 .35
                  2 1 1
                  2 2 .25

                  Order No 1 should return a required box number of 3. Whereby Order 2 would return a required box number of 2.

                  This query is driving me crazy. Can I do this?

                  Any help would be gratefully received.

                  Sarah

Relevant Pages