SQL Guru needed 
Author Message
 SQL Guru needed

Hi,

Can any one tell me how I can do the following in SQL (without
resorting to cursors n stored procedures)

I have table like :

item    status  seq
-------------------------------
A       start   2          (item A starts)
A       stop    3          (later item A stops)
B       start   4
B       stop    5
A       start   8          (later again item A starts ...)
A       stop    12         (.... and stops again )  

& I want to produce a Query that goes

item    start   stop
-----------------------------
A               2               3
B               4               5
A               8               12

I've tried

SELECT * from table1  as StartT  left  join table1 as StopT
on startT.item = StopT.item
where (StartT.status  = 'Start' and StopT.status = 'Stop' )

but that ends up with repeated rows.  Any Ideas ?  

Bill

-----------------------
 Dr. Bill Honey        
 Collage Consulting Ltd.                    



Tue, 06 Nov 2001 03:00:00 GMT
 SQL Guru needed

It would be a lot easier if you had an additional column to identify a
start/stop pair.  If you change your table structure to this:

CREATE TABLE dbo.test02 (
 pair_key int NOT NULL ,
 item char (1) NOT NULL ,
 status char (6) NOT NULL ,
 seq int NOT NULL
)

then the SQL statement

SELECT StartT.item, StartT.seq 'Start', StopT.seq 'Stop'
FROM test02 AS StartT
LEFT JOIN test02 AS StopT
    ON StartT.pair_key = StopT.pair_key AND StartT.item = StopT.item
WHERE ( StartT.status = 'start' AND StopT.status = 'stop')

yields:

item Start       Stop
---- ----------- -----------
a    2           3
b    4           5
a    8           12

--
Alan Rueckgauer
Rueckgauer Systems
Windows NT & Internet Solutions
www.rueckgauer.com

NOTE:  I do *NOT* read or respond to email replies to newsgroup postings.
Please reply only in the newsgroup so everyone can benefit from the
discussion.

=====

Quote:

>Hi,

>Can any one tell me how I can do the following in SQL (without
>resorting to cursors n stored procedures)

>I have table like :

>item status seq
>-------------------------------
>A   start 2          (item A starts)
>A   stop 3          (later item A stops)
>B   start 4
>B   stop 5
>A  start 8          (later again item A starts ...)
>A  stop 12         (.... and stops again )

>& I want to produce a Query that goes

>item   start  stop
>-----------------------------
>A       2        3
>B       4        5
>A       8       12

>I've tried

>SELECT * from table1  as StartT  left  join table1 as StopT
>on startT.item = StopT.item
>where (StartT.status  = 'Start' and StopT.status = 'Stop' )

>but that ends up with repeated rows.  Any Ideas ?

>Bill

>-----------------------
> Dr. Bill Honey
> Collage Consulting Ltd.



Tue, 06 Nov 2001 03:00:00 GMT
 SQL Guru needed
Alan,

The problem is that when I insert a new stop, I have no way of knowing
what the corresponding start was.  

All I can guarantee is that every start will be followed by a stop,
and that the seq number always increases

I guess what I'm saying, is that I need an easy way to generate that
pair_key value.

I thought about opening a pair of cursors & always moving forward from
a start till I find the corresponding stop, but really I wanted to do
it if plain SQL.

Bill

Quote:

>It would be a lot easier if you had an additional column to identify a
>start/stop pair.  If you change your table structure to this:

>CREATE TABLE dbo.test02 (
> pair_key int NOT NULL ,
> item char (1) NOT NULL ,
> status char (6) NOT NULL ,
> seq int NOT NULL
>)

>then the SQL statement

>SELECT StartT.item, StartT.seq 'Start', StopT.seq 'Stop'
>FROM test02 AS StartT
>LEFT JOIN test02 AS StopT
>    ON StartT.pair_key = StopT.pair_key AND StartT.item = StopT.item
>WHERE ( StartT.status = 'start' AND StopT.status = 'stop')

>yields:

>item Start       Stop
>---- ----------- -----------
>a    2           3
>b    4           5
>a    8           12

---------------------------------------------------------------
 Dr. Bill Honey        Independent I.T. Consultant
 Collage Consulting Ltd.                    
 Tel  0973 837620 (mobile)
      01752 698370 (evenings)
 Fax  01752 698375                

---------------------------------------------------------------


Tue, 06 Nov 2001 03:00:00 GMT
 SQL Guru needed
You haven't said how this data is being populated.  IAE, whatever is doing
the population is going to have to be smart enough to tell you what start a
stop belongs to.  An extremely simplistic solution would be to use a
timestamp and assume that a stop will always correspond to the latest start
for that item.  You'd have to change the join to something like "StartT.item
= StopT.item AND StopT.timestamp > StartT.timestamp".  I can't test this out
just now, but you should get the basic idea.

--
Alan Rueckgauer
Rueckgauer Systems
Windows NT & Internet Solutions
www.rueckgauer.com

NOTE:  I do *NOT* read or respond to email replies to newsgroup postings.
Please reply only in the newsgroup so everyone can benefit from the
discussion.

=====

Quote:

>Alan,

>The problem is that when I insert a new stop, I have no way of knowing
>what the corresponding start was.

>All I can guarantee is that every start will be followed by a stop,
>and that the seq number always increases

>I guess what I'm saying, is that I need an easy way to generate that
>pair_key value.

>I thought about opening a pair of cursors & always moving forward from
>a start till I find the corresponding stop, but really I wanted to do
>it if plain SQL.

>Bill


>>It would be a lot easier if you had an additional column to identify a
>>start/stop pair.  If you change your table structure to this:

>>CREATE TABLE dbo.test02 (
>> pair_key int NOT NULL ,
>> item char (1) NOT NULL ,
>> status char (6) NOT NULL ,
>> seq int NOT NULL
>>)

>>then the SQL statement

>>SELECT StartT.item, StartT.seq 'Start', StopT.seq 'Stop'
>>FROM test02 AS StartT
>>LEFT JOIN test02 AS StopT
>>    ON StartT.pair_key = StopT.pair_key AND StartT.item = StopT.item
>>WHERE ( StartT.status = 'start' AND StopT.status = 'stop')

>>yields:

>>item Start       Stop
>>---- ----------- -----------
>>a    2           3
>>b    4           5
>>a    8           12

>---------------------------------------------------------------
> Dr. Bill Honey        Independent I.T. Consultant
> Collage Consulting Ltd.
> Tel  0973 837620 (mobile)
>      01752 698370 (evenings)
> Fax  01752 698375

>---------------------------------------------------------------



Tue, 06 Nov 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. SQL Gurus: Need help with simple SELECT Statement

2. Help. A tricky SQL guru needed

3. Transact-SQL guru needed...

4. To SQL Guru, need help!

5. SQL Guru needed M$ person?

6. Illegal Outer Join Error - SQL Guru Needed

7. SQL guru needed in Chicago

8. Illegal Outer Join Error - SQL Guru Needed

9. Access/SQL gurus: Need your help with SQL statement!

10. Access/SQL gurus: Need your help with SQL statement!

11. Access/SQL gurus: Need your help with SQL statement!

12. Access/SQL gurus: Need your help with SQL statement!


 
Powered by phpBB® Forum Software