Can I do this Query?

I need to create a query that returns the number of boxes required per custo
mer 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.
SarahSarah
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 custo
mer 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|||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@.TK2MS
FTNGP10.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 custo
mer 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|||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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||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@.T
K2MSFTNGP09.phx.gbl...
Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||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@.TK2MS
FTNGP12.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@.T
K2MSFTNGP09.phx.gbl...
Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||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:#t
YbqhqXEHA.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@.TK2MS
FTNGP12.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@.T
K2MSFTNGP09.phx.gbl...
Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||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@.T
K2MSFTNGP09.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:#t
YbqhqXEHA.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@.TK2MS
FTNGP12.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@.T
K2MSFTNGP09.phx.gbl...
Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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|||By Order yes but not for each individual Item. The capacity will never exce
ed 1 for any item.
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message news:OS
1BnjrXEHA.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@.T
K2MSFTNGP09.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:#t
YbqhqXEHA.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@.TK2MS
FTNGP12.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@.T
K2MSFTNGP09.phx.gbl...
Thanks Uri.
I can see where you are coming from now. I'll explain in some more detail b
ecause 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 basi
cally 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 ge
t 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 di
splay 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@.TK2MS
FTNGP10.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@.t
k2msftngp13.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@.TK2MS
FTNGP10.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 custo
mer 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