Get back calculated column from insert

I have the following in my Stored Procedure, where I am just adding a record
that will set the ApplicantID to the largest number + 1 or 1000 if null.
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
How do I find out what the ApplicantID is? This is similar to what an
Identity column would do, but I need to do it manually.
Thanks,
TomYour table should have a natural key defined in addition to the
surrogate ApplicantID. Use the natural key to retrieve the last ID. For
example, if this is for an online application you might make the email
address a key, therefore:
SET @.last_id =
(SELECT applicantid
FROM dbo.Applicant
WHERE email = @.email)
If you don't declare a natural key then you can't guarantee uniqueness
and integrity and therefore you can't reliably retrieve the Applicantid
after insert.
David Portas
SQL Server MVP
--|||Why do you need to "do it manually"? What do you think is going to happen
if two transactions hit this at exactly the same time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> I have the following in my Stored Procedure, where I am just adding a
record
> that will set the ApplicantID to the largest number + 1 or 1000 if null.
> INSERT INTO ftsolutions.dbo.Applicant
>
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> How do I find out what the ApplicantID is? This is similar to what an
> Identity column would do, but I need to do it manually.
> Thanks,
> Tom
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ef63V1fFFHA.392@.TK2MSFTNGP09.phx.gbl...
> Why do you need to "do it manually"? What do you think is going to happen
> if two transactions hit this at exactly the same time?
Because there are cases where I will need to add another ApplicantID in the
insert statement.
The create is:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
What would happen here is I could add a record that has no ApplicantID
asssociated with it and the insert would have take the max and add 1 and the
Applicant Unique would be 1. If there is already an ApplicantID out there,
I would have the same ApplicantID with ApplicantUnique =
max(ApplicantUnique)+1. Since the ApplicantID would need to be in this
Insert statement, it can't be an Identity.
Actually, we originally designed this to only allow one ApplicantID per
person without the PositionID, but now we need to allow multiple PositionIDs
per ApplicationID's, so we changed ApplicantID to be a normal bigint.
Tom

>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> record
> (ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,
Da
> tePosted)
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108760606.023053.73570@.l41g2000cwc.googlegroups.com...
> Your table should have a natural key defined in addition to the
> surrogate ApplicantID. Use the natural key to retrieve the last ID. For
> example, if this is for an online application you might make the email
> address a key, therefore:
> SET @.last_id =
> (SELECT applicantid
> FROM dbo.Applicant
> WHERE email = @.email)
> If you don't declare a natural key then you can't guarantee uniqueness
> and integrity and therefore you can't reliably retrieve the Applicantid
> after insert.
>
Actually, I do have key.
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
The insert statement that gets the ApplicantID is:
****************************************
************************************
***************
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
****************************************
************************************
**************
If it does exist, then I would use the same ApplicantID and increase the
ApplicantUnique by one
I used to have ApplicantID as an identity (as I mentioned in my other post)
and after the insert I would do a:
Select SCOPE_IDENTITY() as ApplicantID
to pass the ApplicantID back.
I could do the same thing by doing another select (inside a transaction to
make sure no one else increases it before I get it back) and then pass it
back, but I was hoping there was a way to get it from the insert/select
statement.
Thanks,
Tom

> --
> David Portas
> SQL Server MVP
> --
>|||> The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
An artificial key should not be required to make a candidate key. You
haven't even got First Normal Form. Normalize your schema (apparently a
subset of these columns belongs in another table) and then your problem
will be solved.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
I have a Client Table that is the ClientID only. I also have a Postions
table which is ClientID and PositionID (could have the same PositionID for
different Clients). The we have the Applicant table, where we have multiple
Positions for each Client and each Applicant could have more than one
instance of the same position (which is why I need the ApplicantUnique).
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||An artificial key is an arbitrary value, such as an incrementing count,
that is not a real attribute of the entity you are modelling. By
definition you are carrying redundant data if you don't have a Natural
key - ie. some subset of the attributes (real attributes) that is
unique.
It's far from clear to me what all these columns are but you seem to
have a high level of non-key dependencies in this table. Are you
familiar with the concept of Normalization? Normalization is a basic
design step in which you eliminate duplicated information by
decomposing data into appropriate tables based on functional
dependencies. If this process is performed correctly then it won't be
possible to end up with a structure such as you have. For example I
would not expect to see data about Applicants (actual names and email
addresses of people) in the same table as data about Applications
(positions applied for and who applied for them). Here's a wild guess,
not perfect, but all the tables have at least one natural key:
CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
NULL, lastname VARCHAR(30) NOT NULL)
CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
(client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
(client_id, client_job_code))
CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
REFERENCES Positions (client_id, client_job_code), application_date
DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
client_job_code, application_date))
Regarding PositionUnique. I don't see any other information associated
with multiple instances of a Position for an Applicant. If there isn't
any such information then you don't actually need to add a new row. For
completeness I've added Application_Date into the key.

> The problem is that this is just a temporary table which may not go
any
> farther.
Then you surely won't need another artifical key at all. The only use
for the INSERT you originally asked for is if new rows of data are
being inserted. If you need more help then I expect you can get better
advice if you tell us what your actual *objective* is rather than
propose half a solution to a problem you haven't really explained. The
following article describes the best way to post your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108778173.839320.171900@.z14g2000cwz.googlegroups.com...
> An artificial key is an arbitrary value, such as an incrementing count,
> that is not a real attribute of the entity you are modelling. By
> definition you are carrying redundant data if you don't have a Natural
> key - ie. some subset of the attributes (real attributes) that is
> unique.
> It's far from clear to me what all these columns are but you seem to
> have a high level of non-key dependencies in this table. Are you
> familiar with the concept of Normalization? Normalization is a basic
> design step in which you eliminate duplicated information by
> decomposing data into appropriate tables based on functional
> dependencies.
I understand Normalization and do a certain amount of it but not completely
(to the consternation of many).

>If this process is performed correctly then it won't be
> possible to end up with a structure such as you have.
That's true.

>For example I
> would not expect to see data about Applicants (actual names and email
> addresses of people) in the same table as data about Applications
> (positions applied for and who applied for them).
Not true here.
I would agree with you in other cases, however. I might have a persons data
in a separate table than the application table. But in our case, the person
may want to have different basic information for each position. He will
typically gear the information to the position. We need to keep a snapshot
of each application of each person. So in our case, it is just easier to
keep the personal information along with the application data. We do have a
separate table for each position (whose key is the PostitionID).

>Here's a wild guess,
> not perfect, but all the tables have at least one natural key:
> CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
> email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
> NULL, lastname VARCHAR(30) NOT NULL)
> CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
> VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
> (client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
> (client_id, client_job_code))
> CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
> Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
> VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
> REFERENCES Positions (client_id, client_job_code), application_date
> DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
> client_job_code, application_date))
Your table is pretty close to mine. If I were to separate my tables the you
have, I would also need to have the ClientID in the Applicants table also -
as we need to keep our applicants for our clients separate from each other.
I did make some changes, after looking at your last post about artificial
keys. I realized that I do have another table where I keep summary data
(JobApplicant) for each application and gave that a specific ID by making it
an identity field. There is one JobApplicant record for each application
filed. So I replace the ApplicantUnique with JobApplicant as that would
uniquify the record. The JobApplicant key "artificial key" would be the
JobID which is an identity field.
Also, a person can apply to multiple positions (so for the same ApplicantID
there could be multiple PositionIDs). Also, a person could apply to the
same position more than once. This was why I had the ApplicantUnique
before. But since there is only one JobApplicant record for each Applicant
and each position applied for - I can replace it with that.
Here is what I came up with and how it is currently on my system (there are
more fields in the tables, but these are the pertinent data).
CREATE TABLE [dbo].[Client] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TABLE [dbo].[Position] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PositionID] [int] IDENTITY (1, 1) NOT NULL ,
[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatePosted] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Position] WITH NOCHECK ADD
CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED
(
[ClientID],
[PositionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
ALTER TABLE [dbo].[Position] ADD
CONSTRAINT [FK_Position_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Applicant] WITH NOCHECK ADD
CONSTRAINT [DF_Applicant_PositionID] DEFAULT (0) FOR [PositionID],
CONSTRAINT [DF_Applicant_ApplicantUnique] DEFAULT (0) FOR [JobID],
CONSTRAINT [DF_Applicant_ResumeUnique] DEFAULT (0) FOR [ResumeUnique],
CONSTRAINT [PK_Applicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[JobApplicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Resume] [datetime] NULL ,
[Application] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobApplicant] WITH NOCHECK ADD
CONSTRAINT [DF_jobApplicant_Interviews] DEFAULT (0) FOR [Interviews],
CONSTRAINT [PK_jobApplicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[ApplicantPosition] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateApplied] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApplicantPosition] WITH NOCHECK ADD
CONSTRAINT [DF_ApplicantPosition_JobID] DEFAULT (0) FOR [JobID],
CONSTRAINT [PK_ApplicantPosition] PRIMARY KEY CLUSTERED
(
[ApplicantID],
[ClientID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Tom

> Regarding PositionUnique. I don't see any other information associated
> with multiple instances of a Position for an Applicant. If there isn't
> any such information then you don't actually need to add a new row. For
> completeness I've added Application_Date into the key.
>
> any
> Then you surely won't need another artifical key at all. The only use
> for the INSERT you originally asked for is if new rows of data are
> being inserted. If you need more help then I expect you can get better
> advice if you tell us what your actual *objective* is rather than
> propose half a solution to a problem you haven't really explained. The
> following article describes the best way to post your problem for the
> group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>sql

Get @@rowcount data from MSSQL using SqlDataSource with delete command

Hi

I'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.

In my SQL im using:

DECLARE @.table1CountintDELETE FROM Table1WHERE id = @.new_idSET @.table1Count=@.@.rowcountSELECT @.table1Count

I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @.table1Count back into my aspx page?

Thanks

use OUT parameter

CREATE PROC dbo.SP_DeleteID(@.new_id int, @.effRowCnt intOUT)

As

begin

DELETE FROM Table1WHERE id = @.new_id

SET @.effRowCnt=@.@.rowcount

end

|||

ok, but how do i reference the @.effRowCnt in the code behind page?

thanks

|||

If you are just trying to get a count of the number of rows deleted, an easier way to do it is to use the Deleted methodof the DataSource, something like:

ProtectedSub SQLDataSource1_Deleted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SQLDataSource1.Deleted

NumberofRowsDeleted = e.AffectedRows

EndSub

HTH

Generating unique id strings

Hi
I want to generate a unique record number in the following format
Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)

Could someone recommend how I go about generating this number?

Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?

... or should I just create a stored procedure to generate the number each time?

... or should I do something completely different?

Regards
John :confused:First, I'd recommend against doing this unless this code is required by the business process, i.e. recognized by the system users. Such codes are of little or no value to the application.

If all four of these values already exist in the table, then you could add a calculated column to your table that concatenates them to form the ID. These types of keys are often called "Superkeys", but with the availability of composite keys they are of little use these days and are often difficult to maintain.|||Thanks blindman, I'll take your advise and k.i.s.|||you could create a formula to concatenate the other fields and use it as the default value of a new field in the same row

I don't think I explained this very well

er

read about using formulas as default values in BOL

GW|||As a default value it would not update automatically if any of it's components change. That's the advantage of a calculated field.|||Apologies

U right of course blindman

I was'nt thinking

GW

Generating Sequence Number....

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......

Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1

Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)

DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)

SET NOCOUNT ON

INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)

SET @.i = 1
SET @.x = 1

DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC

OPEN theIckyCursor

FETCH NEXT FROM theIckyCursor INTO @.id, @.col1

WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END

UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id

SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END

CLOSE theIckyCursor
DEALLOCATE theIckyCursor

SELECT col1
, col2
FROM @.myTable

SET NOCOUNT OFF

You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

export your data to excel, use a macro to apply the sequence numbers

otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.

Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?

Can you make it more clear?

Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

I'll be obliged if any one let me know on the query that pdreyer has written.

Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.

Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque

UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)

SELECT ick, bat
FROM #foo

DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!

[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent

here is your solution:

select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1

rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --

select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1

rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is

rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............

Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.

Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty

*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)

I really learnt few things thru this thread.........

Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)

Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)

INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7

-- Check precondition
SELECT * FROM #Sample

-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)

DECLARE @.Index INT,
@.Col INT

SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1

UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1

DROP INDEX #Sample.IX_Sample

-- Check postcondition
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

Generating Sequence Number....

Hello folks,
I have a table where the records are like followings.

ID Value
-----
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

ID Value SeqId
----------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1

Thanks,
Rahul JhaI cannot remember if you need to declare a length for a varchar column when used in a convert expression. Well, you'll either have 1 character or the correct number, so an easy test.

Your question about the actual expression appears a little too easy, which makes me feel I might have misread it. From what I understand it is that you need to accomplish, here is the basic idea behind a solution:

select
(convert(nvarchar, ID) + ' ' + Value) as 'NewSeq'

Of course you will need to modify the formatting to meet your exact requirement, but it does nonetheless appear to be little more than a straight forward concatenation of two fields.

Regards,|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.


Thanks,
Rahul Jha|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.

Thanks,
Rahul Jha

If that is the case, then your question does not make sense from what you have explained. Either there is more to the the problem than has been mentioned or I've really misread it.

Your question asked how to concatenate a numeric and a non-numeric column to produce a single column. In any database, such a concatenation has the possibility to produce a non-numeric result. In your example, each and every row, will produce this kind of result.

Yet you now say that the destination column is of type INT, which makes your question impossible to answer.

Please do excuse me if I haven't understood, but the problem appears to be incredibly simple. Unless of course you want to store the concatenation of the numerical representation of the characters in the value column with the numeric value in the ID column. Could this be what you are trying to achieve?

My apologies, but I found it very difficult to ascertain your requirements other than what I assumed above, from your original question.

Regards,|||You are right Robert. Mistake was from my side only. Actually i should'nt have written (ID + Value). Anyways, what I am looking for is a new column named SeqId (INT) that will contain the data as per the column ID and Value. That means for a particular combination of ID & Value the value will be 1 and then for the same ID but different set of Value the data will be 2 and then for the next set of value for the same ID it will be 3 and so on........ And the same process will start for the next ID......

Thanks,
Rahul Jha|||No, you did not make a mistake at all. I just did not read the question completely, specifically the sample output that you provided. I therefore apologise for my above comments, they are not really relevant.|||You will need access to a temporary row identifier of some description so that you can distinguish between rows that have identical values in each of the columns that are to be used to determine the value for the SeqID column.

A common approach to these types of problems in Oracle is to use the Rownum function in conjunction with a defined ordering of the set. If you are using SQL Server 2005, you can use the new ROW_NUMBER function to achieve this purpose. If you're using SQL Server 2000, the easiest approach would be to temporarily insert an identity column named rowID or similar, and of course ensure that the values within are unique.

Once you have access to a rowID using one of the above methods, the SELECT query becomes a trivial self-join. The following code shows how to produce the result set that you described, using Oracle and the rownum function.

SQL> select * from myTable;

ID VALUE
---- --
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

13 rows selected.

select
t1.id,
t1.Value,
count(t2.rn) SeqID
from
(
select
id,
value,
rownum as rn
from
(select id, value from myTable order by id, value)
) t1

inner join
(
select
id,
value,
rownum as rn
from (select id, value from myTable order by id, value)
) t2
on t2.rn <= t1.rn
and t1.id = t2.id
and t1.value = t2.value

group by
t1.id,
t1.value,
t1.rn
order by
t1.id,
t1.value,
t1.rn;

This query will produce the following result set.

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

I do know that you can use a multiple table select query as the criteria for an update query, but I can't at this moment remember the exact syntax. However the Select query is what's important, and what I've supplied above will produce the set that you need.

Regards,|||ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

This is not the resultset that i am looking for...... I am looking for something like this.......

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 1
1 aa 1
1 bb 2
1 bb 2
1 bb 2
1 bb 2
1 cc 3
2 dd 1
2 dd 1
2 pp 2
3 qq 1
4 aa 1

I am again sorry for my putting across a wrong question...... I got to know this only when you gave me the answer......... Sorry again guys.......

Thanks,
Rahul Jha|||No problem. Nothing gets me more excited than a continuously changing business requirement. ;)

One moment whilst I excuse myself from talking to my wife , to rewrite the SQL. However, could you just confirm that you are now positive that what you supplied above is the result set that you want?|||:) :) :)

I am 100% sure of my requirement this time.

Thanks,
Rahul Jha|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.|||My DB is SQL SERVER 2K (Unfortunetly)

Thanks,
Rahul Jha|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.|||Actually it was that very approach that allowed me to save my previous firm millions of pounds in manual labour costs.

"Rob, we need to improve the efficiency of this process. Could you please take a look"

"Ok, I've concluded the best approach to take is to just delete the process. In other words if we do not run it, the execution time will be zero."

"Oh good heavens, why didn't I think of that. This is what we need in this department, people who can think outside of the box."

"Exactly! Now that will be 250 pounds please."|||Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.
It is, check the IO and TIME statistics! ;)|||George,

I don't believe that comment is logically valid.

To

Although OVER is more efficient..

You replied with

It is, check the IO and TIME statistics!

You appear to be agreeing with the previous post. Unless it's an earth shattering improvement, should we really worry to check the IO and Time statistics.

Alternatively,

Not in 2k of course

You replied with

It is, check the IO and TIME statistics!

I think somebody forgot to read the rest of the sentence while remembering the contents of an earlier post, which stated that the OVER() analytical function is not available in 2000 and hence the running time would be zero ;)

Regards,|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()

Just to clarify, the original poster did make a significant change to the initial requirements, subsequently serving to trivialize the problem, between the first example given and the most recent example that was supplied before you made reference to the analytical functions in SQL Server 2005. ;)

Regards,|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().

I would actually say that the first requirement is a little more involved than the second, for the reason that for the first requirement the poster asked that the SeqID column contain sequentially incrementing values for all rows, including those that contained identical values for each of their column values.

In contrast, the second requirement relaxed this point. The values for the SeqID column can be non-unique, and hence the comparison can be applied directly to the two static columns of the set.

Regards,|||I should have shortened the quote to

unless you count not running at all as efficient.

To make my repsonse make more sense :p

It is, check the IO and TIME statistics! ;)|||Touch

Indeed.|||I'm pretty sure it is trivial. I love this little addition to the SQL syntax (although full support in SS would be nice).

Row_number () OVER (partition by col_1, col_2 order by col_2)|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?|||Does anybody have any sweetener?

I didn't think SQL Server 2005 supported the Row_Number function as an analytical function that could be used in the way you've described in your post.

Had it not been available then... you can see my point.|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?

I'm not sure. I actually ceased to continue in actually solving the problem after the requirements changed again. Though had I known about the use or Row_Number with the OVER() clause then I most probably would have completed the solution.|||I am interested not as much with the potential solution to the OP (no offense, as you guys make it seem more exciting than being naked in a sack of kittens!), as I am with the requirement that Rahul is faced with in order to need to make the database change he seeks to make.

Often those project requirements are a great source of amus...errr...interest in his posts, though often we are not given visibility to them until the third or fourth requirement change undergone in one of these threads.|||Like the changing requirements in these threads is any different from the way things work in the "real world".

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).|||Like the changing requirements in these threads is any different from the way things work in the "real world".and the real world mirrors our little microcosm by bitching about them same as us :)

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).are you sure? I have a video on UTube! :)|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha|||I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Unfortunately my '(' key isn't working, so I'm unable to provide the answer.

Regards,|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha

Rahul,

If you you a tone of voice that is so demanding, would you not agree that people may be a little less inclined to offer assistance?

P.S. See my above post for the answer.

Regards,|||I am sorry Robert If I sounded rude in my sentence..... It was'nt intentional.....

Thanks,
Rahul Jha|||No problem. I understand your intentions, but other people may not.

So the problem has been resolved. That's good news.

Regards,|||But Robert I am unable to get the solution from what you have suggested in the 1st page.... What you have suggested was for the requirement that I have mentioned during the begining of the thread.

Thanks,
Rahul Jha|||Rahul,

Please see my earlier post, an extract of which is provided below.

I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Regards,|||An admirable effort, r123456...

one can lead a horse to water...|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha

Apologies Rahul, but this thread is now closed following two days of inactivity from the original poster, which gave sufficient reason to believe that the question had been resolved.

Regards,|||I was held up at some other frontier...... And in the mean time, gave a try to resolve this according to your suggestion..... That was the reason couldn't reply to the thread. And there was saturday and sunday between...

Thanks,
Rahul Jha|||OK, you've defeated me there with the point about Saturday and Sunday. Could you please post your modified version of the query I supplied earlier that you are using?|||ALTER TABLE TempTable ADD INDENTITY_COUNTER INT IDENTITY
GO

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(*) FROM TempTable TT
WHERE
TT.ID = TempTable.ID AND
TT.VALUE = TempTable.VALUE AND
TT.INDENTITY_COUNTER <= TempTable.INDENTITY_COUNTER
)

ALTER TABLE TempTable DROP COLUMN INDENTITY_COUNTER
GO

Thanks,
Rahul Jha|||I don't see why you need to create the IDENTITY_COUNTER column, the values for the SeqID column can be generated from the existing two columns that you have.

To correct your query you will need to make the following amendments to the query. Firstly you will need to remove the identity_counter column and indeed references to it from your query. Secondly you will need to modify your condition expressions to include the less than or greater than operator for one of your columns, and thirdly, the distinct keyword will need to be used with the count expression.|||Robert, I need the INDENTITY_COUNTER column to get the value for the SeqId column. That is the backbone of the query...... Even you have suggested the same in your query via ROWNUM keyword.

Thanks,
Rahul Jha|||No Rahul, that is not what I said. I indeed did make reference to the IDENTITY_COUNTER column as being one option to use in writing a query to meet your initial requirements, in the absence of the availability of SQL Server 2005's analytical functions. However if you will recall, you later changed your requirements such that the values for the SeqID column needn't be unique. This slight change removes the need for the identity_column. The requirement is now to populate the SeqID column with the count of distinct rows that have a value for the VALUE column which is less than or equal to the corresponding value of the current row.

Therefore to correct your query you will need to change one of your conditional operators to check for less than or equal to, and modify your count function to return only the count of distinct records as determined by the columns mentioned above.

Regards,|||Will it be in the lines of this........?

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(DISTINCT ID, VALUE) FROM TempTable
WHERE
[SOMECONDITION]
)
GO

Thanks,
Rahul Jha|||Be Back From Lunch.... :-)

Thanks,
Rahul Jha|||Oh, I give up!|||Did I say anything which I was not suppose to.....?

Thanks,
Rahul Jha|||No, not at all. The point is that you haven't really said enough for me to be able to help. I and other posters have supplied a comprehensive explanation of how to develop a solution. You just need to carefully read the posts and amend your current query to address the considerations that have been mentioned to reflect the change in your requirements.|||I am sorry again Robert. Couldn't able to figure out the solution. But anyways, thanks to you guys for the kind help......

Thanks,
Rahul Jha

Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
Ansari
Hi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>
|||... and along the same lines:
http://www.databasejournal.com/featu...0894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>
>
sql

Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
AnsariHi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>|||... and along the same lines:
http://www.databasejournal.com/features/mssql/article.php/10894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>>Hi gurus;
>>any body would like to tell how to generate row numbers with a qurery?
>>thanks in advance
>>Ansari
>>
>
>

Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
AnsariHi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>|||... and along the same lines:
http://www.databasejournal.com/feat...10894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>
>
>

Generating parameters from a list

I have a number of fiscal reports that I am writing. Currently, the
parameter for year is hard-coded. Ideally, I would like to be able to
generate the parameter list so that it offers years in descending from the
current year back 5 years or some defined period. In this manner, the
reports would not need to be updated each new year to modify the parameter
list. Is this possible, in v. 2000?One solution could be to create a dataset that pulls the year of a date field
in your report (group on the year) and use that as your query for the
parameter.
"Sarah" wrote:
> I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?|||This query will do it... Setup your parameter and set this query to be the
default value
select datename(yyyy,getdate())
+',' + datename(yyyy,dateadd(yy,-1,getdate()))
+',' + datename(yyyy,dateadd(yy,-2,getdate()))
+',' + datename(yyyy,dateadd(yy,-3,getdate()))
+',' + datename(yyyy,dateadd(yy,-4,getdate()))
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sarah" <Sarah@.discussions.microsoft.com> wrote in message
news:4F379DE9-9B9D-454A-9ABA-41CFB39C41C9@.microsoft.com...
>I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?

Generating numbers in SQL

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem?. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,
-sriramWhat are you doing this for? Uniqueness for records in a table? You can use an Identity Column for that...what is the business problem you are trying to solve?|||thanks for your reply.

My client wants to do it in the way i explained before. Is there a way??

-sriram|||You could do it a few ways, the incrementing number being the point of divergence. To get the current month you simply use the Month() function with GetDate()

ex:
SELECT Month(GetDate())

There are a number of ways to build the second part...

One would be to create an identity column and after the insert read the SCOPE_IDENTITY(), combine it with the Month(GetDate()) to get your number. The problem with this is that it relies on work after the insert which could be done through a stored proc or a trigger.

Another approach would be to select the top value from the column, trimming the month prefix and the creating your value by incrementing that number.

Do you need to stuff with zeros? Does the month need to be 1 or 01; does the second number need to be led by zeros?|||hi,

thanks for replying.

The second number needs to be led with zeros.

thanks,

-sriram|||You can use the SQL-Server Function Replicate() for this:

DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS int
SET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001
SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234

|||Here is some code that will help; I tried not to use UDFs because I dont know what version of SQL you are using. Regardless this will show you how to parse the old value out and generate a new one.

-- Assumes that the fixed length is 10

Declare @.OldValuechar(10)
Set@.OldValue= '0100000365'

Declare @.OldNumberint
Set@.OldNumber= Cast(Substring(@.OldValue,3, LEN(@.OldValue)-2) as int)

Declare @.MonthNamevarchar(10)
Set@.MonthName= DateName(M, Cast(Left(@.OldValue,2) + '/01/2000' as datetime))

-- Show the old values
select @.OldValue
select@.OldNumber
select @.MonthName

Declare @.MonthPrefixchar(2)
Set@.MonthPrefix= (Select Case WHEN Month(GetDate()) >= 10 THEN Cast(Month(GetDate()) as char(2)) ELSE '0' + Cast(Month(GetDate()) as char(1)) END)

Declare @.NewNumbervarchar(8)
Set@.NewNumber= Cast(@.OldNumber + 1 as varchar)

Declare @.Prefix varchar(7)
Set@.Prefix= ''

While Len(@.Prefix) < (7 - len(@.NewNumber))
Set @.Prefix = '0' + @.Prefix

Declare @.NewValuechar(10)
Set@.NewValue= @.MonthPrefix + @.Prefix + Cast(@.NewNumber as char)

-- Show the New Value
Select @.NewNumber
Select@.NewValue|||thanks,

i think it makes sense.

it should work..

anyway thanks for your reply.

-sriramsql

Generating membership number

Hi all,
I have a question about generating membership numbers on the fly when someone registers to my website.

Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...

SR357324J

This will then stay with them for the lifetime of their membership and be on their printed loyalty card.

My questions are...
1) Is there a 'good practice' for membership number format and generation?

2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.

I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.

Thanks for your contribution.I'm not sure about #1, but as for #2: You can still use the ID to retrieve records, even if you display the manually generated membership #.

If, however, you create your clustered index on the manually generated membership # field, you can make that field the clustered index (though this may hurt performance as relates to joins with other tables).|||I'd say let the db autogenerate an ID for you and you can append the 'SR' later..you could get into locking problems and duplicate ID's sooner or later..

hth

Generating complicate WHERE clause via parameters

Hi,
I have a large number of parameters defined which can be null and I have to
generate WHERE clause based on valid parameters, this makes things complicated
becuse I have to add "AND" to the WHERE clause depending on availability of
the parameters, the number of validation I have to do increases gradually
because the 10th parametrs should check for availability of 10 previous
parameters
before it can add "AND" infront of it. Is there any slick ways to handle
this problem?
Thank you.COALESCE will do.
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Thank you for you reply, I still do not see how CAOESECE can solve my problem.
My query looks like this.
SELECT column1, column2 FROM table1
WHERE
column3 = val1
AND
column4 = val2
with out val1 parameter supplied it should be:
SELECT column1, column2 FROM table1
WHERE
column4 = val2
So printing "AND" is the problem and only way to do it for me right now is
to check
if val1 was supplied or not.
can COALESCE help in this situation?
Thank you.
"Bing Bing Yu" wrote:
> COALESCE will do.
>
> "JC" wrote:
> > Hi,
> >
> > I have a large number of parameters defined which can be null and I have to
> > generate WHERE clause based on valid parameters, this makes things complicated
> > becuse I have to add "AND" to the WHERE clause depending on availability of
> > the parameters, the number of validation I have to do increases gradually
> > because the 10th parametrs should check for availability of 10 previous
> > parameters
> > before it can add "AND" infront of it. Is there any slick ways to handle
> > this problem?
> >
> > Thank you.|||WHERE column3 = COALESCE(val1, column3)
AND column4 = COALESCE(val2, column4)
"JC" wrote:
> Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> My query looks like this.
> SELECT column1, column2 FROM table1
> WHERE
> column3 = val1
> AND
> column4 = val2
> with out val1 parameter supplied it should be:
> SELECT column1, column2 FROM table1
> WHERE
> column4 = val2
> So printing "AND" is the problem and only way to do it for me right now is
> to check
> if val1 was supplied or not.
> can COALESCE help in this situation?
> Thank you.
>
> "Bing Bing Yu" wrote:
> >
> > COALESCE will do.
> >
> >
> > "JC" wrote:
> >
> > > Hi,
> > >
> > > I have a large number of parameters defined which can be null and I have to
> > > generate WHERE clause based on valid parameters, this makes things complicated
> > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > the parameters, the number of validation I have to do increases gradually
> > > because the 10th parametrs should check for availability of 10 previous
> > > parameters
> > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > this problem?
> > >
> > > Thank you.|||I eneded up doing it like this (using AdventureWorks) can this be enhanced?
(I have credited you by the way, thank you)
="DECLARE @.ContactID INT, " &
"@.FirstName NVARCHAR(50) " &
Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
" SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
"WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName =COALESCE(@.FirstName, FirstName)"
"Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
>
> WHERE column3 = COALESCE(val1, column3)
> AND column4 = COALESCE(val2, column4)
>
> "JC" wrote:
> >
> > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> >
> > My query looks like this.
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column3 = val1
> > AND
> > column4 = val2
> >
> > with out val1 parameter supplied it should be:
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column4 = val2
> > So printing "AND" is the problem and only way to do it for me right now is
> > to check
> > if val1 was supplied or not.
> >
> > can COALESCE help in this situation?
> >
> > Thank you.
> >
> >
> > "Bing Bing Yu" wrote:
> >
> > >
> > > COALESCE will do.
> > >
> > >
> > > "JC" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a large number of parameters defined which can be null and I have to
> > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > the parameters, the number of validation I have to do increases gradually
> > > > because the 10th parametrs should check for availability of 10 previous
> > > > parameters
> > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > this problem?
> > > >
> > > > Thank you.|||Hi,
I think you need to build the query something like this.
declare @.str as nvarchar(1000)
set @.str = ''
set @.str = @.str + "select * from "...
if @.val is null
set @.str = @.str + "val = .."
exec sp_executesql @.str
you can use if conditions where ever required.
Amarnath
"JC" wrote:
> I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> (I have credited you by the way, thank you)
> ="DECLARE @.ContactID INT, " &
> "@.FirstName NVARCHAR(50) " &
> Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => COALESCE(@.FirstName, FirstName)"
>
> "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> >
> > WHERE column3 = COALESCE(val1, column3)
> > AND column4 = COALESCE(val2, column4)
> >
> >
> > "JC" wrote:
> >
> > >
> > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > >
> > > My query looks like this.
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column3 = val1
> > > AND
> > > column4 = val2
> > >
> > > with out val1 parameter supplied it should be:
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column4 = val2
> > > So printing "AND" is the problem and only way to do it for me right now is
> > > to check
> > > if val1 was supplied or not.
> > >
> > > can COALESCE help in this situation?
> > >
> > > Thank you.
> > >
> > >
> > > "Bing Bing Yu" wrote:
> > >
> > > >
> > > > COALESCE will do.
> > > >
> > > >
> > > > "JC" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a large number of parameters defined which can be null and I have to
> > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > the parameters, the number of validation I have to do increases gradually
> > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > parameters
> > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > this problem?
> > > > >
> > > > > Thank you.|||Well, I'm pretty sure the dynamic query works just fine. One concern would
be since it's dynamic, SQL server won't be able to optimize the query.
"Amarnath" wrote:
> Hi,
> I think you need to build the query something like this.
> declare @.str as nvarchar(1000)
> set @.str = ''
> set @.str = @.str + "select * from "...
> if @.val is null
> set @.str = @.str + "val = .."
> exec sp_executesql @.str
> you can use if conditions where ever required.
> Amarnath
>
> "JC" wrote:
> >
> > I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> > (I have credited you by the way, thank you)
> >
> > ="DECLARE @.ContactID INT, " &
> > "@.FirstName NVARCHAR(50) " &
> > Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> > Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> > Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> > Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> > " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> > "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => > COALESCE(@.FirstName, FirstName)"
> >
> >
> >
> > "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> > >
> > >
> > > WHERE column3 = COALESCE(val1, column3)
> > > AND column4 = COALESCE(val2, column4)
> > >
> > >
> > > "JC" wrote:
> > >
> > > >
> > > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > > >
> > > > My query looks like this.
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column3 = val1
> > > > AND
> > > > column4 = val2
> > > >
> > > > with out val1 parameter supplied it should be:
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column4 = val2
> > > > So printing "AND" is the problem and only way to do it for me right now is
> > > > to check
> > > > if val1 was supplied or not.
> > > >
> > > > can COALESCE help in this situation?
> > > >
> > > > Thank you.
> > > >
> > > >
> > > > "Bing Bing Yu" wrote:
> > > >
> > > > >
> > > > > COALESCE will do.
> > > > >
> > > > >
> > > > > "JC" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a large number of parameters defined which can be null and I have to
> > > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > > the parameters, the number of validation I have to do increases gradually
> > > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > > parameters
> > > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > > this problem?
> > > > > >
> > > > > > Thank you.|||One easy way would be to use 1=1 and
'Select col1 from table 1 where 1=1 and' + @.str_where
@. str_where can be populated from the parameters you accept in
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Although you can do dynamic SQL I find this technique easier:
select somefields from sometable where (somefield = @.Param1 or @.Param1 ='All') and (anotherfield = @.Param2 or @.Param1 = 'All')
Note that if a number field you can check for a number that doesn't exist in
your database whether it is -1 or -99999 or whatever as long as you know it
doesn't exist in real life.
I prefer to have an All selection, however, you can use Null or Empty string
as well.
select somefields from sometable where (somefield = @.Param1 or
isnull(@.Param1)) and (anotherfield = @.Param2 or isnull(@.Param1))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunil" <Sunil@.discussions.microsoft.com> wrote in message
news:2666D2EB-03EA-47CF-A958-52EC722567FC@.microsoft.com...
> One easy way would be to use 1=1 and
> 'Select col1 from table 1 where 1=1 and' + @.str_where
> @. str_where can be populated from the parameters you accept in
>
> "JC" wrote:
>> Hi,
>> I have a large number of parameters defined which can be null and I have
>> to
>> generate WHERE clause based on valid parameters, this makes things
>> complicated
>> becuse I have to add "AND" to the WHERE clause depending on availability
>> of
>> the parameters, the number of validation I have to do increases gradually
>> because the 10th parametrs should check for availability of 10 previous
>> parameters
>> before it can add "AND" infront of it. Is there any slick ways to handle
>> this problem?
>> Thank you.

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

Generating a daily statistic report SQL

Hi,

I Have a table below.

Query

PKEY id int

name varchar(128)

date_add DateTime

What is the SQL statement to get the number of query on each day?

the output should be date and quantity. There should still be an output even if there is no query on that day.

The only way I can think of is by a table-value UDF. (rough design)

function(startdate, enddate)

{

for each day from start to end

insert into result select count(*) from Query where date_add = currentDate

return

}

Is there a more efficient way to do this?

Thanks,

Max

Something like this will do.

Code Snippet

select dateadd(day,datediff(day,0,date_add),0) [day], count(*) [quantity]

from your_table_goes_here

where date_add between @.startdate and @.enddate

group by dateadd(day,datediff(day,0,date_add),0)

If you need to return "0" for unaccount date, you will need a calendar (or number) table to seed the date to be calculated by.

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

go

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

as

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

go

)

update cte

set pol_seq_id = rn;

AMB

Generate row number in query

How can I
Generate row number in query
Thank youTales Mein wrote:
> How can I
> Generate row number in query
>
> Thank you
In SQL Server 2005:
SELECT
ROW_NUMBER() OVER (ORDER BY key_col) AS row_number,
key_col, col1, col2, ...
FROM your_tbl ;
If that's not the answer you wanted then read my signature to
understand how some more information would help us to help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||And if you are on 2000, then:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||2005 ?
i wish.
I need 2000
thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138140087.744818.153810@.g14g2000cwa.googlegroups.com...
> Tales Mein wrote:
> In SQL Server 2005:
> SELECT
> ROW_NUMBER() OVER (ORDER BY key_col) AS row_number,
> key_col, col1, col2, ...
> FROM your_tbl ;
> If that's not the answer you wanted then read my signature to
> understand how some more information would help us to help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:elygrUTIGHA.516@.TK2MSFTNGP15.phx.gbl...
> And if you are on 2000, then:
> http://support.microsoft.com/defaul...b;EN-US;q186133
> --
> Anith
>

Generate Percentage

I have a report that shows number of units in use for a given day. You can
then drill down and view the number of units in use for a given hour. I want
to display what percentage of units are in use for the given hour. Ex:
Yesterday there were 4 units in use. If you drill down you see that 1 unit
was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
in use.
The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
is 4, that is displayed in the group header. How can I do this?
TIA
PaulPaul,
To achive this u need to pass this value as a parameter to drill down
report and use this value for your calculation in next report. As far as I
know there is now way for you to access field of other report.
Hope this helps
Thanks,
Mahesh
"Paul Timmerman" wrote:
> I have a report that shows number of units in use for a given day. You can
> then drill down and view the number of units in use for a given hour. I want
> to display what percentage of units are in use for the given hour. Ex:
> Yesterday there were 4 units in use. If you drill down you see that 1 unit
> was in use @. 10AM. For 10AM, I want to display that 25% (1/4) of units were
> in use.
> The problem is I cannot seem to reference the SUM(UnitsInUse) value, which
> is 4, that is displayed in the group header. How can I do this?
> TIA
> Paul
>

Generate chart dynamically

Can sombody tell me how to generate cahrt dynamically in reporting service?
I need to generate a number of charts based on the number specified.
Thanks
--
WeiNot sure what you exactly mean with dynamically "generate a number of
charts" - but did you look into the Visibility.Hidden property of the chart?
You can define several charts at design time and hide them using an
expression at runtime (dependent on e.g. a parameter value).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <weiliu00@.hotmail.com> wrote in message
news:D2828717-5299-43FF-B838-F29269B9D713@.microsoft.com...
> Can sombody tell me how to generate cahrt dynamically in reporting
service?
> I need to generate a number of charts based on the number specified.
> Thanks
> --
> Wei|||I 've got a chart (2 actually) inside a list. It creates the charts for
each element in the list. Works like a charm. (Except that you cannot
control the Y-scale, but that will be solved in the next release, I hear).
Hth,
Tom
"David" wrote:
> Can sombody tell me how to generate cahrt dynamically in reporting service?
> I need to generate a number of charts based on the number specified.
> Thanks
> --
> Wei|||Hi, Robert :
Thank you for your kind help. As my project, I do not know how many charts
will be generated. It depends on the number that a user specifies. What I am
planning to do is to use the following codes to generate chart dynamically:
Response.Write("<cc1:ReportViewer id='ReportViewer1' style='Z-INDEX: 101;
LEFT: 8px; POSITION: absolute; TOP: 8px' runat=server Width=680px
Height=632px ServerUrl='http://localhost/reportserver' ReportPath='/Sample
Report/Report1'" & _
"Toolbar=Default></cc1:ReportViewer>")
Of course I need to pass a parameter to the report so that the chart will be
different. However, it looks like the the above codes does not work.
Robert. Could you give me some suggestions?
Thanks a lot,
David
"Robert Bruckner [MSFT]" wrote:
> Not sure what you exactly mean with dynamically "generate a number of
> charts" - but did you look into the Visibility.Hidden property of the chart?
> You can define several charts at design time and hide them using an
> expression at runtime (dependent on e.g. a parameter value).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "David" <weiliu00@.hotmail.com> wrote in message
> news:D2828717-5299-43FF-B838-F29269B9D713@.microsoft.com...
> > Can sombody tell me how to generate cahrt dynamically in reporting
> service?
> >
> > I need to generate a number of charts based on the number specified.
> >
> > Thanks
> > --
> > Wei
>
>

generate a sequential number

Hi,
I'm trying to generate a sequential number as part of a query I have.
I'm sorting the query using one of the items and need to generate a sequential number from 1 to N for the number of items in the selection.
I thought it sounds easy but could not figure this outHelp please

Thanks
MottyHi, I'm trying to generate a sequential number as part of a query I have. I'm sorting the query using one of the items and need to generate a sequential number from 1 to N for the number of items in the selection. I thought it sounds easy but could not figure this out
Q1 Help please
Thanks Motty

A1 As presented, this is a very general requirement however one approach to the issue (which may or may not be suitable depending on the detailed requirements), may be to insert the result set into a newly created #Temp table with an identity column seeded at 1.|||Same functionality with identity function and select into

select OrderId=identity(int,1,1),*
into #Table
from YourTable
order by ColumnX

generate a random int to add to a record

I need to generate a random whole number between 0 and 999 to add to
add to every record in a table. The number needs to be different for
each record in the table.

How would I do this all within TSQL?the procedure of mystery man produces random numbers but they can
appear more than once which should not happen. So it would be
necessary to create a new number, check if it exists, if not use it,
else create a new random number, ... can become a timeconsuming
problem if you try this with numbers between 1 and 999 and you have
more than 999 records in your database :-))
If you only need it to do a random read, try this:

select * from myTable order by newid()

this will show you the records every time in another order.

hth,
Helmut

"Mystery Man" <PromisedOyster@.hotmail.com> schrieb im Newsbeitrag
news:87c81238.0307030244.627bf1@.posting.google.com ...
> Have a look at the Rand function.
> Here is a possible example
> declare @.counter int
> SET @.counter = 1
> WHILE @.counter < 1000
> begin
> print convert(int,substring(convert(varchar(8), RAND(@.counter)),6,3))
> SET @.counter = @.counter + 1
> end