VERY complex stored procedure... help? please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

VERY complex stored procedure... help? please?

Author

Topic

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 12:01:37

I don't even know if this is possible - I'm still fairly new to stored procedures.

I have 4 tables I need to compile data from:
saved_searches
saved_homes
printed_homes
member_info

saved_searches, saved_homes and printed_homes each track the date they were created.

I need to grab all the users from member_info who have had activity in saved_searches, saved_homes and printed_homes between now and X date, along with how many of each (sum(id)) they have performed from saved_searches, saved_homes and printed_homes.

I need to order the results by who has had the most activity in the specified date range, meaning the sum of the sum(id) from the searches going into saved_searches, saved_homes and printed_homes.

The results also have to be restricted to a field in member_info matched to an additional parameter.

Ok... that's a mouthful.

Here's the concept so far:

The following stored procedure would be created for saved_searches, saved_homes and printed_homes, so I'll just show one as the three tables are pretty similar:

@workingWithID
@maxDate
SELECT sum(id), username FROM saved_searches WHERE username IN
(SELECT username FROM member_info WHERE workingWithID = @workingWithID)
AND searchDate > @maxDate

Now, to get all the member info of those with recent activity, I would have this stored procedure that would call the (above) stored procedures for saved_searches, saved_homes and printed_homes:

@workingWithID
@maxDate
SELECT * FROM member_info WHERE username IN
(sp_savedSearch(@workingWithID,@maxDate))
AND username IN
(sp_savedHomes(@workingWithID,@maxDate))
AND username IN
(sp_printedHomes(@workingWithID,@maxDate))

HOWEVER, this concept wouldn't return the sums generated by the smaller stored procedures....

I am truly lost here - can anyone assist me in making sense of how to accomplish this?

Thank you in advance
-=Patrick=-

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 12:42:12

UPDATE

After extensive reading I have changed my approach...

I'm selecting the sums grouped by usernames from each of the three tables and storing them in temp tables but I'm stuck on something I;ve never dopne before - a union select statement

What I hope to achieve with this select statement is a full count of all activity per member, and from there I should be able to loop through those results and grab the member info, the full sum of all activity, and each separate sum for each type of activity... I just don't know how to do that within a stored procedure...

Here's what I have so far - please help!!!

CREATE PROCEDURE dbo.memberRecentActivity
@workingWithID int(4)
@maxDate datetime

AS

-- Variables --
DECLARE @savedSearches nvarchar(4000),
@savedHomes nvarchar(4000),
@printedHomes nvarchar(4000),
@combinedSums nvarchar(4000)

-- Set SAVED SEARCHEs --
SET @savedSearches = (SELECT sum(id), username FROM endeavor_search WHERE searchDate > @maxDate AND username IN (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) GROUP BY username)
IF @savedSearches = ' ' SET @savedSearches = ' '

-- Create Temporary Local Table for saved searches --
DECLARE @svdSrchTable TABLE (sum int(4), username varchar(50))
INSERT INTO @svdSrchTable SELECT value FROM dbo.fnc_split(@savedSearches, ',')

-- Set SAVED HOMES --
SET @savedHomes = (SELECT sum(id), username FROM endeavor_mlsSaved WHERE mlsDate > @maxDate AND username IN (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) GROUP BY username)
IF @savedHomes = ' ' SET @savedHomes = ' '

-- Create Temporary Local Table for saved homes --
DECLARE @svdHomeTable TABLE (sum int(4), username varchar(50))
INSERT INTO @svdHomeTable SELECT value FROM dbo.fnc_split(@savedHomes, ',')

-- Set PRINTED HOMES --
SET @printedHomes = (SELECT sum(id), username FROM endeavor_mlsPrint WHERE mlsDate > @maxDate AND username IN (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) GROUP BY username)
IF @printedHomes = ' ' SET @printedHomes = ' '

-- Create Temporary Local Table for printed homes --
DECLARE @prntHomeTable TABLE (sum int(4), username varchar(50))
INSERT INTO @prntHomeTable SELECT value FROM dbo.fnc_split(@printedHomes, ',')

-- Set COMBINED SUMS --
SET @combinedSums = (SELECT sum(id) as totalSum, username from @svdSrchTable UNION SELECT sum(id) as totalSum, username from @svdHomeTable UNION SELECT sum(id) as totalSum, username from @prntHomeTable GROUP BY username ORDER BY totalSum)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources