A Challenging SQL Server Puzzle
-
Well, I'd still consider myself a newbie here. I was hoping someone would be able to rewrite the following monster to be more efficient. Thank You and Good Luck! :omg: 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_tTe
-
Well, I'd still consider myself a newbie here. I was hoping someone would be able to rewrite the following monster to be more efficient. Thank You and Good Luck! :omg: 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_tTe
This looks like some sort of dynamic search procedure. Stored procedures are a particularly poor fit for this sort of thing. It is far easier to write dynamic SQL on the client. If you absolutely have to write it in a stored procedure, then you should consider using the
execcommand inside the stored proc. That will let you execute dynamically constructed SQL, which would be much simpler. By this I mean that you can dynamically build the SQL string (based on the parameters), much as you could in a normal programming language.
-
Well, I'd still consider myself a newbie here. I was hoping someone would be able to rewrite the following monster to be more efficient. Thank You and Good Luck! :omg: 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_tTe
Just my 2 cents.... Sql S 2000? I would put this part into a table valued function: For sex I would use and in (i.e WHERE ttester.sexid in(@sexid): IF @sexid = '*' SET @sexid = 'm,f' That would narrow it down a bit 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 Then I would call the function: SELECT Distinct dayspast,etc* From dbo.myfunction(@sexid,@vars...etc) f WHERE not exists ( SELECT tTester.TesterID (don't need distinct or group.) FROM tTester INNER JOIN tResultQue ON (tTester.TesterID = tResultQue.TesterID) INNER JOIN tGroup ON (tResultQue.GroupID = tGroup.GroupID) WHERE tGroup.ScheduleStamp > @DD and f.testerid = tTester.TesterID ) AND f.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 = f.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 = f.TesterID) order by f.LastName