Check out my SP... Could it be better[?]

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)

Check out my SP... Could it be better[?]

Author

Topic

TorreyKite
Starting Member

40 Posts

Posted - 2003-12-12 : 10:30:08

Hey guys,
I'm a bit of a newbie, trying to get the most out of an SQL stored procedure. I'm joining 4 tables (ClientInfo, Projects, Campaign, Campaign_Detail) and gathering all the data that i need as well as a few calculated fields. right now, it works fine, I get the results that my App needs. But I wanted to let you all check it out to see if it could be better. Am I using SQL correctly and efficiently.

so here we go...

 
CREATE PROCEDURE getWeeklySetup
@WeekStart smalldatetime

AS

SELECT c.CompanyName, p.ProjectName,ca.WKHours,
	 ca.WKHours - SUM(CASE WHEN cd.CallDate > @WeekStart THEN cd.Time_Billed ELSE 0 END)as wkHoursLeft, 
	ca.TMHours, ca.TMHours - SUM(cd.Time_Billed)as HoursLeft, c.ClientID, p.ProjectID, ca.CampaignID

FROM (Campaign ca INNER JOIN  Projects p ON ca.ProjectID = p.ProjectID 
	JOIN ClientInfo c ON p.ClientID = c.ClientID JOIN Campaign_Detail cd ON ca.CampaignID = cd.CampaignID) 

WHERE ca.Status = 'ready' OR ca.Status = 'pending'

GROUP BY ca.CampaignID, c.CompanyName, p.ProjectName, ca.WKHours,  c.ClientID, p.ProjectID, ca.TMHours

P.S. "@WeekStart" is passed from my COM object indicating what date is for the most recent monday(the start of our business week schedule). is this best or should "@WeekStart" be determined within the SP? The purpose of the procedure is to show management what clients have work that needs to be done so they can schedule the week accordingly. this may be run on any day of the week.

Thanks for your help.

TK

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-12 : 11:23:13

Do you need to bring all those ID's back in the result set? Not a big issue just wondering.

TorreyKite
Starting Member

40 Posts

Posted - 2003-12-12 : 12:00:55

Yes... the results populate a MSflexgrid in vb6 where the user can edit WKHours (hours for the week)which requires the CampaignID for the update. They can also choose to look up the Client or Project associated with that campaign. I guess I could leave out the ClientID as that is in my Projects table. hmmnn...food for thought ... I'm sure there's a quite a few places in my App that would be better off utilizing JOIN more.
Thanks

TK

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-12 : 12:17:39

Here is one way you could do it

CREATE PROCEDURE getWeeklySetup
@WeekStart smalldatetime

AS

SELECT 	c.CompanyName	
	, p.ProjectName
	, ca.WKHours
	, ca.WKHours - SUM(cd.Time_Billed) as wkHoursLeft
	, ca.TMHours
	, ca.TMHours - SUM(cd.Time_Billed)as HoursLeft
	, c.ClientID
        , p.ProjectID
        , ca.CampaignID
FROM	Campaign ca INNER JOIN Projects p 
		ON ca.ProjectID = p.ProjectID 
	JOIN ClientInfo c 
		ON p.ClientID = c.ClientID 
	LEFT JOIN Campaign_Detail cd 
		ON ca.CampaignID = cd.CampaignID
		AND cd.CallDate > @WeekStart 
WHERE	ca.Status = 'ready' OR ca.Status = 'pending' -- maybe use IN ('ready','pending') here
GROUP BY ca.CampaignID, c.CompanyName, p.ProjectName, ca.WKHours, c.ClientID, p.ProjectID, ca.TMHours

but if it seems to be running fine and the results are as expected then you probably are OK.
I don't have sample tables and data to test this but I think it does the same thing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-12 : 16:29:26

quote:
Originally posted by drymchaser

WHERE ca.Status = 'ready' OR ca.Status = 'pending' -- maybe use IN ('ready','pending') here

Nah, they are equivalent.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-12-12 : 17:04:08

Thanks for the insight guys. TK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources