Can I index a table in View ?

I create a view based on table. The table (say tblA) has an index on column
A.
When I do a sql query like "select .. from vMyView as MyView,tblB
where tblB.colA = MyView.A", will it use the index on tblA column A , or do
I
need to (and can I) create an index on the view ?
CREATE view vMyView()
select a,b,c...from tblA --> tblA.a is indexed
Thank you very much.Look up "Indexed views" in Books online.
Note - This feature is only supported in Enterprise/Developer editions.
Immy
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:16F4C4B5-1006-4A73-9BDF-BF7C2CC36B80@.microsoft.com...
>I create a view based on table. The table (say tblA) has an index on column
>A.
> When I do a sql query like "select .. from vMyView as MyView,tblB
> where tblB.colA = MyView.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the view ?
> CREATE view vMyView()
> select a,b,c...from tblA --> tblA.a is indexed
> Thank you very much.|||The base table index will be used, unless you create an indexed view. See
Books Online for details on how to create indexed views.
Indexed views basically allow you to index only a subset of data.
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Not entirely true. From Books Online:
"
Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
"
ML
http://milambda.blogspot.com/|||It will use the base table index if it is useful to do so (based on many
factors, like the selectivity of the index and your search argument)
You can index the view, provided it meets the strict requirements for doing
so. You can do it in any version, but as noted only in Enterprise Edition
will it use the index transparently, other versions require you to use the
NOEXPAND keyword so he text of the view isn't brought into the plan creation
(so you manually force it to treat the view as a table, more or less)
So the main reason I piped up and said basically the same things that were
said before is this. Do you know how to tell if an index is being used
using Management Studio / Query Analyzer? When it comes to predicting what
index may or may not be used, it generally behooves you to try it out.
There are menu items on both for displaying estimated plans, and then actual
plans that were used to execute the query. So execute the query and look at
the plan, and fiddle about with it for a while and you will get the hang of
when an index will be used.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:16F4C4B5-1006-4A73-9BDF-BF7C2CC36B80@.microsoft.com...
>I create a view based on table. The table (say tblA) has an index on column
>A.
> When I do a sql query like "select .. from vMyView as MyView,tblB
> where tblB.colA = MyView.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the view ?
> CREATE view vMyView()
> select a,b,c...from tblA --> tblA.a is indexed
> Thank you very much.|||Thanks.
I guess I am worry that in a sql query like the following
"select .. from vMyView as MyView,tblB where tblB.colA = MyView.A", the
query will be slow if it does not use the base table index.
CREATE view vMyView()
select a,b,c...from tblA --> tblA.a is indexed
"ML" wrote:

> The base table index will be used, unless you create an indexed view. See
> Books Online for details on how to create indexed views.
> Indexed views basically allow you to index only a subset of data.
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||If you post more DDL (see here for details:
http://www.aspfaq.com/etiquette.asp?id=5006) , we can provide better help.
select .. from vMyView as MyView,tblB where tblB.colA = MyView.A
This may not be optimal. For one the old (deprecated) join syntax is used.
Compare the execution plan of your current query to the execution plan of
something like this:
select <columns>
from tblA
inner join tblB
on tblB.colA = tblA.colA
ML
http://milambda.blogspot.com/sql

Can I index a table in UDF ?

I create a UDF based on a table. The table (say tblA) has an index on column
A.
When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
where tblB.colA = MyProc.A", will it use the index on tblA column A , or do
I
need to (and can I) create an index on the UDF ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
Thank you very much.That query will not use the index on "tblA" - you need to create one on the
temp table returned by the UDF. You cannot create a regular index on table
variables though (a CREATE INDEX statement), but you can use PRIMARY KEY and
UNIQUE constraints:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL PRIMARY KEY
)
If the index can have non-unique records you can fake an index by adding an
identity column so that each record will be unique:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL
, meaningless_column int NOT NULL IDENTITY(1,1)
, PRIMARY KEY (somestring, meaningless_column)
)
Make sure to make the IDENTITY column last in the PK/UNIQUE definition so
the index is still useful.
KH
"Paul fpvt2" wrote:

> I create a UDF based on a table. The table (say tblA) has an index on colu
mn A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or d
o I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||What do you mean by "use the index"? It will not copy the index onto
@.table, if that's what you mean. The only indexes you can create on a table
variable are via PRIMARY KEY or UNIQUE constraints... if you're talking
about ordering, that's a different conversation altogether...
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.
If your function truly consists of a single INSERT... SELECT statement then
you should turn it into an inline function rather than a multi-statement
one. That way your query against the function will be more likely to benefit
from an index on the base table. An example version of an inline function is
given below. This looks subtly different from what you posted but in terms
of the way the function works the difference is very significant.
CREATE function dbo.udfMyProc()
RETURNS TABLE
AS
RETURN (SELECT a,b,c...FROM tblA)
GO
BTW, if the function doesn't have any parameters then why use a function at
all? You could use a view for that.
Hope this helps.
David Portas
SQL Server MVP
--|||Thank you everybody for your replies.
I tried the view and it works a lot faster, I will use it instead.
But, I have a question about inline function. The example that you posted
looks the same with the UDF that I posted (CREATE function dbo.udfMyProc() )
What is the difference between UDF and inline function ?
Thanks a lot.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||One more question about inline function and views.
I understand that the ORDER BY clause is invalid in views and inline
functions.
If I need to use ORDER BY clause in my query inside the views or inline
function, Is there a way around it ?
Thanks.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||> What is the difference between UDF and inline function ?
there are two types of UDFs:
Inline. Consists of only one query. Think of it as a view or "macro".
Multi-statement. Here you define a table variable and populate that table va
riable, and when
function code exist at run-time the data is selected from the variable, All
that work is overhead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
> Thank you everybody for your replies.
> I tried the view and it works a lot faster, I will use it instead.
> But, I have a question about inline function. The example that you posted
> looks the same with the UDF that I posted (CREATE function dbo.udfMyProc()
)
> What is the difference between UDF and inline function ?
> Thanks a lot.
>
> "David Portas" wrote:
>|||Thanks.
Is the following UDF considered inline (because it only has 1 query: select
a,b,c...) ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
"Tibor Karaszi" wrote:

> there are two types of UDFs:
> Inline. Consists of only one query. Think of it as a view or "macro".
> Multi-statement. Here you define a table variable and populate that table
variable, and when
> function code exist at run-time the data is selected from the variable, Al
l that work is overhead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
>|||No, that is a multi-statement UDF. An Inline is:
CREATE FUNCTION f(...)
RETURNS TABLE
AS
RETURN (SELECT ...)
For above, SQL Server doesn't have to populate a table variable and then ret
urn the result. SQL
Server can, and will "inline" above query in the outer query, just like it d
oes with a view. Or
think of it as a macro, if you wish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
> Thanks.
> Is the following UDF considered inline (because it only has 1 query: selec
t
> a,b,c...) ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> "Tibor Karaszi" wrote:
>|||Thanks.
In the sample query that you posted, does it mean that you return a table
that is populated with the select statement ?
"Tibor Karaszi" wrote:

> No, that is a multi-statement UDF. An Inline is:
> CREATE FUNCTION f(...)
> RETURNS TABLE
> AS
> RETURN (SELECT ...)
> For above, SQL Server doesn't have to populate a table variable and then r
eturn the result. SQL
> Server can, and will "inline" above query in the outer query, just like it
does with a view. Or
> think of it as a macro, if you wish.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
>

Can I group stored procedures some way

Hi folks!
I'm working on a report that requires about 20 SPs to retrieve data.
My code would be better if I could somehow create some object
(package?) that would have all my 20 SPs in it.
This way if I have 100 reports it will be easy to manage the SPs in the
database.
I thought I read several years ago there was a way to do this. Is there
still a way?
Thanks in advance.Bob wrote:
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in
> the database.
> I thought I read several years ago there was a way to do this. Is
> there still a way?
> Thanks in advance.
I'm about you're actually trying to do here. Are you talking
about packaging your procedures in order to create them on another
database? Can you give an example of what you need?
David Gugick
Imceda Software
www.imceda.com|||"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1109359684.432319.268490@.l41g2000cwc.googlegroups.com...
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in the
> database.
> I thought I read several years ago there was a way to do this. Is there
> still a way?
>
Are you refering to the stored procedure number?
CREATE PROC [ EDURE ] procedure_name [ ; number ]
. . .
;number
Is an optional integer used to group procedures of the same name so they can
be dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter
around procedure_name only.
This can be used to group procedures, but it's an old and rarely used
feature, and you run the risk of confusing people. I would probably just
use a common name prefix to sort and identify the related procedures.
David

Can I get ordinary users to create reports rather than admin...?

Can I get ordinary users to create reports rather than admin using a web browser interface?

If you mean with the Reporting Model, yes. Without Reporting Builder you will need Visual Studio.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for replying - It looks OK however the Reporting Builder program would have to be installed on the end users machines (meaning we would have to convince IT Departments to let them do this) and would have to use a DBA to set up the initial database models for each business. So I'm thinking this is probably not the best solution.

A reporting section is integrated within the current application that the user can configure. In the new application we didnt want to re-code this functionality and not to have a DBA to configure the reports and prefer not to install extra applications.

If you could think of any 3rd parties that are similar to Reporting Builder that could integrate with our application I would be really grateful? You mentioned Visual Studio - Will I be able to integrate it their?

Can I generate a SQL CREATE script for a table that exists in the db automatical

I'm trying to figure out what the easiest/best way to transfer just one table from one sql server to another. Any help would be appreciated. Thanks!you can use the query analyzer, select database --> user tables --> select correct table --> right click --> script object to new window as -->> create. The query analyzer will generate a script that will generate your table.|||Thanks for the quick reply!
However, I can't seem to find the "user tables-->select correct table" option in the query analyzer?
Thanks again!|||At the left side is the object browser. If you cannot see, push the F8 button. You see at the top your server and below the databases in that server. From that point on it's like the Windows Explorer. When you see the user tables you can select one and right click on it for the scripting.|||Is this a SQL 2000 thing? I'm on SQL 7.0|||don't know, never used sql 7. Something else you can try: in the enterprise manager, select the table you want to script. Right click on it, select all tasks and then generate script. A popup will appear and if you want you can set some preferences, but just pushing OK is sufficient. Hopefully this is in sql 7|||You can access this through enterprise manager by right-clicking on the table and selecting All Tasks, Generate SQL Script.

Note that this does not copy the actual data. To do that you will need to use DTS.

blindman

Can I filter the data on mining structure, mining model?

I perform data mining on all products and a specific product category.
Do I need to create 2 data source views, one for all products and the other one for the specific product category?
Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures.
I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures.
Is there any simple way to do it?

Thanks.
Joe.

Yes you'd need to use two data sources. However instead of going through the wizard twice and recreating all the contained models, you could use SQL Mgmt Studio to script the mining structure and its contained models, modify the name in the generated script and then run t. To do this, right-click on the mining structure in SQL Mgmt Studio and select "Script Mining Structure As"-> CREATE TO.

|||Thanks Raman.

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 develop on a different machine?

I have just installed reporting services on our web server and am able
to create report projects from there, but I don't generally create
visual studio projects on the web server. I use a development machine
that does not have IIS installed. Is there any way to create report
projects on my development machine and point it to the report server?
I can't find any information on this, all of the examples I find tell
me to open a new report project, but the template only exists on the
report server. Is there some client tool I need to install?When you installed on the web server you installed both server and client
tools. Normally you would not have VS installed on the production machine
and only install the server part. Although it can be handy to be developing
on a machine that has both installed it is not necessary. When you preview a
report in VS it is not using IIS. Just install the client tools from the RS
installation on a machine that has Visual Studio on it.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"malpous" <porterdn@.email.uc.edu> wrote in message
news:97b85c30.0411221031.6907a8e2@.posting.google.com...
> I have just installed reporting services on our web server and am able
> to create report projects from there, but I don't generally create
> visual studio projects on the web server. I use a development machine
> that does not have IIS installed. Is there any way to create report
> projects on my development machine and point it to the report server?
> I can't find any information on this, all of the examples I find tell
> me to open a new report project, but the template only exists on the
> report server. Is there some client tool I need to install?

Can I create table with constrains in SMO?

Hello Guys,
I m new to SMO thing. Just wonder if we can define data constrains in SMO for tables?
Cheers,
Elton

The simple answer is yes, anything you can do in SSMS you can do using SMO. Can you offer any specifics regarding what you want to do?

|||

I wrote a tutorial on how to create a table in SQL Server using SMO. This may get you started:

Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation

Regards,

Dave

Can I create table with constrains in SMO?

Hello Guys,

I m new to SMO thing. Just wonder if we can define data constrains in SMO for tables?

Cheers,

Elton

The simple answer is yes, anything you can do in SSMS you can do using SMO. Can you offer any specifics regarding what you want to do?

|||

I wrote a tutorial on how to create a table in SQL Server using SMO. This may get you started:

Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation

Regards,

Dave

Can I create subscriptions programatically?

Is that possible? if so please point me in the right direction.There is sample code in a MS KB here that does it. I also think it is a good basis for more research along the same avenue.
|||This article doesnt talk anything about reporting services|||Yes, you can create and manage subscriptions through the SOAP API.
Check MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_20ft.asp

-- Robert|||I was looking at the example on the MSDN, and on the link provided above, I was trying to reference system.web.services.protols, but it was not found.

I am trying to set up a dynamic subscription application so that users can run a certain report with certain parameters on a monthly basis, or off peak hours, and have the report emailed/placed on a network drive. It seems however, I am missing a key point to getting this application created, since I am unable to reference services.protocols...

Any help plz?

Can i create reports using VB Express and SQL Server Express?

Hi,

i ve installed visual basic express and sql server express. my question is can i create reports in my database application?

An update for SQL Server Express that includes a limited edition of Report Server is expected to become available in 2006. Its features are described here: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

Can I create Packages programatically

For example assume that there is a DTS That takes some SELLS data from an oracle database and sum the sells for each month.

I can create the package visually, but what about if I want to create it programatically. I want to make a software for Balance Scorecard in which the users dont need to know Integration Services. they should have a consoloe for asking where to get an Indicator value. and the program should create it programatically.Yes, look in books online under Integration Services programming. There's a good reference there.sql

Can I create OLAP Cubes without a staging area?

I have de normalised tables in the source for use in a star
schema.However the grain of the facts that I may need are different
from that in the source also the source database has got denormalised
database which has data according to the three diferent companies
where the data is generated...
Can i build views not move the data and create cubes...how do i deal
with Unassiged if I do so?
Does anyone have an Idea how to deal with unassigned values without
creating a fact table based just on views from the source?
Thanks in anticipation...
AjayYes you can certainly build cubes off relational sources, to handle
unassigned values, you could use the isnull function. If you do end up using
a fact table, you can create "unknown" values in your dim tables that still
link to the fact table.
Not clear if this is what you are asking or not..
Ray Higdon MCSE, MCDBA, CCNA
--
"Ajay Garg" <ajayz90@.hotmail.com> wrote in message
news:d9477327.0403170556.173c077c@.posting.google.com...
> I have de normalised tables in the source for use in a star
> schema.However the grain of the facts that I may need are different
> from that in the source also the source database has got denormalised
> database which has data according to the three diferent companies
> where the data is generated...
>
> Can i build views not move the data and create cubes...how do i deal
> with Unassiged if I do so?
> Does anyone have an Idea how to deal with unassigned values without
> creating a fact table based just on views from the source?
>
> Thanks in anticipation...
>
> Ajay

Can I create new rows by using spreadsheet with less columns?

I wondering if I can create new rows in a table that has 48 columns by impor
ting data in a spreadsheet that has only 4 columns?yes, assuming the 44 columns that won't have data allow nulls or have defaul
t values assigned to them.
joel wrote:

> I wondering if I can create new rows in a table that has 48 columns by importing d
ata in a spreadsheet that has only 4 columns?|||I did it using the DTS designer - worked great.
Thanks
Joel

Can I create Letter type reports within the Report Builder

Hi,

I need to create letter type reports using the report builder, is this possible?. I am trying to create a letter that embeds some variable fields, i.e,. name, address, contact number. I can't seem to find a way to create page headers, and embed report data fields within text. Is this not possible within the report builder? it seems that the only thing you can do within the report builder is add fields to their predesignated areas. I am being forced to use the report builder instead of the report designer in order to give the end user the ability to make modifications to the report/letter. This is very frustrating. not being able to allow the user to edit report designer files, and then having force them to use report builder which doesn't seem to support some simple features..

Any suggestions would be appreciated...

Sorry, the current version of Report Builder is very limited in its functionality and you can't do what you are trying to do.

There is an undocumented feature in Report Builder that may help. You can create a textbox and set its value to an RDL expression, by typing the RDL expression into the text area. This is very limited since you cannot create custom datasets to reference.

Can I create index on table variables?

I was not able to find any document on how to create
index on table variables. Please let me know if you are
able to do so.
Thanks,
RachanFrom http://www.aspfaq.com/2475:
You cannot explicitly add an index to a table variable, however you can
create a system index through a PRIMARY KEY CONSTRAINT, and you can add as
many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does
with them is another story. <G>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||This is a multi-part message in MIME format.
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You're not allowed to run CREATE INDEX against a table variable. However,
you can declare the variable with primary key and unique constraints:
declare @.MyTable table
(
id int primary key
, x char (1) not null
, z int not null unique
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
I was not able to find any document on how to create
index on table variables. Please let me know if you are
able to do so.
Thanks,
Rachan
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You're not allowed to run CREATE INDEX =against a table variable. However, you can declare the variable with primary =key and unique constraints:
declare @.MyTable =table
(
=id int primary key
, =x char (1) not null
, =z int not null =unique
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rachan Terrell" wrote =in message news:021601c3af95$66=dc5710$a301280a@.phx.gbl...I was not able to find any document on how to create index on table variables. Please let me know if you are able to do so.Thanks,Rachan

---

--=_NextPart_000_0147_01C3AF6C.A83E9BB0--|||Rachan,
Take a look at BOL for "table variable". You will see that you can indeed
create keys on table variables.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||All,
Thanks so much for all your helps.
Take Care,
Rachan
>--Original Message--
>Rachan,
>Take a look at BOL for "table variable". You will see
that you can indeed
>create keys on table variables.
>J.R.
>Largo SQL Tools
>The Finest Collection of SQL Tools Available
>http://www.largosqltools.com
>"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
>news:021601c3af95$66dc5710$a301280a@.phx.gbl...
>> I was not able to find any document on how to create
>> index on table variables. Please let me know if you
are
>> able to do so.
>> Thanks,
>> Rachan
>
>.
>

Can I create dynamic report parameters?

Hi all,

Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -

Parameter 1 - 'Select an Application' - List of applications that are available

Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.

Is this possible to do in SSRS?

Thanks,

Brian

Hi Brian,

Yes you can do that. Its called Cascaded parameters you need to write 2 store procedure. 1st one will be for "select an application" and 2nd one will be for "select a module" for the 2nd sp you need to pass the application id as a parameter and depending on the application Id modules will be fetched.

Once these SPs are done you need to set the report parameters make sure order of the parameters in the report parameter is correct.

Take a look at this example

http://msdn2.microsoft.com/en-us/library/aa337426.aspx

I hope this helps

Thanks

Ashwini

|||

Hello Brian,

Yes, you can do this. Take a look at 'Cascading Parameters' in Books Online.

Hope this helps.

Jarret

sql

can I create db on nfs-mounted drive?

I have a large NAS server that I want to use as a SANS server. I would like
to NFS mount the NAS to my sql server, create a database on it, then unattac
h it and reattach it to another sql server. Has anyone tried this before? do
es anyone have any other id
eas on how I can do something simular? I am trying to reduce my copy times b
etween my production servers and my delivery servers.
thank you in advance!!Below might be of some help...
http://support.microsoft.com/defaul...kb;en-us;304261
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"chenthorn" <anonymous@.discussions.microsoft.com> wrote in message
news:F118D86C-1D51-4052-86F3-B4541D730207@.microsoft.com...
> I have a large NAS server that I want to use as a SANS server. I would
like to NFS mount the NAS to my sql server, create a database on it, then
unattach it and reattach it to another sql server. Has anyone tried this
before? does anyone have any other ideas on how I can do something simular?
I am trying to reduce my copy times between my production servers and my
delivery servers.
> thank you in advance!!