Comparing one record to the next

Good People,
I want to compare one record to the next, that is, say I have one table
call it T1 with recordId (int) and code (char (5)) recordid is
identity field.
I want to compare 'code' of the first record on this table with 'code'
of the second record on this table, the second record with the third
record, third record with fourth and so forth.
recordid may or may not have sequencial order (ie, can be 1,2,3,4,5 and
might be 2,20,34,35,65) I want to perform this comparison using SQL
without a FETCH command.
Can anybody here help me with this?
Many Thanks,
RichardRichard wrote:
> Good People,
> I want to compare one record to the next, that is, say I have one table
> call it T1 with recordId (int) and code (char (5)) recordid is
> identity field.
> I want to compare 'code' of the first record on this table with 'code'
> of the second record on this table, the second record with the third
> record, third record with fourth and so forth.
> recordid may or may not have sequencial order (ie, can be 1,2,3,4,5 and
> might be 2,20,34,35,65) I want to perform this comparison using SQL
> without a FETCH command.
> Can anybody here help me with this?
> Many Thanks,
> Richard
What do you mean by "compare" code of one row to the code on another?
What do you intend to be the result of that comparison? It might help
to know.
You've mentioned only two columns, one IDENTITY. IDENTITY should not be
the only key of a table so are we to assume from your description that
the code column is also unique? Or are we to assume that your
description was incomplete? Please, please post DDL with future
questions so that we don't have to make guesses. It will save you some
typing too...
There's another thing I'm going to have to guess because you didn't
specify it: the version of SQL Server you are using. Assuming SQL
Server 2005, try the following example:
/* DDL: */
CREATE TABLE T1 (id INTEGER PRIMARY KEY, code CHAR(5) NOT NULL UNIQUE
/* ? */)
/* Sample data: */
INSERT INTO T1 (id, code)
SELECT 2,'A' UNION ALL
SELECT 20,'B' UNION ALL
SELECT 34,'C' UNION ALL
SELECT 35,'D' UNION ALL
SELECT 65,'E' ;
/* The query: */
WITH T (r,id,code)
AS
(SELECT RANK() OVER (ORDER BY id),
id, code
FROM T1)
SELECT T1.code AS code1,
T2.code AS code2
FROM T AS T1
LEFT JOIN T AS T2
ON T1.r=T2.r-1 ;
David Portas
SQL Server MVP
--|||Your narrative is a bit vague. Please refer to www.aspfaq.com/5006 and post
your table structures, sample data & expected results for others to better
understand you problem.
Anith|||Thank you for your quick reply
The table has only one primary key and it is the record id (int)
the other fields on the table are service name (char) date raised
(datetime), time raised (char), date closed (datetime), time closed
(char)
I realise that this table can be better designed but for now due to
historical reasons I am stucked with this design.
I want to find the time difference (in minutes) between the closed
date/time of one record and the raised date/time of the next record
(providing they are for the same service name) so that if the
difference between these two values exceeds a certain agreed value both
records are returned and reported.
This table can have a mixture of service names, first record can be for
example 'computer room' second record 'kitchen' third record 'computer
room' fourth record 'car park' and so forth. Time difference has to be
calculated for the same service name - in our example first record of
'computer room' with second record of 'computer room' second record of
'computer room' with third record of 'computer room'
then, first record of 'car park' with second record of 'car park'
second record of 'car park' with third record of 'car park' and so
fourth.
Hope this better clarifies my enquiry.
David Portas wrote:

> Richard wrote:
> What do you mean by "compare" code of one row to the code on another?
> What do you intend to be the result of that comparison? It might help
> to know.
> You've mentioned only two columns, one IDENTITY. IDENTITY should not be
> the only key of a table so are we to assume from your description that
> the code column is also unique? Or are we to assume that your
> description was incomplete? Please, please post DDL with future
> questions so that we don't have to make guesses. It will save you some
> typing too...
> There's another thing I'm going to have to guess because you didn't
> specify it: the version of SQL Server you are using. Assuming SQL
> Server 2005, try the following example:
> /* DDL: */
> CREATE TABLE T1 (id INTEGER PRIMARY KEY, code CHAR(5) NOT NULL UNIQUE
> /* ? */)
> /* Sample data: */
> INSERT INTO T1 (id, code)
> SELECT 2,'A' UNION ALL
> SELECT 20,'B' UNION ALL
> SELECT 34,'C' UNION ALL
> SELECT 35,'D' UNION ALL
> SELECT 65,'E' ;
> /* The query: */
> WITH T (r,id,code)
> AS
> (SELECT RANK() OVER (ORDER BY id),
> id, code
> FROM T1)
> SELECT T1.code AS code1,
> T2.code AS code2
> FROM T AS T1
> LEFT JOIN T AS T2
> ON T1.r=T2.r-1 ;
> --
> David Portas
> SQL Server MVP
> --|||for SQL 2000:
create table #t(id int, c1 char(1))
insert into #t values(1, 'a')
insert into #t values(3, 'a')
insert into #t values(17, 'b')
insert into #t values(29, 'b')
insert into #t values(30, 'c')
go
-- matches
select t1.id id1, t2.id id2 from #t t1
join #t t2
on t1.c1=t2.c1
where t1.id<t2.id
and not exists(select 1 from #t t3 where (t1.id<t3.id) and
(t3.id<t2.id))
id1 id2
-- --
1 3
17 29
(2 row(s) affected)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications (What the heck does "compare" mean?).
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS. And an IDENTITY can never be a relational key;
they are a way of faking a 1950's sequential file when you do not know
RDBMS and have not found the keys.|||>> I want to find the time difference (in minutes) between the closed date/time
of one record and the raised date/time of the next record [sic] (providing they a
re for the same service name) so that if the difference between these two va
lues exceeds a ce
rtain agreed value both records [sic] are returned and reported. <<
Okay, this is a common design error. Go back and read Zeno and
Einstein; time comes in durations, so you need to model it with
(start_time, end_time) pairs. What you are modeling is the *physical*
sign-in/sign-out sheet and not the data contained in the form. You
are even using the words "record" and "field", which is nothing
whatsoever like rows and columns in an RDBMS. You seem to think that
tables have a sequence to them.
IDENTITY cannot ever be a relational key. Finally, SQL Server uses a
DATETIME datatype, so the CHAR(n) is a total mess. Whoever did this,
did nothing right. I am going to guess that you want something like
this, since you do not think enough of us to give us DDL:
CREATE TABLE ServiceLog
(service_name CHAR(15) NOT NULL
REFERENCES Services (service_name),
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means on-going
CHECK (start_date < end_date),
PRIMARY KEY (service_name, start_date));
Now update this table when you get new data. Your other choice is a
VIEW with an OUTER JOIN that will run like glue which produces this
table.|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:575201
--CELKO-- wrote:
rtain agreed value both records [sic] are returned and reported. <<
>
> Okay, this is a common design error. Go back and read Zeno and
> Einstein; time comes in durations, so you need to model it with
> (start_time, end_time) pairs. What you are modeling is the *physical*
> sign-in/sign-out sheet and not the data contained in the form. You
> are even using the words "record" and "field", which is nothing
> whatsoever like rows and columns in an RDBMS. You seem to think that
> tables have a sequence to them.
> IDENTITY cannot ever be a relational key. Finally, SQL Server uses a
> DATETIME datatype, so the CHAR(n) is a total mess. Whoever did this,
> did nothing right. I am going to guess that you want something like
> this, since you do not think enough of us to give us DDL:
> CREATE TABLE ServiceLog
> (service_name CHAR(15) NOT NULL
> REFERENCES Services (service_name),
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME, -- null means on-going
> CHECK (start_date < end_date),
> PRIMARY KEY (service_name, start_date));
> Now update this table when you get new data. Your other choice is a
> VIEW with an OUTER JOIN that will run like glue which produces this
> table.
>
I know this database is a total mass, the person that designed this
database knew nothing about E-R, for now however, till we rewright the
whole system (which was really designed using Ms Access and then the
tables were simply thrown to SQL 2K server) I am stucked with this design.
Since I am currently not at work I do not have the design in front of me
but the design of this table is fairly simple, something like this
id (int, identity), service_name (varchar(20) NOT NULL), raised_date
(datetime NULL permitted), raised_time (char(5) NULL permitted),
closed_date (datetime NULL permitted),closed_time (char(5) NULL permitted)
example data -
'computer room', 12/21/2005, 07:30, 12/21/2005, 07:50
'car park', 12/21/2005, 07:32, 12/21/2005, 07:58
'computer room', 12/21/2005, 08:01, 12/21/2005, 08:10
'computer room', 12/22/2005, 17:05, 12/22/2005, 18:09
'kitchen', 12/22/2005, 17:30,,
each entry on this table represents a service issue and our SLA dictates
the time gap from the time we closed one issue and the time we raised a
new issue for the same service (not the time between raising an issue
and closing it but the time it takes from closing one issue and opening
a new one for the same service) if that time falls (or exceeds) a
certain prescribed time the system needs to report on the two issues
Hence my need to find the time difference (in minutes) between one
issue and the next issue
Regard,
Richard|||"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM>:
news:1136332369.645595.305340@.f14g2000cwb.googlegroups.com...
> for SQL 2000:
> create table #t(id int, c1 char(1))
> insert into #t values(1, 'a')
> insert into #t values(3, 'a')
> insert into #t values(17, 'b')
> insert into #t values(29, 'b')
> insert into #t values(30, 'c')
> go
> -- matches
> select t1.id id1, t2.id id2 from #t t1
> join #t t2
> on t1.c1=t2.c1
> where t1.id<t2.id
> and not exists(select 1 from #t t3 where (t1.id<t3.id) and
> (t3.id<t2.id))
> id1 id2
> -- --
> 1 3
> 17 29
> (2 row(s) affected)
>
Alexander,
for data like yours
it should be enough
SELECT MIN(id), MAX(id)
FROM #T
GROUP BY c1
HAVING MIN(id) <> MAX(id) -- perhaps optional
--
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||>Alexander,
>for data like yours
>it should be enough
Andrey,
I think for a slightly bigger set of data your query won't work, but
mine does:
create table #t(id int, c1 char(1))
insert into #t values(1, 'a')
insert into #t values(3, 'a')
insert into #t values(17, 'b')
insert into #t values(29, 'b')
insert into #t values(30, 'c')
insert into #t values(41, 'a')
insert into #t values(43, 'a')
insert into #t values(67, 'b')
insert into #t values(79, 'b')
insert into #t values(90, 'c')
go
-- matches
select t1.id id1, t2.id id2 from #t t1
join #t t2
on t1.c1=t2.c1
where t1.id<t2.id
and not exists(select 1 from #t t3 where (t1.id<t3.id) and
(t3.id<t2.id))
id1 id2
-- --
1 3
17 29
41 43
67 79
-- this seems to be just plain wrong
SELECT MIN(id), MAX(id)
FROM #T
GROUP BY c1
HAVING MIN(id) <> MAX(id)
-- --
1 43
17 79
30 90

Commit a loop of inserting

I use loop to insert few record into a table:
But the for_Loop only loop once and throw an error:
"The variable name '@.res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."
What should i do to get this fix?

Code:

Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim DataSources1 As New SqlDataSource()
DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

DataSources1.InsertCommandType = SqlDataSourceCommandType.Text
DataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@.res_name, @.my_menu)"

Dim c As Integer
For c = 0 To selectListBox.Items.Count - 1 Step +2

DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)
DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)
DataSources1.Insert()
Next
End Sub

your loop is trying the re-add the same parameters on each pass.

For c = 0To selectListBox.Items.Count - 1Step +2 DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text) DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text) DataSources1.Insert() DataSources1.InsertParameters.Clear()'remove the previous parametersNext

you can clear them after you perform the insert so they can then be re-added with new values on the next pass through the loop

|||thanks, now the loop works fine.

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes has multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would return a recordset with each errorCode as its own field. If the newly generated field values could have the same name as the errorCode, that would be good as well.
Thanks,
Stew
I suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew
|||Aaron, I understand your suggestion and gave it some consideration. However, I'm unaware of all the possible error codes which may be encountered. Meaning I'd be entering new errorcodes as they are encountered - which I'm not interested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. However if there is a query that would satisfy my original request, I'd still be interested in that.
Thanks,
Stew
|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes ha
s multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would retur
n a recordset with each errorCode as its own field. If the newly generated f
ield values could have the same name as the errorCode, that would be good as
well.
Thanks,
StewI suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew|||Aaron, I understand your suggestion and gave it some consideration. However,
I'm unaware of all the possible error codes which may be encountered. Meani
ng I'd be entering new errorcodes as they are encountered - which I'm not in
terested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. Ho
wever if there is a query that would satisfy my original request, I'd still
be interested in that.
Thanks,
Stew|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net