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 CONTAINSTABLE be made to match unconditionally?

Say that users searching a Books database are able to search by Author,
Title, and Publisher using three separate text boxes. They can choose to
leave any or all text boxes empty if they do not care to filter by that
particular item.
Say I have a query that looks similar to this:
b.* FROM Books b
INNER JOIN CONTAINSTABLE( Books, Author, @.author) authorRank ON b.BookId =
authorRank.[KEY]
INNER JOIN CONTAINSTABLE( Books, Title, @.title) titleRank ON ( b.BookId =
titleRank.[KEY] )
INNER JOIN CONTAINSTABLE( Books, Publisher, @.publisher) publisherRank ON (
b.BookId = publisherRank.[KEY] )
This works great if the user chooses to enter something in each of the three
text boxes. However, CONTAINSTABLE does not accept an empty string for the
search condition (nor does it accept *, %, or other wildcards except when
searching for a prefix). How can I get the query to unconditionally match
title and publisher if they leave title and publisher blank but type
something for author?
If there is no way to allow any title and publisher to be returned when the
user leaves those text boxes blank, I will need to write a query for if they
type all three text boxes, a query for if they type in none of the text
boxes, a query if they type in just the publisher but not the title or
author, a query... etc. Not a good solution.
How can I achieve the results I want without writing multiple queries?
Thank you.Hi, Greg
Try something like this (untested):
SELECT * FROM Books
WHERE (@.author IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
)) AND (@.title IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
)) AND (@.publisher IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
))
Razvan|||That doesn't let me use any of the ranking information provided by
CONTAINSTABLE. It may be workable. I may have found some sort of
sp_configure setting that will change the behavior of noise words. I'm not
sure about it yet.
"Razvan Socol" wrote:

> Hi, Greg
> Try something like this (untested):
> SELECT * FROM Books
> WHERE (@.author IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
> )) AND (@.title IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
> )) AND (@.publisher IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
> ))
> Razvan
>|||> That doesn't let me use any of the ranking information provided by
> CONTAINSTABLE.
In this case, you might want to use something like this (also
untested):
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookID=z.[KEY]
WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
Razvan|||If @.author is NULL (or blank), then CONTAINSTABLE will throw an error, which
is pretty much the entire problem here.
"Razvan Socol" wrote:

> In this case, you might want to use something like this (also
> untested):
> SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
> LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
> b.BookID=z.[KEY]
> WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
> AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
> AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
> Razvan
>|||> If @.author is NULL (or blank), then CONTAINSTABLE will throw an error [...]
Aha! I told you it was untested... :) I assumed that CONTAINSTABLE will
return an empty resultset when given a NULL search condition.
Obviously, I was wrong.
In this case, we can use a non-existent word instead of NULL, like
this:
SET @.title=ISNULL(@.title,'NotSpecified')
SET @.author=ISNULL(@.author,'NotSpecified')
SET @.publisher=ISNULL(@.publisher,'NotSpecifi
ed')
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookId=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookId=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookId=z.[KEY]
WHERE (@.author='NotSpecified' OR x.[KEY] IS NOT NULL)
AND (@.title='NotSpecified' OR y.[KEY] IS NOT NULL)
AND (@.publisher='NotSpecified' OR z.[KEY] IS NOT NULL)
If you think the word "NotSpecified" may appear in a title of a book,
you can change it with another inexisting word.
Razvan

Can connect to sql 7 but not sql 2000

I have three boxes: a web server running windows 2003, a database server
running windows 2000 and sql server 7 and another database server running
windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
replacement for the sql 7 box. The web server is in the DMZ while the
database servers are in our internal network.
The web server can get through our firewall to the sql 7 box with no issues.
However, when we try connecting from the web server to the sql 2000 box, we
keep getting 'Error establishing socket. Connection Refused' messages.
The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
also reviewed the TCP/IP settings suggested in other posts but have still
come up with nothing. Have I overlooked something else?
Check the "Server Network Configuration" to find out whether "TCP-IP"
protocol has been added.
"Mike Smith" wrote:

> I have three boxes: a web server running windows 2003, a database server
> running windows 2000 and sql server 7 and another database server running
> windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
> replacement for the sql 7 box. The web server is in the DMZ while the
> database servers are in our internal network.
> The web server can get through our firewall to the sql 7 box with no issues.
> However, when we try connecting from the web server to the sql 2000 box, we
> keep getting 'Error establishing socket. Connection Refused' messages.
> The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
> also reviewed the TCP/IP settings suggested in other posts but have still
> come up with nothing. Have I overlooked something else?
|||TCP-IP is the only protocol enabled.
"Jason Lu" wrote:
[vbcol=seagreen]
> Check the "Server Network Configuration" to find out whether "TCP-IP"
> protocol has been added.
> "Mike Smith" wrote:

Can connect to sql 7 but not sql 2000

I have three boxes: a web server running windows 2003, a database server
running Windows 2000 and sql server 7 and another database server running
windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
replacement for the sql 7 box. The web server is in the DMZ while the
database servers are in our internal network.
The web server can get through our firewall to the sql 7 box with no issues.
However, when we try connecting from the web server to the sql 2000 box, we
keep getting 'Error establishing socket. Connection Refused' messages.
The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
also reviewed the TCP/IP settings suggested in other posts but have still
come up with nothing. Have I overlooked something else?Check the "Server Network Configuration" to find out whether "TCP-IP"
protocol has been added.
"Mike Smith" wrote:

> I have three boxes: a web server running windows 2003, a database server
> running Windows 2000 and sql server 7 and another database server running
> windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
> replacement for the sql 7 box. The web server is in the DMZ while the
> database servers are in our internal network.
> The web server can get through our firewall to the sql 7 box with no issue
s.
> However, when we try connecting from the web server to the sql 2000 box, w
e
> keep getting 'Error establishing socket. Connection Refused' messages.
> The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
> also reviewed the TCP/IP settings suggested in other posts but have still
> come up with nothing. Have I overlooked something else?|||TCP-IP is the only protocol enabled.
"Jason Lu" wrote:
[vbcol=seagreen]
> Check the "Server Network Configuration" to find out whether "TCP-IP"
> protocol has been added.
> "Mike Smith" wrote:
>

Can connect to sql 7 but not sql 2000

I have three boxes: a web server running windows 2003, a database server
running windows 2000 and sql server 7 and another database server running
windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
replacement for the sql 7 box. The web server is in the DMZ while the
database servers are in our internal network.
The web server can get through our firewall to the sql 7 box with no issues.
However, when we try connecting from the web server to the sql 2000 box, we
keep getting 'Error establishing socket. Connection Refused' messages.
The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
also reviewed the TCP/IP settings suggested in other posts but have still
come up with nothing. Have I overlooked something else?Check the "Server Network Configuration" to find out whether "TCP-IP"
protocol has been added.
"Mike Smith" wrote:
> I have three boxes: a web server running windows 2003, a database server
> running windows 2000 and sql server 7 and another database server running
> windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
> replacement for the sql 7 box. The web server is in the DMZ while the
> database servers are in our internal network.
> The web server can get through our firewall to the sql 7 box with no issues.
> However, when we try connecting from the web server to the sql 2000 box, we
> keep getting 'Error establishing socket. Connection Refused' messages.
> The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
> also reviewed the TCP/IP settings suggested in other posts but have still
> come up with nothing. Have I overlooked something else?|||TCP-IP is the only protocol enabled.
"Jason Lu" wrote:
> Check the "Server Network Configuration" to find out whether "TCP-IP"
> protocol has been added.
> "Mike Smith" wrote:
> > I have three boxes: a web server running windows 2003, a database server
> > running windows 2000 and sql server 7 and another database server running
> > windows 2003 and sql server 2000 (with sp4). The sql 2000 box is a
> > replacement for the sql 7 box. The web server is in the DMZ while the
> > database servers are in our internal network.
> >
> > The web server can get through our firewall to the sql 7 box with no issues.
> > However, when we try connecting from the web server to the sql 2000 box, we
> > keep getting 'Error establishing socket. Connection Refused' messages.
> >
> > The firewall rules for the sql 7 and sql 2000 boxes are the same. I have
> > also reviewed the TCP/IP settings suggested in other posts but have still
> > come up with nothing. Have I overlooked something else?