Re: Sort of a loop in SQL - how to do it

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 12:05:41 +0100

On Mon, 22 Mar 2004 09:23:33 +0100, Goran Vukusic wrote:

>Hi Steve,
>
>thanks for the answer.
>I also want to apologize for using the wrong name in the 'from' field. It
>was the computer of a friend of mine and I just forgot to change the
>settings.
>
>The problem is that I don't want to add all records having the same Name and
>ActionID, but only those which have the same Name and ActionID AND are in
>subsequent records. That is what in my eyes makes the problem hard.
>
>One more example I hope will desribe the problem better:
>
>Line| Name ActionID TimeInterval (seconds)
>-----|--------------------------------------------------------------------
>1 | x 1 20
>2 | x 2 10
>3 | x 3 15
>4 | y 3 45
>5 | y 3 15
>6 | y 2 25
>7 | x 1 10
>8 | x 2 20
>
>In this table I would add only lines 4 and 5 (Name=y ActionID=3) because
>they have the same Name and ActionID AND also come one after another.
>I don't want to add lines 1 and 7 or 2 and 8 even though they also have the
>same Name and ActionID, because they are not in subsequent records.
>
>Any ideas on this?
>
>Many thanks.
>
>Bye,
>Goran

This is not possible.

Any table in a relational database is by definition an UNORDERED set
of rows. For a relational database, the example above is completely
equivalent to the one below:

Line| Name ActionID TimeInterval (seconds)
-----|--------------------------------------------------------------------
3 | x 3 15
6 | y 2 25
8 | x 2 20
5 | y 3 15
4 | y 3 45
1 | x 1 20
7 | x 1 10
2 | x 2 10

Therefore, this part of your question:

> (...) but only those which have the same Name and ActionID AND are in
>subsequent records.

contains two logical errors.

1) There are no records in a relational database, only rows. I usually
don't fret over this terminology issue, but in this particular case it
is crucial. Fields are part of a file, a hierarchic database or a
CODASYL database; these all have an implicit ordering. Rows are part
of a relational database and are unorderded. Thus:

2) There is no such thing as "subsequent" in a relational database.
You have to define your output in terms of just the input, not the
order in which the rows just happen to be read on a give executtion of
a query (as the order might be different on the next execution).

In short: what you want is not possible if you can't define
"subsequent" in terms of your data. If you can define "subsequent" in
terms of your data, it can be done. If the column "Data" above was not
just added for clarity but actually part of the table, you'd have no
problem:

set nocount on
go
create table MyTable (Line int not null primary key,
                      [Name] char(1) not null,
                      ActionID int not null,
                      TimeInterval int not null)
go
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (1, 'x', 1, 20)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (2, 'x', 2, 10)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (3, 'x', 3, 15)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (4, 'y', 3, 45)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (5, 'y', 3, 15)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (6, 'y', 2, 25)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (7, 'x', 1, 10)
insert MyTable (Line, [Name], ActionID, TimeInterval)
values (8, 'x', 2, 20)
go
create view StartOfGroup
as select Line
   from MyTable AS O
   where not exists
     (select * from MyTable AS I
      where O.Line = I.Line + 1
      and O.[Name] = I.[Name]
      and O.ActionID = I.ActionID)
go
select M.Line, [Name], ActionID,
      (select sum(TimeInterval)
       from MyTable as S
       where S.Line >= M.Line
       and S.[Name] = M.[Name]
       and S.ActionID = M.ActionID
       and (S.Line not in (select Line from StartOfGroup)
            or S.Line = M.Line)) AS TimeInterval
from MyTable AS M
inner join StartOfGroup
        on StartOfGroup.Line = M.Line
go
drop view StartOfGroup
go
drop table MyTable
go

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Sort of a loop in SQL - how to do it
    ... was the computer of a friend of mine and I just forgot to change the ... ActionID, but only those which have the same Name and ActionID AND are in ... because they are not in subsequent records. ... >>like visual basic etc. but I would prefer using SQL for this if possible. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sort of a loop in SQL - how to do it
    ... ActionID TINYINT NOT NULL, ... because they are not in subsequent records. ... >> Goran, ...
    (microsoft.public.sqlserver.programming)
  • Re: Sort of a loop in SQL - how to do it
    ... > ActionID TINYINT NOT NULL, ... because they are not in subsequent records. ...
    (microsoft.public.sqlserver.programming)