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)
 A Challengin SQL Server Puzzle

Author  Topic 

Trent
Starting Member

39 Posts
Posted - 2004-07-21 : 12:58:08
I still consider myself a newbie at this. I was hoping someone
could make the following monster more efficient.
Thank You and Good Luck!

CREATE PROCEDURE usp_Calls
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime,
@CallActivity datetime
AS
declare @CAct table (CallStamp datetime, TesterID char(7), StudyNo char(15), QueStatusID varchar(2))
declare @CallFT table (CallFrom datetime, CallTo datetime, TesterID char(7))

insert @CAct
select distinct tResultQue.CallStamp, tResultQue.TesterID, tResultQue.StudyNo, tResultQue.QueStatusID
from tResultQue
where tResultQue.CallStamp >= @CallActivity

insert @CallFT
select distinct tTesterContactInfo.ContactBestFrom as CallFrom, tTesterContactInfo.ContactBestTo as CallTo, tTesterContactInfo.TesterID
from tTesterContactInfo

IF @SexID = '*' AND @RaceID = '*' AND @SiteID <> '90000'

BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status,TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID) AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID = '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID <> '*' AND @RaceID = '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID <> '*' AND @RaceID = '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status , TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID <> '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.SexID = @SexID AND tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID = '*' AND @RaceID <> '*' AND @SiteID <> '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tTester.SiteID = @SiteID AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END

IF @SexID = '*' AND @RaceID <> '*' AND @SiteID = '90000'
BEGIN SELECT distinct datediff(dd, getdate(), tGroup.ScheduleStamp) * -1 as DaysPast, tResultQue.TesterID, tTester.LastName, tTester.FirstName, tTester.WorkNo, tTester.WorkExtension as WorkExt, tTester.HomeNo, tTester.SexID as Sex, tTester.Birth, tTester.RaceID as Race, Poof.CallStamp as CallActivity, Poof.StudyNo as StudyActivity, Poof.QueStatusID as Status, TCall.CallFrom, TCall.CallTo
FROM tGroup INNER JOIN tResultQue ON (tGroup.GroupID = tResultQue.GroupID) INNER JOIN tTester ON (tResultQue.TesterID = tTester.TesterID) left join @CAct as Poof on (Poof.TesterID = tTester.TesterID) left join @CallFT as TCall on (TCall.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND tTester.Birth >= @LBirth AND tTester.Birth <= @UBirth AND tGroup.ScheduleStamp <= @DD AND tTester.TesterID NOT IN
(SELECT distinct tTester.TesterID
FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
AND
tGroup.ScheduleStamp = (SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
and
tResultQue.CallStamp = (SELECT MAX(cor_tResultQue.CallStamp)
FROM tTester cor_tTester INNER JOIN tResultQue cor_tResultQue ON (cor_tTester.TesterID = cor_tResultQue.TesterID) INNER JOIN tGroup cor_tGroup ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TesterID)
order by tTester.LastName
END
GO

kselvia
Aged Yak Warrior

526 Posts
Posted - 2004-07-21 : 13:18:05
Whew! I'll think I'll wait for Kristen to reformat it for us

(And yes, this looks like it can be rewritten as a single select.)

--Ken
Your Kung-Fu is not strong. -- 'The Core'
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site