Help finding record pairs

I'm trying to find away to match up two rows into a single event.
PK Name Event Type
1 Foo Open Foreground
8 Foo Close Foreground
16 Bar Open Foreground
18 Bar Open Background
22 Bar Close Background
23 Bar Cose Foreground
I need to know that
1 is paired with 8
16 is paired with 23
18 is paired with 22
Any help would be appreciatedPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
This looks like a common design error. Time is based on durations
(Zeno, Einstein, et al) and not on split facts like you are showing.
Also, your data element names are much too vague for any data model.
The kludge to repair the design flaw will involve self-joins and
aggregations. It has been posted several times in various forms.
I think that you wanted something like this in the first place, instead
of wasting insane amounts of time constructing the proper design from
scratch everytime you use it.
CREATE TABLE Events
(event_name CHAR(3) NOT NULL,
open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
close_time DATETIME, --null means still open
CHECK (open_time < close_time),
event_type CHAR(10) NOT NULL
CHECK (event_type IN ('Foreground', 'Background')),
PRIMARY KEY (event_name, open_time));|||"lee_mre@..no-spam.yahoo.com" <lee_mre@.yahoo.com> wrote in message
news:1134010863.875294.231700@.o13g2000cwo.googlegroups.com...
> I'm trying to find away to match up two rows into a single event.
>
> PK Name Event Type
> 1 Foo Open Foreground
> 8 Foo Close Foreground
> 16 Bar Open Foreground
> 18 Bar Open Background
> 22 Bar Close Background
> 23 Bar Cose Foreground
>
> I need to know that
> 1 is paired with 8
> 16 is paired with 23
> 18 is paired with 22
> Any help would be appreciated
>
create table T
(
PK int primary key,
Name varchar(5),
Event varchar(5),
Type varchar(20)
)
insert into T(PK,Name,Event,Type)
select 1, 'Foo', 'Open', 'Foreground'
union all
select 8, 'Foo', 'Close', 'Foreground'
union all
select 16, 'Bar', 'Open', 'Foreground'
union all
select 18, 'Bar', 'Open', 'Background'
union all
select 22, 'Bar', 'Close', 'Background'
union all
select 23, 'Bar', 'Close', 'Foreground'
select
o.pk Opened, c.pk Closed
from
T o
join T c
on o.Name = c.Name
and o.Event = 'Open'
and c.Event = 'Close'
and o.Type = c.Type
--BUT I suspect that you really want to match each open event with the
"next" close event of the same type. Assuming that "next" means next ORDER
BY PK.
select
o.pk Opened,
(select min(pk)
from T
where Event = 'Close'
and Type = o.Type
and pk > o.pk) Closed
from T o
where Event = 'Open'
--David|||On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:

> CREATE TABLE Events
> (event_name CHAR(3) NOT NULL,
> open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> close_time DATETIME, --null means still open
> CHECK (open_time < close_time),
> event_type CHAR(10) NOT NULL
> CHECK (event_type IN ('Foreground', 'Background')),
> PRIMARY KEY (event_name, open_time));
Question ... how will the check constraint work when close_time is null on
an insert? Perhaps it should be
CHECK (NOT open_time >= close_time)
?|||On Thu, 8 Dec 2005 12:07:47 -0500, Ross Presser wrote:

>On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:
>
>Question ... how will the check constraint work when close_time is null on
>an insert? Perhaps it should be
> CHECK (NOT open_time >= close_time)
>?
Hi Ross,
Not necessary. If close_time is NULL and open_time is '2005-12-10', then
the CHECK constraint suggested by Celko reads
CHECK (open_time < close_time)
after substitution
CHECK ('2005-12-10' < NULL)
which evaluates to
CHECK (Unknown)
A major difference between a logic test in a WHERE and a logic test in a
constraint is that a WHERE includes only rows if the test evaluates to
True (i.e. False and Unknown are both rejected), but a constraint
accepts rows if the test evaluates to either True or False (or, to
paraphrase the ANSI standard, the constraint must not evaluate to
False).
The row with NULL close_time will be allowed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:
<snip>

>A major difference between a logic test in a WHERE and a logic test in a
>constraint is that a WHERE includes only rows if the test evaluates to
>True (i.e. False and Unknown are both rejected), but a constraint
>accepts rows if the test evaluates to either True or False (or, to
>
I think you meant to write "... if the test evaluates to either True or
Unknown ..."
SK

>paraphrase the ANSI standard, the constraint must not evaluate to
>False).
>
>|||On Sat, 10 Dec 2005 20:16:22 -0500, Steve Kass wrote:

>
>Hugo Kornelis wrote:
><snip>
>
>I think you meant to write "... if the test evaluates to either True or
>Unknown ..."
Hi Steve,
You're right. I obviously typed to fast and forgot to check before
posting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql