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