Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I have a simple technical example of how to start?

I find difficulty in getting started with the service broker, and didnwt find a simple example.

I understand service broker is used for usynchronous jobs.

For example- I want to have a service that will perform SQL commands.

What are the necesary commands to create the queue,

to add the comand "Update MyTable Set MyField=1" to it,

and to get the command and perform it in another sesion?

Hi Geri!

Enclosed you'll find a complete T-SQL script that shows you how you can use Service Broker for asynchronous jobs. When you look through the script, you'll encounter the stored procedure "ProcessRequestMessages". This is the place where you can put your needed T-SQL code into.

If you have further questions just ask.

HTH

Klaus Aschenbrenner

http://www.csharp.at

http://www.csharp.at/blog

Code Snippet

USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Chapter3_HelloWorldSvc')
BEGIN
PRINT 'Dropping database ''Chapter3_HelloWorldSvc''';
DROP DATABASE Chapter3_HelloWorldSvc;
END
GO

CREATE DATABASE Chapter3_HelloWorldSvc
GO

USE Chapter3_HelloWorldSvc
GO

--*********************************************
--* Create the message type "RequestMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = NONE
GO

--*********************************************
--* Create the message type "ResponseMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = NONE
GO

--*********************************************
--* Show the created message types
--*********************************************
SELECT * FROM sys.service_message_types
GO

--************************************************
--* Changing the validation of the message types
--************************************************
ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

--************************************************
--* Create the contract "HelloWorldContract"
--************************************************
CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
(
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage] SENT BY INITIATOR,
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] SENT BY TARGET
)
GO

--*************************************************************
--* Getting some information about the newly created contract
--*************************************************************
SELECT
sc.name AS 'Contract',
mt.name AS 'Message type',
cm.is_sent_by_initiator,
cm.is_sent_by_target,
mt.validation
FROM sys.service_contract_message_usages cm
INNER JOIN sys.service_message_types mt ON cm.message_type_id = mt.message_type_id
INNER JOIN sys.service_contracts sc ON sc.service_contract_id = cm.service_contract_id
GO

--********************************************************
--* Create the queues "InitiatorQueue" and "TargetQueue"
--********************************************************
CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

--*************************************************************
--* Getting some information about the newly created queues
--*************************************************************
SELECT * FROM sys.service_queues
GO

--************************************************************
--* Create the queues "InitiatorService" and "TargetService"
--************************************************************
CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue
(
[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

--*************************************************************
--* Getting some information about the newly created services
--*************************************************************
SELECT
sv.name AS 'Service',
sc.name AS 'Contract'
FROM sys.services sv
INNER JOIN sys.service_contract_usages scu ON scu.service_id = sv.service_id
INNER JOIN sys.service_contracts sc ON sc.service_contract_id = scu.service_contract_id
GO

--********************************************************************
--* Sending a message from the InitiatorService to the TargetService
--********************************************************************
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.msg NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @.ch
FROM SERVICE [InitiatorService]
TO SERVICE 'TargetService'
ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
WITH ENCRYPTION = OFF;

SET @.msg =
'<HelloWorldRequest>
Klaus Aschenbrenner
</HelloWorldRequest>';

SEND ON CONVERSATION @.ch MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
(@.msg);
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--* View the sent message on the queue "TargetQueue"
--********************************************************************
SELECT * FROM TargetQueue
GO

--********************************************************************
--* View the created conversation endpoints
--********************************************************************
SELECT * FROM sys.conversation_endpoints
GO

--********************************************************************
--* Retrieve the sent message from the queue "TargetQueue"
--********************************************************************
DECLARE @.cg UNIQUEIDENTIFIER
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION;

RECEIVE TOP(1)
@.cg = conversation_group_id,
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue

PRINT 'Conversation group: ' + CAST(@.cg AS NVARCHAR(MAX))
PRINT 'Conversation handle: ' + CAST(@.ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @.messagetypename
PRINT 'Message body: ' + CAST(@.messagebody AS NVARCHAR(MAX))

COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--* Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @.cg UNIQUEIDENTIFIER
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION;

WAITFOR (
RECEIVE TOP (1)
@.cg = conversation_group_id,
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
PRINT 'Conversation group: ' + CAST(@.cg AS NVARCHAR(MAX))
PRINT 'Conversation handle: ' + CAST(@.ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @.messagetypename
PRINT 'Message body: ' + CAST(@.messagebody AS NVARCHAR(MAX))
END

COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--**************************************************
--* Create a table to store the processed messages
--**************************************************
CREATE TABLE ProcessedMessages
(
ID UNIQUEIDENTIFIER NOT NULL,
MessageBody XML NOT NULL,
ServiceName NVARCHAR(MAX) NOT NULL
)
GO

--*******************************************************************
--* Send a response message back to the service "InitiatorService"
--*******************************************************************
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
BEGIN
-- Store the received request message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName)
VALUES (NEWID(), @.messagebody, 'TargetService')

-- Construct the response message
SET @.responsemessage =
'<HelloWorldResponse>' +
@.messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
'</HelloWorldResponse>';

-- Send the response message back to the initiating service
SEND ON CONVERSATION @.ch MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
(@.responsemessage);

-- End the conversation on the target's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--* View the processed message in the table "ProcessedMessages"
--********************************************************************
SELECT * FROM ProcessedMessages
GO

SELECT * FROM InitiatorQueue
GO

--*******************************************************************
--* Service program for the service "InitiatorService"
--*******************************************************************
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM InitiatorQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
BEGIN
-- Store the received response) message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName)
VALUES (NEWID(), @.messagebody, 'InitiatorService')
END

IF (@.messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
-- End the conversation on the initiator's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--************************************************************************
--* A stored procedure used for internal activation on the target queue
--************************************************************************
CREATE PROCEDURE ProcessRequestMessages
AS
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
BEGIN
-- Store the received request message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @.messagebody, 'TargetService')

-- Construct the response message
SET @.responsemessage =
'<HelloWorldResponse>' +
@.messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
'</HelloWorldResponse>';

-- Send the response message back to the initiating service
SEND ON CONVERSATION @.ch
MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
(@.responsemessage);

-- End the conversation on the target's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--*****************************************************************
--* Enabling internal activation on the queue "TargetQueue"
--*****************************************************************
ALTER QUEUE TargetQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [ProcessRequestMessages],
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
)
GO

SELECT * FROM ProcessedMessages
GO

|||

I will try it as soon as possible.

Can I get it back the deleted records?

Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylThere are two options:
1=2E Do a Restore (Perhaps you have a point in time backup of the
database with transaction logs)
2=2E If you capsulated the query in a transaction (which I assume you
didn=B4t) Roll it back with ROLLBACK
HTH, Jens Suessmeyer.|||Daryl
yes, if you are lucky. you have to resort to back up. when did you take last
back up?
If you have taken any back up just prior to you can apply that. Wait I
understand that is not there. so you have to depend on 'Point in time '
Recovery.
BOL Has this: But remember. It will restore back to that particulat time. So
all the oprations after that time are be done manually again. Anyway take
full back up with no_truncate option before doing anything.
To restore to a point in time BOL has this.
Expand a server group, and then expand a server.
Expand Databases, right-click the database, point to All Tasks, and then
click Restore Database.
In Restore as database, type or select the name of the database to restore,
if different from the default.
Click Database.
In the First backup to restore list, click the backup set to restore.
In the Restore list, select the database backup and one or more transaction
logs to restore.
Click Point in time restore, and then type values for Date and Time.
Click the Options tab, and then click Leave database operational. No
additional transaction logs can be restored.
--
search and read for point-in-time;point of failure and related topics before
doing anything
--
Take Preventive measures like using
1) use delete trigger to not to rollback when
@.@.rowcount >1 or some records( if it is practical for you)
2) use trigger to store in history tables if data is so crucial.
Regards
R.D
"Daryl" wrote:

> Dear All,
> If I used a SQL command "Delete" to delete for example 1000 records, can I
> get it back after? It is because I executed the delete command and later
> found out I need some records back. Thank you for your help.
>
> Daryl
>
>

Can I get back the deleted records

Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
Daryl
Daryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Can I get back the deleted records

Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can I get back the deleted records

Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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 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 change SQL Server name?

First. Usually, SQL Server name is equal to computer name. Can I change SQL
Server name? So example: My computer name is ADIW, But SQL Server name is
ALI.
Can I do like it?
Second. Can/may one computer have 2 SQL Server? If Can, So how to make it?Hi,
Login into sql server using Query Analyzer and execute below commands,
sp_dropserver <old server name>
go
sp_addserver <newserver name>,local
After this stop and start sql server service
2. From SQL 2000 onwards you can install 16 instances of sql server in the
same machine. Each of the installation is independant of itself.
First installation will be default and the rest will be of named
installation. For Named instances the server name will be prefixed with a
Name.
This will allow all the sql server services to run in parellel.
Thanks
Hari
SQL Server MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eoXn92QVFHA.3444@.TK2MSFTNGP10.phx.gbl...
> First. Usually, SQL Server name is equal to computer name. Can I change
SQL
> Server name? So example: My computer name is ADIW, But SQL Server name is
> ALI.
> Can I do like it?
> Second. Can/may one computer have 2 SQL Server? If Can, So how to make it?
>|||1: http://www.karaszi.com/SQLServer/in...server_name.asp
2. Yes. Install several instances (run setup several times).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eoXn92QVFHA.3444@.TK2MSFTNGP10.phx.gbl...
> First. Usually, SQL Server name is equal to computer name. Can I change SQ
L
> Server name? So example: My computer name is ADIW, But SQL Server name is
> ALI.
> Can I do like it?
> Second. Can/may one computer have 2 SQL Server? If Can, So how to make it?
>sql

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.
4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
>
table[vbcol=seagreen]
do[vbcol=seagreen]
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"[vbcol=seagreen]
>sql

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.
Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>
|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>
|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> table
> do
>
|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:

>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>
|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>
|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com... [vbcol=seagreen]
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
table[vbcol=seagreen]
do
>
|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A
|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
data"
>

Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> > Can I change a field/column width in a script ? For example, I have a
> table
> > where field COL1 is a varchar(200). The table already has data in it. I
> > would like to increase the width of field COL1 to be varchar(300). Can I
> do
> > this in a script ? Thank you.
> >
> >
>|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>> Assuming no keys or constraints reference the column:
>> ALTER TABLE tablename
>> ALTER COLUMN Col1 VARCHAR(300)
>> You won't lose any data if you do this (but if you went the other way, you
>> could).
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
>> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
>> > Can I change a field/column width in a script ? For example, I have a
>> table
>> > where field COL1 is a varchar(200). The table already has data in it. I
>> > would like to increase the width of field COL1 to be varchar(300). Can I
>> do
>> > this in a script ? Thank you.
>> >
>> >
>>
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
> >Can I change a field/column width in a script ? For example, I have a
table
> >where field COL1 is a varchar(200). The table already has data in it. I
> >would like to increase the width of field COL1 to be varchar(300). Can I
do
> >this in a script ? Thank you.
> >
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>