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

Can I do this Query?

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
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
|||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
|||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
|||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
|||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
|||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
|||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
|||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
|||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

Can I determine insert order without an explicit field

I want to be able to find the differences between the before and after
values in a table as updates occur. I thought an easy way to do this
would be to create another table with an identical structure and then
use an update trigger to insert the deleted and inserted rows into
that alternate table. I know what order the rows are in the table
since I will put them in there but how can I, without a time value
column, know which one was inserted into the table first?You can't. It would be very easy to add a column, e.g., InsertDate, with a
default of CURRENT_TIMESTAMP. In your alternate table you wouldn't need a
default on it.
HTH
Vern Rabe
"Computer User" wrote:

> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?
>|||ordering is an aspect of data selection, so you need some sort of
ordering column to indicate time based data.
there's no such thing intrinsically in a sql table as a row number, so
you really don't know what order the rows are in the table.
why wouldn't you want a datetime stamp column?
if you care that data was changed, wouldn't you want to know when it
changed?
you'll probably also want an indicator for which row it was
[deleted/inserted]
Computer User wrote:
> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?|||On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:

>ordering is an aspect of data selection, so you need some sort of
>ordering column to indicate time based data.
>there's no such thing intrinsically in a sql table as a row number, so
>you really don't know what order the rows are in the table.
>
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.

>why wouldn't you want a datetime stamp column?
>if you care that data was changed, wouldn't you want to know when it
>changed?
>
In this instance, I don't care when the data was changed, only that it
was. A web application is supposed to send an email to an
administrator showing db modifications. Having a "before" row and an
"after" row would make this easy.

>you'll probably also want an indicator for which row it was
>[deleted/inserted]
>
If I knew the order I would know which row it was because I will
insert the deleted row before the inserted row.|||email notifications aren't necessarily terribly reliable.
I find it advisable to have a screen ( as well ) where you can see
notifications.
If you want to be sure of the order then I suggest writing to a log
file would be better than a table.
The order that data is in will not be useful otherwise.
I would recommend creating a table which has a bunch of fields for
before and the same again for after.
Plus your primary (unique ) key, a datestamp and change indicator (
Insert, Update, Delete ).
Write this with your trigger.
What I'd do with it then depends on how dynamic the data is.
I would hope that it's not very dynamic of all this is almost certainly
a complete waste of time.
Anyhow.
Stick a screen on the front of your app that the administrator only
sees with the changes from yesterday and today presented on it.
Use the timestamp to drive the selection.|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
It's in the database in some order, true. But there is no guarantee of
the order in which the server will retrieve rows, unless you impose an
ordering. It is *entirely* up to the server in what order it returns a
set of rows, and the order you receive them in may depend on server
version, patches, number of processors, *workload*, *data volumes*,
*indexes* and *statistics*. (the * ones are ones likely to change just
in the day-to-day use of a database). So if you need to retrieve data
in an order based on when it was inserted, you best record that
information.
In general, for small tables, your data will be returned to you in the
order determined by the clustered index (if it exists), or the order in
which data was inserted (if no clustered index). However, this is for
very small tables (I think as soon as you start using two pages, the
server can start reordering the rows as it sees fit, but not sure)
Damien|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
>
no, it's not. it's wherever the dbms put it. it could be in order, it
might not be, even for clustered indexes.
there is no intrinsic row number or insertion order. if you want one,
you have to add one.

> In this instance, I don't care when the data was changed, only that it
> was. A web application is supposed to send an email to an
> administrator showing db modifications. Having a "before" row and an
> "after" row would make this easy.
>
so what's the problem with adding a column that will help you?
"i don't care when the data was changed..." - famous last words :)

> If I knew the order I would know which row it was because I will
> insert the deleted row before the inserted row.
if you really do not care and can honestly say that you will never care
when the data was changed, then you could add an identity column to your
auditing table.
your better bet would be a single row with before and after values for
each column being audited.

Can I change Sort order configuration for a single column?

Hi,
I wanted to know, if there is a way to change the sort order (Collation) for a particular column. Assuming, sql server is configured for Case Insensitive, can I change the sort order to "Case sensitive" for a single column or object, retaining the entire sql server instance to Case insenstive
Thank
GYKTry COLLATE.
USE NORTHWIND
select * from orders
order by shipname COLLATE Latin1_General_CS_AS
There is a lot to read about in BO.
cheers,
Herbert Albert
"GYK" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E309852D-B260-441A-BB31-676AA92D2346@.microsoft.com...
> Hi,
> I wanted to know, if there is a way to change the sort order (Collation)
for a particular column. Assuming, sql server is configured for Case
Insensitive, can I change the sort order to "Case sensitive" for a single
column or object, retaining the entire sql server instance to Case
insenstive ?
> Thanks
> GYK|||Yes, you can do that using ALTER TABLE ... ALTER COLUMN and specify the
desired collation. Or specify collation in the query, as per Herbert's
example.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:E309852D-B260-441A-BB31-676AA92D2346@.microsoft.com...
> Hi,
> I wanted to know, if there is a way to change the sort order (Collation)
for a particular column. Assuming, sql server is configured for Case
Insensitive, can I change the sort order to "Case sensitive" for a single
column or object, retaining the entire sql server instance to Case
insenstive ?
> Thanks
> GYK|||Great...
Thanks a lot to you guys
GYK|||HI,
I have one more question. Can a collation be set for a user session? That is I logon with a particular collation, and it becomes invalid, once I logout... :-)
Thanks agai
GY|||Unfortunately not. Consider sending a request for such a feature to
sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:714D7DFF-E243-4B19-9545-F76BF40E475B@.microsoft.com...
> HI,
> I have one more question. Can a collation be set for a user session? That
is I logon with a particular collation, and it becomes invalid, once I
logout... :-)
> Thanks again
> GYK
>

Can Events be fired in a certain order?

I am getting my arms around Notification Services (NS). We need to generate notifications based on about 5 different subscriptions. The 5 events that feed the subscriptions will be SQL based and will read from a work table. I would like to populate that work table with a subset of our high volume transaction data, run the events, and then clear the data for the next run...say 5 minutes later. So my question is, how do I know when the last of the 5 events has fired so I can clean out the data. In other words, I'd like the 5 events to fire in a certain order and have the last event clean up. Is there a way to tell the events in what order they can fire? Or other suggestions on how to solve this? I read up on Chronicles but it wasn't clear to me if that would solve my problem.

Secondly, if my events and subscriptions created notifications for 5 different subscriptions, can they be combined into one e-mail to be delivered to the subscriber? In other words, there are several subscription events that may happen during the day for a subscriber. The subscriber only wants one e-mail at the end of the day. Will NS combine it?

There are several ways to recognize new events in the SQL Server Event Provider. A couple that I've used in the past is:

1) Use a postprocessing query to update a column in the data source indicating that the row has been processed.

2) Use a Chronicles table to track the datetime of the last query and only look for rows that have been added since then.

HTH...

Joe|||Thanks Joe. That does help.