Comparing two sql statements in stored procedure

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

SQL Server Development (2000)

Comparing two sql statements in stored procedure

Author

Topic

scrtagt69
Starting Member

3 Posts

Posted - 2005-09-09 : 15:17:51

what i want to do is compare the following 2 sql statements in one stored procedure and if they are equal to each other return the campusaddress where they are equal.

SELECT
COUNT(s.CourseKey) AS total,
e.CampusAddress
FROM
ActiveSchedule s INNER JOIN
ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
WHERE (s.CompletionDate <= '11/30/2005') AND
(s.Bydate <= '11/30/2005')
GROUP BY e.CampusAddress
ORDER BY e.CampusAddress

and this is my second one

SELECT
COUNT(s.CourseKey) AS total,
e.CampusAddress
FROM
ActiveSchedule s INNER JOIN
ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
WHERE (s.ByDate <= '11/30/2005')

GROUP BY e.CampusAddress
ORDER BY e.CampusAddress

How can i do it to compare the 2 and only display the campusaddress when they are equal. thank you for any input on this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-09 : 15:28:08

Here is one way (just using your existing code).


select	CampusAddress
from	(
	SELECT 
	COUNT(s.CourseKey) AS total,
	e.CampusAddress
	FROM 
	ActiveSchedule s INNER JOIN
	ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
	WHERE (s.CompletionDate <= '11/30/2005') AND 
	(s.Bydate <= '11/30/2005')
	GROUP BY e.CampusAddress
	--ORDER BY e.CampusAddress
	
	union all
	
	SELECT 
	COUNT(s.CourseKey) AS total,
	e.CampusAddress
	FROM 
	ActiveSchedule s INNER JOIN
	ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
	WHERE (s.ByDate <= '11/30/2005')
	
	GROUP BY e.CampusAddress
	--ORDER BY e.CampusAddress
	) a
group by CampusAddress
having count(*) = 2
ORDER BY CampusAddress

Be One with the Optimizer
TG

scrtagt69
Starting Member

3 Posts

Posted - 2005-09-09 : 15:46:21

thanks for the reply TG, but that did not quite work...that actually returns the same CampusAddress as the first query when run by itself.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-09 : 16:29:37

>>How can i do it to compare the 2 and only display the campusaddress when they are equal

The "having count(*)=2" means that I'm only returning rows where both statements would return the same [total] result for each campusaddress.

If I'm misunderstanding the problem (which it sounds like I am) Post the DDL of the tables, include some insert statement to populate a few sample rows and finally, post a sample of the desired output.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-10 : 01:06:23

Have you tried this?
Not sure whether this will work



Select T1.campusaddress from 
	(
	SELECT 
	COUNT(s.CourseKey) AS total,
	e.CampusAddress
	FROM 
	ActiveSchedule s INNER JOIN
	ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
	WHERE (s.CompletionDate <= '11/30/2005') AND 
	(s.Bydate <= '11/30/2005')
	GROUP BY e.CampusAddress
	ORDER BY e.CampusAddress
	) T1 
inner join 
	(
	SELECT 
	COUNT(s.CourseKey) AS total,
	e.CampusAddress
	FROM 
	ActiveSchedule s INNER JOIN
	ActiveEmployees e ON s.SPRIDEN_ID = e.SPRIDEN_ID
	WHERE (s.ByDate <= '11/30/2005')
	
	GROUP BY e.CampusAddress
	ORDER BY e.CampusAddress
	) T2 
on T1.campusaddress = T2.campusaddress

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources