SQL Syntax: convert correlated subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Syntax: convert correlated subquery

Author

Topic

iansr
Starting Member

23 Posts

Posted - 2003-12-30 : 21:49:07

Greetings!

I created a stored procedure in a proprietary CRM database.
In the procedure, I had to use a correlated subquery twice to return two different columns from the same resultset. I would like to ask for possible alternatives.

Basically, the problem is that I need to use "SELECT TOP 1 column ... ORDER BY column" in the correlated subquery. How can I convert this to a derived table? (Or any other alternative?)

For simulation, I created a TEST database and the sql script. I could email it to you if you wish.

Here is the query:

*******
To illustrate:

ACTIVITIES TABLE


ROW_ID	ACTIVITY_TYPE	START	END
1	Email		16:00	16:11	
2	Handle		16:05
3	Handle		16:10
4	Email		17:00	17:06
5	Handle		17:05

The ACTIVITIES table contains two types of activities, 'Email' and 'Handle' activity types.
I need to join an Email Activity with it's corresponding Handle activity.
The problem is, there could be several Handle activities for each Email activity.
The correct Handle activity is the latest Handle after that Email but before the next Email.
We can get the order of the activities from the START and END columns in the ACTIVITIES table (No where else)

In the Query, I should be able to join rows 1 with 3, and 4 with 5.
So the ultimate output should be:


EMAIL_ROW_ID	START	Handle ROW_ID	START	
1		16:00	3		16:10
4		17:00	5		17:05

For simplicity, I removed other fields that are necessary should there be other records aside from the ones above. For this query, assume that the ACTIVITIES table contains only the above 5 records.


SELECT  	
	EMAIL.ROW_ID, 
	'Email Start' = EMAIL.START, 
	'HANDLE ROW_ID' = (
		SELECT TOP 1 ROW_ID
	    FROM ACTIVITIES HANDLE
	    WHERE HANDLE.ACTIVITY_TYPE = 'Handle' 
		AND HANDLE.START >= EMAIL.START 
		AND HANDLE.START <= EMAIL.[END]
		ORDER BY HANDLE.START DESC
	),
	'Handle Start' =
    	(
		SELECT TOP 1 START
	    FROM ACTIVITIES HANDLE
	    WHERE HANDLE.ACTIVITY_TYPE = 'Handle' 
		AND HANDLE.START >= EMAIL.START 
		AND HANDLE.START <= EMAIL.[END]
		ORDER BY HANDLE.START DESC
	)
FROM ACTIVITIES EMAIL
WHERE ACTIVITY_TYPE = 'Email'

As you can see in the query, both correlated subqueries are very similar, and I was thinking that an alternative could remove that duplication.

*******

Thank you very much,
Ian

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 22:38:17

[code]create table activities (row_id int, activity_type varchar(10), starttime datetime, endtime datetime )

insert into activities
select 1,'Email', '12/1/2003 16:00:00', '12/1/2003 16:11:00'
union all select 2,'Handle','12/1/2003 16:05:00',NULL
union all select 3,'Handle','12/1/2003 16:10:00',NULL
union all select 4,'Email','12/1/2003 17:00:00','12/1/2003 17:06:00'
union all select 5,'Handle','12/1/2003 17:05:00',null

SELECT d.*, a.starttime
FROM
(
SELECT row_id email_row_id, starttime,
(
SELECT MAX(row_id)
FROM activities a1
WHERE a1.activity_type = 'Handle' AND a1.starttime >= a.starttime AND a1.starttime <= a.endtime
) handle_row_id
FROM activities a
WHERE activity_type = 'Email'
) d
JOIN activities a ON a.row_id = d.handle_row_id

--DROP TABLE activities
[/code]

iansr
Starting Member

23 Posts

Posted - 2003-12-31 : 05:16:57

Thanks for the reply ehorn. This could very well solve my problem.

However, row_ID is not assured to be ascending in order (in fact it ain't even an integer field), therefore using the MAX function on row_ID might not be fool proof. I tried modifying your code to use the START field instead. However at this point, there are 4 nest levels already.

Any other suggestions?


SELECT D.*, a.start
FROM
(
	SELECT row_id 'email_row_id', start, 
	(     
		SELECT ROW_ID 
		FROM ACTIVITIES 
		WHERE ACTIVITY_TYPE = 'Handle'
		AND START = (
			SELECT MAX(START) 
			FROM ACTIVITIES a1 
			WHERE a1.ACTIVITY_TYPE = 'Handle' 
			AND a1.START >= a.START 
			AND a1.START <= a.[End]
		)

	) handle_row_id
	FROM activities a 
	WHERE activity_type = 'Email'
) D
JOIN activities a ON a.row_id = D.handle_row_id

Thanks a lot,
Ian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources