Stored Procedure timing out for SSRS - Need Some Help

• Friday, February 08, 2013 9:05 PM

   

   
  

  0

  I'm a front-end developer who has been handed a Stored Procedure that is taking long periods of time to run and eventually the query is timing out.

  This stored procedure is used for a variety of reports via SSRS. The front-end user chooses the pertaining information to query on, chooses a report and this stored procedure will filter out any clients who does not meet the criteria selected from the front end, and then creates a subquery for the report chosen.

  I'm very new to TSQL but I have a feeling you guys are going to laugh. Here it is, how can it be made better?

  USE [CTS]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER proc  [dbo].[FindClientScoutMembers] (  @ReportClientMemberGUID varchar(200), @ReturnOption int = 1)
  AS
  SET NOCOUNT ON;
  If OBJECT_ID('tempdb..#ClientScoutMembers') is not null
  	Drop Table #ClientScoutMembers;
  
  
  Declare 
  @StartDate datetime, 
  @EndDate datetime, 
  @RelativeCounselingDateID int,
  @RelativeEventDateID int, 
  @EventStartDate datetime,
  @EventEndDate datetime,
  @CounselingHours int, 
  @PrepHours int,
  ect...
  @FollowUpResultID varchar(10) 
  
  
  -------------------------------------------------------------------------------------------------------
  ------  Assign values to all of the possible parameters, use GetParm to read the table
  -------------------------------------------------------------------------------------------------------
  declare @date datetime, @CurrMonth int, @CurrYear int, @CurrQuarter int, @CurrDayofYear int
   
  set @date = getdate()
  set @CurrMonth = DatePart(mm,GetDate())
  set @CurrYear = DatePart(y,GetDate())
  set @CurrQuarter = DatePart(qq,GetDate())
  set @CurrDayOfYear = DatePart(dy,GetDate())
  
  declare @reportGUID varchar(50)
  set @reportguid = @ReportClientMemberGUID
  
  SELECT @StartDate = CounsStartDate,
         @EndDate = CounsEndDate,
         @RelativeCounselingDateID = RelativeCounselingDateID,
         @EventStartDate = EventStartDate,
         @EventEndDate = EventEndDate,
         @RelativeEventDateID = RelativeEventDateID,
         @RelativeBusEstDateID = RelativeBusEstDateID,
         @RelativeReferralDateID = RelativeReferralDateID,
         @ReferralStartDate = ReferralStartDate,
         @ReferralEndDate = ReferralEndDate,
         @CounselingHours = CounselingHours,
         @PrepHours = PrepHours,
         ect...
  FROM   dbo.GetParms(@reportguid) as G
  
  
  Create Table #ClientScoutMembers
  (
  	ClientID INT  ,
  	PersonID INT Not Null
  );
  
  Create Table #ClientScoutMembersS2
  (
  		PersonID INT Not Null
  );
  
  
  -------------------------------------------------------------------------------------------------------
  ----- Set all of the relative date values
  -------------------------------------------------------------------------------------------------------
  If @RelativeCounselingDateID = 1  begin 
  	set @StartDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @EndDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 1  begin 
  	set @EventStartDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @EventEndDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 1  begin 
  	set @ReferralStartDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @ReferralEndDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID = 1  begin 
  	set @BusEstBeforeDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @BusEstSinceDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 1  begin 
  	set @NoCounselingStartDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select PreviousDay from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 2  begin
  	set @StartDate = (Select PreviousWeekStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select PreviousWeekEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 2  begin
  	set @EventStartDate = (Select PreviousWeekStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select PreviousWeekEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 2  begin
  	set @ReferralStartDate = (Select PreviousWeekStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select PreviousWeekEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID  = 2  begin
  	set @BusEstBeforeDate= (Select PreviousWeekStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select PreviousWeekEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 2  begin 
  	set @NoCounselingStartDate = (Select PreviousWeekStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select PreviousWeekEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 3  begin
  	set @StartDate = (Select PreviousMonthStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select PreviousMonthEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 3  begin
  	set @EventStartDate = (Select PreviousMonthStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select PreviousMonthEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 3  begin
  	set @ReferralStartDate = (Select PreviousMonthStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select PreviousMonthEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID = 3  begin
  	set @BusEstBeforeDate = (Select PreviousMonthStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select PreviousMonthEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 3  begin 
  	set @NoCounselingStartDate = (Select PreviousMonthStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select PreviousMonthEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 4  begin
  	set @StartDate = (Select PreviousQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select PreviousQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 4  begin
  	set @EventStartDate = (Select PreviousQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select PreviousQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 4  begin
  	set @ReferralStartDate = (Select PreviousQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select PreviousQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID = 4  begin
  	set @BusEstBeforeDate = (Select PreviousQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select PreviousQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 4  begin 
  	set @NoCounselingStartDate = (Select PreviousQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select PreviousQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 5  begin
  	set @StartDate = (Select PreviousYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select PreviousYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 5  begin
  	set @EventStartDate = (Select PreviousYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select PreviousYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 5  begin
  	set @ReferralStartDate = (Select PreviousYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select PreviousYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID  = 5  begin
  	set @BusEstBeforeDate = (Select PreviousYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select PreviousYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 5  begin 
  	set @NoCounselingStartDate = (Select PreviousYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select PreviousYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 6  begin
  	set @StartDate = (Select CurrentQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select CurrentQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 6  begin
  	set @EventStartDate = (Select CurrentQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select CurrentQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 6  begin
  	set @ReferralStartDate = (Select CurrentQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select CurrentQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID = 6  begin
  	set @BusEstBeforeDate = (Select CurrentQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select CurrentQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 6  begin 
  	set @NoCounselingStartDate = (Select CurrentQuarterStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select CurrentQuarterEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 7  begin
  	set @StartDate = (Select CurrentYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EndDate = (Select CurrentYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeEventDateID = 7  begin
  	set @EventStartDate = (Select CurrentYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @EventEndDate = (Select CurrentYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeReferralDateID = 7  begin
  	set @ReferralStartDate = (Select CurrentYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @ReferralEndDate = (Select CurrentYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeBusEstDateID = 7  begin
  	set @BusEstBeforeDate = (Select CurrentYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )  
  	set @BusEstSinceDate = (Select CurrentYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  If @RelativeNoCounselingDateID = 7  begin 
  	set @NoCounselingStartDate = (Select CurrentYearStartDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) ) 
  	set @NoCounselingEndDate = (Select CurrentYearEndDate from [dbo].[FindAllRelativeDates]   (@date , @CurrMonth , @CurrYear , @CurrQuarter , @CurrDayofYear ) )
  end
  
  
  If @RelativeCounselingDateID = 8  begin
  	set @StartDate = '1/1/1900' 
  	set @EndDate = '12/31/2050' 
  end
  If @RelativeEventDateID = 8  begin
  	set @EventStartDate = '1/1/1900' 
  	set @EventEndDate = '12/31/2050' 
  end
  If @RelativeReferralDateID = 8  begin
  	set @ReferralStartDate = '1/1/1900' 
  	set @ReferralEndDate = '12/31/2050' 
  end
  If @RelativeBusEstDateID = 8  begin
  	set @BusEstBeforeDate = '1/1/1900' 
  	set @BusEstSinceDate = '12/31/2050' 
  end
  If @RelativeNoCounselingDateID = 8  begin
  	set @NoCounselingStartDate = '1/1/1900' 
  	set @NoCounselingEndDate = '12/31/2050' 
  end
  
  declare @sql varchar(max);
  declare @interactionssql varchar(max);
  declare @tempStartDate datetime ;
  declare @tempEndDate datetime ;
  declare @tempSnapshotStartDate datetime ;
  declare @tempSnapshotEndDate datetime ;
  
  set @TempStartDate = @StartDate
  set @TempEndDate = @EndDate
  set @TempSnapshotStartDate = @SnapshotStartDate
  set @TempSnapshotEndDate = @SnapshotEndDate
  
  if @StartDate is null  and @RelativeCounselingDateID is null begin set @TempStartDate = '1/1/1900' end
  if (@EndDate is null  and @RelativeCounselingDateID is null )begin set @TempEndDate = '12/31/2050' end
  if @SnapshotStartDate is null  begin set @TempSnapshotStartDate = '1/1/1900' end
  if @SnapshotEndDate is null  begin set @TempSnapshotEndDate = '12/31/2050' end
  
  -------------------------------------------------------------------------------------------------------
  -------  Build the SQL statement to select the filter set members
  -------------------------------------------------------------------------------------------------------
  set @sql= N' INSERT INTO #ClientScoutMembers(ClientID, PersonID )  (SELECT person.clientID, person.PersonID FROM dbo.person Left Outer Join dbo.surveysentsurvey on person.personid = surveysentsurvey.personid Left Outer Join dbo.interaction ON person.PersonID = interaction.PersonID Left OUTER JOIN dbo.client ON person.clientID = client.ClientID LEFT OUTER JOIN dbo.OptIn ON person.email = Optin.email LEFT OUTER JOIN dbo.EventAttCountsByPerson ON person.personid = EventAttCountsByPerson.personid LEFT OUTER JOIN dbo.EventAttByEvent on person.personID = EventAttByEvent.personid ' 
  	+ CASE when @EventStartDate is not null  then N' INNER JOIN dbo.EventAttByPersonDateRange(''' + convert(varchar(50),@EventStartDate) + ''', ''' + convert(varchar(50),@EventEndDate)  + ''') AS EventAttByDate ON person.personid = EventAttByDate.personID ' else N' ' end 
  	+ CASE when @ReferralStartDate is not null  then N' INNER JOIN dbo.ReferralsByDateRange(''' + convert(varchar(50),@ReferralStartDate) + ''', ''' + convert(varchar(50),@ReferralEndDate)  + ''', ''' + @IncludeUserOrgIDs+ ''') AS ReferralsByDate ON person.personid = ReferralsByDate.personID ' else N' ' end				
  	+ CASE when @PerClientCounselingHours is not null  then N' INNER JOIN dbo.PerClientCounselingGoalMembers(''' + convert(varchar(50),@TempStartDate) + ''', ''' + convert(varchar(50),@TempEndDate) + ''', ''' + convert(varchar(10),@PerClientCounselingHours) + ''', ''' + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS CounselingGoalMembers_1 ON client.ClientID = CounselingGoalMembers_1.clientID ' else N' ' end 
  	+ CASE when @PerPersonCounselingHours is not null  then N' INNER JOIN dbo.PerPersonCounselingGoalMembers(''' + convert(varchar(50),@TempStartDate) + ''', ''' + convert(varchar(50),@TempEndDate) + ''', ''' + convert(varchar(10),@PerPersonCounselingHours) + ''', ''' + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS CounselingGoalMembers_2 ON person.PersonID = CounselingGoalMembers_2.PersonID ' else N' ' end 
  	+ CASE when @SnapshotStartDate is not null  then N' INNER JOIN dbo.PerPersonSnapShotDateMembers(''' + convert(varchar(50),@SnapshotStartDate) + ''', ''' + convert(varchar(50),@SnapshotEndDate) + ''', '''  + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS PerPersonSnapShotDateMembers ON person.personID = PerPersonSnapShotDateMembers.personID ' else N' ' end 
  	+ CASE when @PrepHours is not null  then N' INNER JOIN dbo.PerClientPrepGoalMembers(''' + convert(varchar(50),@TempStartDate) + ''', ''' + convert(varchar(50),@TempEndDate) + ''', ''' + convert(varchar(10),@PrepHours) + ''', ''' + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS PrepGoalMembers ON client.ClientID = PrepGoalMembers.clientID ' else N' ' end 
  	+ CASE when @TravelHours is not null  then N' INNER JOIN dbo.PerClientTravelGoalMembers(''' + convert(varchar(50),@TempStartDate) + ''', ''' + convert(varchar(50),@TempEndDate) + ''', ''' + convert(varchar(10),@TravelHours) + ''', ''' + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS TravelGoalMembers ON client.ClientID = TravelGoalMembers.clientID ' else N' ' end 
  	+ CASE when @CounselingHours is not null  then N' INNER JOIN dbo.CounselingGoalMembers(''' + convert(varchar(50),@TempStartDate) + ''', ''' + convert(varchar(50),@TempEndDate) + ''', ''' + convert(varchar(10),@CounselingHours) + ''', ''' + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS CounselingMembers ON client.ClientID = CounselingMembers.clientID ' else N' ' end 
  	+ CASE when @NoCounselingStartDate is not null  then N' INNER JOIN dbo.NoCounselingMembers(''' + convert(varchar(50),@NoCounselingStartDate) + ''', ''' + convert(varchar(50),@NoCounselingEndDate) + ''', '''  + convert(varchar(max),@IncludeUserOrgIDs)+ ''') AS NoCounselingMembers ON client.ClientID = NoCounselingMembers.clientID ' else N' ' end 
  	+ ' LEFT OUTER JOIN dbo.SnapshotSummaryPerClient ON Client.ClientID = SnapshotSummaryPerClient.ClientID Where 1 = 1'
  	+ CASE when @RaceID is not null THEN N' AND person.RaceID  IN(Select param from dbo.MVParam(''' + @RaceID + ''', '','') ) ' ELSE N'' END
  	+ CASE when @GenderID is not null then N' AND person.GenderID IN(SELECT Param FROM dbo.MVParam(''' + @GenderID + ''', '',''))' ELSE N'' END
  	+ CASE when @EthnicityID is not null then N' AND person.EthnicityID IN(SELECT Param FROM dbo.MVParam(''' + @EthnicityID + ''', '',''))' ELSE N'' END
  	+ CASE when @DisabledID is not null then N' AND person.DisabledID IN(SELECT Param FROM dbo.MVParam(''' + @DisabledID + ''', '',''))' ELSE N'' END
  	+ CASE when @MilitaryID is not null then N' AND person.MilitaryStatusID IN(SELECT Param FROM dbo.MVParam(''' + @MilitaryID + ''', '',''))' ELSE N'' END
  	+ CASE when @VeteranID is not null then N' AND person.VeteranStatusID IN(SELECT Param FROM dbo.MVParam(''' + @VeteranID + ''', '',''))' ELSE N'' END
  	+ CASE when @ClientStatusID is not null then N' AND client.ClientStatusID IN(SELECT Param FROM dbo.MVParam(''' + @ClientStatusID + ''', '',''))' ELSE N'' END
  	+ CASE when @ClientInterfaceTypeID is not null then N' AND client.ClientInterfaceTypeID IN(SELECT Param FROM dbo.MVParam(''' + @ClientInterfaceTypeID + ''', '',''))' ELSE N'' END
  	+ CASE when @BusEstStartYear is not null then N' AND client.BusEstYear between ' + CONVERT(varchar(4), @BusEstStartYear) + ' AND ' +  CONVERT(varchar(4), @BusEstEndYear) ELSE N'' END
  	+ CASE when @StartDate is not null then N' AND interaction.InteractDate between ''' +  CONVERT(varchar(25),@StartDate) + ''' AND ''' +    CONVERT(varchar(25),@EndDate) +''' ' ELSE N'' END
  	+ CASE when @TypeOfBusiness is not null then N' AND client.SBABusinessTypeID IN(SELECT Param FROM dbo.MVParam(''' + @TypeOfBusiness + ''', '',''))' ELSE N'' END
  	+ CASE when @AssignedCounselorID is not null then N' AND client.CounselorID IN(SELECT Param FROM dbo.MVParam(''' + @AssignedCounselorID + ''', '',''))' ELSE N'' END
  	+ CASE when @OwnerGenderID is not null then N' AND client.OwnerGenderID IN(SELECT Param FROM dbo.MVParam(''' + @OwnerGenderID + ''', '',''))' ELSE N'' END
  	+ CASE when @OwnerRaceID is not null then N' AND client.OwnerRaceID IN(SELECT Param FROM dbo.MVParam(''' + @OwnerRaceID + ''', '',''))' ELSE N'' END
  	+ CASE when @OwnerVeteranStatusID is not null then N' AND client.OwnerVeteranStatusID IN(SELECT Param FROM dbo.MVParam(''' + @OwnerVeteranStatusID + ''', '',''))' ELSE N'' END
  	+ CASE when @ZipCode is not null then N' AND (client.Zip IN(SELECT Param FROM dbo.MVParam(''' + @ZipCode + ''', '','')) OR person.Zip IN(SELECT Param FROM dbo.MVParam(''' + @ZipCode + ''', '',''))) ' ELSE N'' END
  	+ CASE when @City is not null then N' AND (client.City IN(SELECT Param FROM dbo.MVParam(''' + @City + ''', '','')) OR person.City IN(SELECT Param FROM dbo.MVParam(''' + @City + ''', '','')))' ELSE N'' END
  	+ CASE when @State is not null then N' AND (client.State IN(SELECT Param FROM dbo.MVParam(''' + @State + ''', '','')) OR person.State IN(SELECT Param FROM dbo.MVParam(''' + @State + ''', '','')))' ELSE N'' END
  	+ CASE when @County is not null then N' AND (client.County IN(SELECT Param FROM dbo.MVParam(''' + @County + ''', '',''))OR person.County IN(SELECT Param FROM dbo.MVParam(''' + @County + ''', '','')))' ELSE N'' END
  	+ CASE when @EntityTypeID is not null then N' AND client.EntityTypeID IN(SELECT Param FROM dbo.MVParam(''' + @EntityTypeID + ''', '',''))' ELSE N'' END
  	+ CASE when @ClientTypeID is not null then N' AND client.ClientTypeID IN(SELECT Param FROM dbo.MVParam(''' + @ClientTypeID + ''', '',''))' ELSE N'' END
  	+ CASE when @LanguageID is not null then N' AND person.LanguageID IN(SELECT Param FROM dbo.MVParam(''' + @LanguageID + ''', '',''))' ELSE N'' END
  	+ CASE when @CounselorInteractionID is not null then N' AND interaction.CounselorID IN(SELECT Param FROM dbo.MVParam(''' + @CounselorInteractionID + ''', '',''))' ELSE N'' END
  	+ CASE when @InteractionTypeID is not null then N' AND interaction.InteractTypeID IN(SELECT Param FROM dbo.MVParam(''' + @InteractionTypeID + ''', '',''))' ELSE N'' END
  	+ CASE when @InBusiness =1 then N' AND client.InBusiness =1' ELSE N'' END
  	+ CASE when @Owner =1 then N' AND client.Owner =1' ELSE N'' END
  	+ CASE when @eCommerce =1 then N' AND client.eCommerce =1' ELSE N'' END
  	+ CASE when @HomeBased =1 then N' AND client.HomeBased =1' ELSE N'' END
  	+ CASE when @SurveyPanel =1 then N' AND person.SurveyPanel =''1''' ELSE N'' END
  	+ CASE when @ObstHealth =1 then N' AND SnapshotSummaryPerClient.ObstHealth =1' ELSE N'' END
  	+ CASE when @ObstTime =1 then N' AND SnapshotSummaryPerClient.ObstTime =1' ELSE N'' END
  	+ CASE when @ObstMarket =1 then N' AND SnapshotSummaryPerClient.ObstMarket =1' ELSE N'' END
  	+ CASE when @ObstLocation =1 then N' AND SnapshotSummaryPerClient.ObstLocation =1' ELSE N'' END
  	+ CASE when @ObstFinancing =1 then N' AND SnapshotSummaryPerClient.ObstFinancing =1' ELSE N'' END
  	+ CASE when @ObstExperience =1 then N' AND SnapshotSummaryPerClient.ObstExperience =1' ELSE N'' END
  	+ CASE when @ObstFamily =1 then N' AND SnapshotSummaryPerClient.ObstFamily =1' ELSE N'' END
  	+ CASE when @ObstStaffing =1 then N' AND SnapshotSummaryPerClient.ObstStaffing =1' ELSE N'' END
  	+ CASE when @ObstGvtReg =1 then N' AND SnapshotSummaryPerClient.ObstGvtReg =1' ELSE N'' END
  	+ CASE when @ObstLanguage =1 then N' AND SnapshotSummaryPerClient.ObstLanguage =1' ELSE N'' END
  	+ CASE when @ObstCredit =1 then N' AND SnapshotSummaryPerClient.ObstCredit =1' ELSE N'' END
  	+ CASE when @AssistanceRequested is not null then N' AND interaction.ServiceTypeID IN(SELECT Param FROM dbo.MVParam(''' + @AssistanceRequested + ''', '',''))' ELSE N'' END
  	+ CASE when @AnnualRevenue is not null then N' AND SnapshotSummaryPerClient.Revenue >=  ''' +convert(varchar(50),@AnnualRevenue) + ''' '  ELSE N' ' END
  	+ CASE when @AnnualProfit is not null then N' AND SnapshotSummaryPerClient.Profit >= ''' +convert(varchar(50),@AnnualProfit) + ''' '  ELSE N' ' END
  	+ CASE when @AnnualLoss is not null then N' AND SnapshotSummaryPerClient.Loss >= ''' +convert(varchar(50),@AnnualLoss) + ''' '  ELSE N' ' END
  	+ CASE when @TotalEmployees is not null then N' AND SnapshotSummaryPerClient.Employees >= ''' +convert(varchar(50),@TotalEmployees) + ''' '  ELSE N' ' END
  	+ CASE when @JobsCreated is not null then N' AND SnapshotSummaryPerClient.JobsCreated >= ''' +convert(varchar(50),@JobsCreated) + ''' '  ELSE N' ' END
  	+ CASE when @JobsSaved is not null then N' AND SnapshotSummaryPerClient.JobsSaved >= ''' +convert(varchar(50),@JobsSaved) + ''' '  ELSE N' ' END
  	+ CASE when @FinancingReceived is not null then N' AND SnapshotSummaryPerClient.SBALoanAmount >= ''' +convert(varchar(50),@FinancingReceived) + ''' '  ELSE N' ' END
  	+ CASE when @OptInStatus =1 then N' AND OptIn.OptInStatus =1' ELSE N'' END
  	+ CASE when @OptInStatus = -1 then N' AND (OptIn.OptInStatus =-1 or OptIn.OptInStatus is null) ' ELSE N'' END
  	+ CASE when @EmailOkToSend =1 then N' AND (person.EmailOKToSend =1) and (person.email is not null) and (person.email <> '''')' ELSE N'' END
  	+ CASE when @ValidPhysicalAddress =1 then N' AND ((person.persontype = ''con'' and client.address1 is not null and client.address1 <> '''' and client.city is not null and client.city <> '''' and client.[state] is not null and client.[state] <> '''' and client.zip is not null and client.zip <> '''') 
  												OR (person.persontype in (''att'', ''ind'') and person.address1 is not null and person.address1 <> '''' and person.city is not null and person.city <> '''' and person.[state] is not null and person.[state] <> '''' and person.zip is not null and person.zip <> ''''))' ELSE N'' END	
  	+ CASE when @EventsAttended is not null then N' AND EventAttCountsByPerson.EventsAttended >= ''' +convert(varchar(10),@EventsAttended) + ''' '  ELSE N' ' END
  	+ CASE when @EventAttendedID is not null then N' AND EventAttByEvent.EventID IN(SELECT Param FROM dbo.MVParam(''' + @EventAttendedID + ''', '',''))' ELSE N'' END
  	+ CASE when @SurveyID is not null then N' AND surveysentsurvey.SurveyID IN(SELECT Param FROM dbo.MVParam(''' + @SurveyID + ''', '',''))' ELSE N'' END
  	+ CASE when @PrimaryPerson =1 then N' AND client.PrimaryPersonID =person.PersonID' ELSE N'' END
  	+ CASE when @IndustryTypeID is not null then N' AND Client.SBABusinessTypeID IN(SELECT Param FROM dbo.MVParam(''' + @IndustryTypeID + ''', '',''))' ELSE N'' END
  	+ CASE when @ThreeDigitNAICSID is not null then N' AND left(Client.NAICSID,3) IN(SELECT Param FROM dbo.MVParam(''' + @ThreeDigitNAICSID + ''', '',''))' ELSE N'' END
  	+ CASE when @BusEstBeforeDate is not null then N' AND (CONVERT (varchar(2), BusEstMonth) + CONVERT (varchar(4), ''01'') + CONVERT (varchar(4), BusEstYear))  < ' +  CONVERT(varchar(25),@BusEstBeforeDate)  ELSE N'' END
  	+ CASE when @BusEstSinceDate is not null then N' AND (CONVERT (varchar(2), BusEstMonth) + CONVERT (varchar(4), ''01'') + CONVERT (varchar(4), BusEstYear))  >+ ' +  CONVERT(varchar(25),@BusEstSinceDate)  ELSE N'' END
     	+ CASE when @FirstContactStartDate is not null then N' AND client.FirstContactDate  between '''+ CONVERT(varchar(25),@FirstContactStartDate) + '''   and   ''' + CONVERT(varchar(25),@FirstContactEndDate) + ''' '   ELSE N'' END
  	+ CASE when @FollowUpResultID is not null then N' AND interaction.FollowUpResultID IN(SELECT Param FROM dbo.MVParam(''' + @FollowUpResultID + ''', '',''))' ELSE N'' END
  	+ CASE when @PersonType is not null then N' AND person.persontype IN(SELECT Param FROM dbo.MVParam(''' + @PersonType + ''', '',''))' ELSE N'' END
  	+ N' AND person.UserOrgID  IN(Select param from dbo.MVParam(''' + @IncludeUserOrgIDs + ''', '','') )   and person.deleted <> 1 '
  	+ N' GROUP BY person.UserOrgID, person.clientID, person.PersonID)';
  
  EXEC  (@sql);
  
  
  -------------------------------------------------------------------------------------------------------
  --- This code handles the include person and exclude person parms
  -------------------------------------------------------------------------------------------------------
  if @IncludePersonID is not null begin
  	Insert Into #ClientScoutMembers (PersonID) 
  	SELECT  Param  
  	FROM dbo.MVParam(@IncludePersonID,',') A left outer join #ClientScoutMembers on A.Param = #ClientScoutMembers.PersonID 
  	WHERE #ClientScoutMembers.PersonID is Null;
  end
  
  if @ExcludePersonID is not null begin
  	DELETE FROM [#ClientScoutMembers]
  	FROM [#ClientScoutMembers] INNER JOIN
  		(SELECT Param
  		FROM dbo.MVParam(@ExcludePersonID, ',') AS MVParam_1) AS derivedtbl_1 ON derivedtbl_1.Param = [#ClientScoutMembers].PersonID;
  end
  
  
  	If @ReturnOption = 1 begin GOTO ReturnOption1 end
  	If @ReturnOption = 2 begin GOTO ReturnOption2 end
  	If @ReturnOption = 3 begin GOTO ReturnOption3 end
  	If @ReturnOption = 4 begin GOTO ReturnOption4 end
  	If @ReturnOption = 10 begin GOTO ReturnOption10 end
  	If @ReturnOption = 11 begin GOTO ReturnOption11 end
  	If @ReturnOption = 12 begin GOTO ReturnOption12 end
  	If @ReturnOption = 13 begin GOTO ReturnOption13 end
  	If @ReturnOption = 14 begin GOTO ReturnOption14 end
  	If @ReturnOption = 15 begin GOTO ReturnOption15 end
  	If @ReturnOption = 24 begin GOTO ReturnOption24 end
  	GOTO ExitTag
  
  
  
  -------------------------------------------------------------------------------------------------------
  ---     Return Option 1 - Basic Client Scout Interaction Activity View
  -------------------------------------------------------------------------------------------------------
  ReturnOption1:
  SELECT     person.clientID, InteractionsWithinDateRange_1.TimeSpent AS CounselingTime, InteractionsWithinDateRange_1.PrepTime, 
                        InteractionsWithinDateRange_1.TravelTime, InteractionsWithinDateRange_1.InteractionID, client.PrevSysClientNo, person.PersonID, 
                        InteractionsWithinDateRange_1.InteractDate, client.Company, person.LastName + ', ' + person.FirstName AS FullName, person.RaceID, 
                        person.GenderID, person.EthnicityID, person.DisabledID, person.MilitaryStatusID, person.VeteranStatusID, LEFT(InteractionsWithinDateRange_1.Notes, 1024), 
                        counselor.LastName + ', ' + counselor.FirstName AS CounselorName, ownerveteranstatus_1.VeteranStatus AS OwnerVeteranStatus, 
                        ownergender.OwnerGender, ownerrace_1.Race AS OwnerRace, gender.Gender, ownerdisabled.OwnerDisabled AS Disabled, 
                        ownerethnicity.OwnerEthnicity, ownerrace.Race AS PersonRace, CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address1 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address1 ELSE person.Address1 END) 
                        END AS Address1, CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address2 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address2 ELSE person.Address2 END) 
                        END AS Address2, CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.City ELSE (CASE WHEN person.Address1 = '' THEN Client.City ELSE person.City END) END AS City, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.State ELSE (CASE WHEN person.Address1 = '' THEN Client.State ELSE person.State END) END AS State, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Zip ELSE (CASE WHEN person.Address1 = '' THEN Client.Zip ELSE person.Zip END) END AS Zip, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.County ELSE (CASE WHEN person.Address1 = '' THEN Client.County ELSE person.County END) 
                        END AS County, SnapshotSummaryPerClient.FirstSnapshot, SnapshotSummaryPerClient.LastSnapshot, SnapshotSummaryPerClient.SnapshotCount, 
                        SnapshotSummaryPerClient.Employees, SnapshotSummaryPerClient.JobsSaved, SnapshotSummaryPerClient.Revenue, 
                        SnapshotSummaryPerClient.Profit, SnapshotSummaryPerClient.Loss, SnapshotSummaryPerClient.JobsCreated, SnapshotSummaryPerClient.SBALoan, 
                        SnapshotSummaryPerClient.NonSBALoan, SnapshotSummaryPerClient.MicroLoan, SnapshotSummaryPerClient.SBIR, 
                        SnapshotSummaryPerClient.EquityFunding, SnapshotSummaryPerClient.SelfFunding, SnapshotSummaryPerClient.GovernmentContract, 
                        ownerveteranstatus.VeteranStatus, person.persontype, 
                        CASE WHEN person.persontype = 'con' THEN client.Phone ELSE person.HomePhone END AS Phone, 
                        CASE WHEN person.persontype = 'con' THEN client.Fax ELSE person.Fax END AS Fax, person.Email, person.Address1 AS PAddress1, 
                        person.Address2 AS PAddress2, person.City AS PCity, person.State AS PState, person.Zip AS PZip, person.County AS PCounty, 
                        servicetype.ServiceType, naics.NAICSID, naics.NAICSTitle, naicsThreeDigitCodes.category, naicsThreeDigitCodes.Title, followupresult.FollowupResult, 
                        persontype.persontypedesc, ownerethnicity_1.OwnerEthnicity AS Ethnicity, 
                        counselor_1.LastName + ', ' + counselor_1.FirstName AS AssignedCounselor
  FROM         dbo.ownerethnicity RIGHT OUTER JOIN
                        dbo.counselor AS counselor_1 RIGHT OUTER JOIN
                        dbo.client ON counselor_1.CounselorID = client.CounselorID LEFT OUTER JOIN
                        dbo.ownergender ON client.OwnerGenderID = ownergender.OwnerGenderID LEFT OUTER JOIN
                        dbo.SnapshotSummaryPerClient ON client.ClientID = SnapshotSummaryPerClient.ClientID LEFT OUTER JOIN
                        dbo.naicsThreeDigitCodes RIGHT OUTER JOIN
                        dbo.naics ON naicsThreeDigitCodes.category = LEFT(naics.NAICSID, 3) ON client.NAICSID = naics.NAICSIndex LEFT OUTER JOIN
                        dbo.ownerrace AS ownerrace_1 ON client.OwnerRaceID = ownerrace_1.RaceID LEFT OUTER JOIN
                        dbo.ownerveteranstatus AS ownerveteranstatus_1 ON client.OwnerVeteranStatusID = ownerveteranstatus_1.VeteranStatusID ON 
                        ownerethnicity.OwnerEthnicityID = client.OwnerEthnicityID RIGHT OUTER JOIN
                        dbo.ownerveteranstatus RIGHT OUTER JOIN
                        dbo.gender RIGHT OUTER JOIN
                        dbo.ownerethnicity AS ownerethnicity_1 INNER JOIN
                        [#ClientScoutMembers] INNER JOIN
                        dbo.person ON person.PersonID = [#ClientScoutMembers].personid ON ownerethnicity_1.OwnerEthnicityID = person.EthnicityID LEFT OUTER JOIN
                        dbo.persontype ON person.persontype = persontype.persontype LEFT OUTER JOIN
                        dbo.ownerdisabled ON person.DisabledID = ownerdisabled.OwnerDisabledID ON gender.GenderID = person.GenderID ON 
                        dbo.ownerveteranstatus.VeteranStatusID = person.VeteranStatusID LEFT OUTER JOIN
                        dbo.ownerrace ON person.RaceID = ownerrace.RaceID LEFT OUTER JOIN
                        dbo.followupresult RIGHT OUTER JOIN
                        dbo.InteractionsWithinDateRange(@tempstartdate, @tempenddate, @IncludeUserOrgIDs) AS InteractionsWithinDateRange_1 ON 
                        followupresult.FollowupResultID = InteractionsWithinDateRange_1.FollowupResultID LEFT OUTER JOIN
                        dbo.counselor ON InteractionsWithinDateRange_1.CounselorID = counselor.CounselorID ON 
                        person.PersonID = InteractionsWithinDateRange_1.PersonID ON client.ClientID = person.clientID LEFT OUTER JOIN
                        dbo.servicetype ON InteractionsWithinDateRange_1.ServiceTypeID = servicetype.ServiceTypeID
  WHERE     (person.UserOrgID IN
                            (SELECT     Param
                              FROM          dbo.MVParam(@IncludeUserOrgIDs, ',') AS MVParam_1))
  ORDER BY  client.Company, InteractionsWithinDateRange_1.InteractDate
  
  option(recompile)
  GOTO ExitTag
  
  
  
  -------------------------------------------------------------------------------------------------------
  ---     Return Option 3 - Mailing Label Views
  -------------------------------------------------------------------------------------------------------
  ReturnOption3:
  SELECT     person.clientID, client.PrevSysClientNo, person.PersonID, client.Company, person.FirstName + ' ' + person.LastName AS FullName, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address1 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address1 ELSE person.Address1 END) 
                        END AS Address1, CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address2 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address2 ELSE person.Address2 END) 
                        END AS Address2, CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.City ELSE (CASE WHEN person.Address1 = '' THEN Client.City ELSE person.City END) END AS City, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.State ELSE (CASE WHEN person.Address1 = '' THEN Client.State ELSE person.State END) END AS State, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Zip ELSE (CASE WHEN person.Address1 = '' THEN Client.Zip ELSE person.Zip END) END AS Zip, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.County ELSE (CASE WHEN person.Address1 = '' THEN Client.County ELSE person.County END) 
                        END AS County, person.persontype, CASE WHEN person.persontype = 'con' THEN client.Phone ELSE person.HomePhone END AS Phone, 
                        CASE WHEN person.persontype = 'con' THEN client.Fax ELSE person.Fax END AS Fax, 
                        CASE WHEN person.persontype = 'con' THEN client.Email ELSE person.Email END AS Email, person.Address1 AS PAddress1, 
                        person.Address2 AS PAddress2, person.City AS PCity, person.State AS PState, person.Zip AS PZip, person.County AS PCounty, 
                        persontype.persontypedesc, person.LastName, person.FirstName
  FROM         client RIGHT OUTER JOIN
                        persontype RIGHT OUTER JOIN
                        [#ClientScoutMembers] INNER JOIN
                        person ON person.PersonID = [#ClientScoutMembers].personid ON persontype.persontype = person.persontype ON 
                        client.ClientID = person.clientID
  WHERE     (person.UserOrgID IN
                            (SELECT     Param
                              FROM          dbo.MVParam(@IncludeUserOrgIDs, ',') AS MVParam_1))
  GROUP BY person.clientID, client.PrevSysClientNo, person.PersonID, client.Company, person.FirstName + ' ' + person.LastName, person.persontype, 
                        person.Address1, person.Address2, person.City, person.State, person.Zip, person.County, persontype.persontypedesc, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address1 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address1 ELSE person.Address1 END) END, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Address2 ELSE (CASE WHEN person.Address1 = '' THEN Client.Address2 ELSE person.Address2 END) END, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.City ELSE (CASE WHEN person.Address1 = '' THEN Client.City ELSE person.City END) END, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.State ELSE (CASE WHEN person.Address1 = '' THEN Client.State ELSE person.State END) END, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.Zip ELSE (CASE WHEN person.Address1 = '' THEN Client.Zip ELSE person.Zip END) END, 
                        CASE WHEN person.persontype = 'att' OR
                        person.persontype = 'ind' THEN person.County ELSE (CASE WHEN person.Address1 = '' THEN Client.County ELSE person.County END) END, 
                        CASE WHEN person.persontype = 'con' THEN client.Phone ELSE person.HomePhone END, 
                        CASE WHEN person.persontype = 'con' THEN client.Fax ELSE person.Fax END, 
                        CASE WHEN person.persontype = 'con' THEN client.Email ELSE person.Email END, person.LastName, person.FirstName
  ORDER BY Zip, person.LastName, person.FirstName
  
  option(recompile)
  GOTO ExitTag
  
  
  
  -------------------------------------------------------------------------------------------------------
  ---     Return Option 4 - Header Block on Client Scout Views
  -------------------------------------------------------------------------------------------------------
  ReturnOption4:
  SELECT     CASE WHEN client.individual = '1' THEN 'Individual' ELSE (CASE WHEN client.individual IS NULL 
                        THEN 'Event Attendee' ELSE 'Company' END) END AS ClientCategory, 
  			COUNT(DISTINCT CASE WHEN client.individual IS NOT NULL 
                        THEN client.clientid ELSE NULL END) AS CompanyCount, 
  			COUNT(DISTINCT person.PersonID) AS PersonCount
  FROM         client RIGHT OUTER JOIN
                        [#ClientScoutMembers] INNER JOIN
                        person ON person.PersonID = [#ClientScoutMembers].personid ON client.ClientID = person.clientID
  GROUP BY CASE WHEN client.individual = '1' THEN 'Individual' ELSE (CASE WHEN client.individual IS NULL 
                        THEN 'Event Attendee' ELSE 'Company' END) END
  
  option(recompile)				  
  GOTO ExitTag
  
  
  -------------------------------------------------------------------------------------------------------
  ---     Return Option 10 - EX610(Company Info) on Client Scout Views
  -------------------------------------------------------------------------------------------------------
  ReturnOption10:
  SELECT     person_1.clientID, client.Company, counselor.LastName + ', ' + counselor.FirstName AS Counselor, person.LastName AS [Primary Last Name], 
                        person.FirstName AS [Primary First Name], client.Address1, client.Address2, client.City, client.State, client.Zip, client.County, client.Phone, client.Fax, 
                        client.WebURL, entitytype.EntityDescript AS [Entity Type], clientstatus.ClientStatus, clienttype.ClientType, 
                        CASE WHEN Client.AggReportPerm = 1 THEN 'Yes' ELSE (CASE WHEN Client.AggReportPerm = 0 THEN 'No Response' ELSE 'No' END) 
                        END AS AggReportPerm, 
                        CASE WHEN Client.ReferralPerm = 1 THEN 'Yes' ELSE (CASE WHEN Client.ReferralPerm = 0 THEN 'No Response' ELSE 'No' END) 
                        END AS ReferralPerm, CASE WHEN Client.InBusiness = 1 THEN 'Yes' ELSE 'No' END AS InBusiness, naics.NAICSID, naics.NAICSTitle, 
                        client.BusEstMonth, client.BusEstYear, ownerpriorstatus.OwnerPriorStatus, ownerbusstarted.OwnerBusStarted AS [Other Businesses Started], 
                        exposure.ExposureDesc AS [Incoming Referral Type], CASE WHEN eCommerce = 1 THEN 'Yes' ELSE 'No' END AS eCommerce, 
                        CASE WHEN HomeBased = 1 THEN 'Yes' ELSE 'No' END AS HomeBased, 
                        CASE WHEN OwnerPurchaseBusiness = 1 THEN 'Yes' ELSE 'No' END AS OwnerPurchaeBusiness, 
                        ownergender.OwnerGender AS [Gender(s) of Ownership], ownerveteranstatus.VeteranStatus, ownerrace.Race, client.FirstContactDate,
                        client.Latitude, client.Longitude, industry.industry, industry.industryID
  FROM         client LEFT OUTER JOIN
                        ownerrace ON client.OwnerRaceID = ownerrace.RaceID LEFT OUTER JOIN
                        person ON client.PrimaryPersonID = person.PersonID LEFT OUTER JOIN
                        counselor ON client.CounselorID = counselor.CounselorID LEFT OUTER JOIN
                        ownerpriorstatus ON client.OwnerPriorStatusID = ownerpriorstatus.OwnerPriorStatusID LEFT OUTER JOIN
                        ownerbusstarted ON client.OwnerBusStartedID = ownerbusstarted.OwnerBusStartedID LEFT OUTER JOIN
                        naics ON client.NAICSID = naics.NAICSIndex LEFT OUTER JOIN
                        industry ON client.IndustryID = Industry.IndustryID LEFT OUTER JOIN
                        ownergender ON client.OwnerGenderID = ownergender.OwnerGenderID LEFT OUTER JOIN
                        ownerveteranstatus ON client.OwnerVeteranStatusID = ownerveteranstatus.VeteranStatusID LEFT OUTER JOIN
                        clienttype ON client.ClientTypeID = clienttype.ClientTypeID LEFT OUTER JOIN
                        entitytype ON client.EntityTypeID = entitytype.EntityID LEFT OUTER JOIN
                        clientstatus ON client.ClientStatusID = clientstatus.ClientStatusID LEFT OUTER JOIN
                        ownerstartreason ON client.OwnerStartReasonID = ownerstartreason.OwnerStartReasonID LEFT OUTER JOIN
                        exposure ON client.ExposureID = exposure.ExposureID RIGHT OUTER JOIN
                        [#ClientScoutMembers] INNER JOIN
                        person AS person_1 ON person_1.PersonID = [#ClientScoutMembers].personid ON client.ClientID = person_1.clientID
  GROUP BY person_1.clientID, client.Company, counselor.LastName + ', ' + counselor.FirstName, person.LastName, person.FirstName, client.Address1, 
                        client.Address2, client.City, client.State, client.Zip, client.County, client.Phone, client.Fax, client.WebURL, entitytype.EntityDescript, 
                        clientstatus.ClientStatus, clienttype.ClientType, naics.NAICSID, naics.NAICSTitle, client.BusEstMonth, client.BusEstYear, 
                        ownerpriorstatus.OwnerPriorStatus, ownerbusstarted.OwnerBusStarted, exposure.ExposureDesc, ownergender.OwnerGender, 
                        ownerveteranstatus.VeteranStatus, ownerrace.Race, 
                        CASE WHEN Client.AggReportPerm = 1 THEN 'Yes' ELSE (CASE WHEN Client.AggReportPerm = 0 THEN 'No Response' ELSE 'No' END) END, 
                        CASE WHEN Client.ReferralPerm = 1 THEN 'Yes' ELSE (CASE WHEN Client.ReferralPerm = 0 THEN 'No Response' ELSE 'No' END) END, 
                        CASE WHEN Client.InBusiness = 1 THEN 'Yes' ELSE 'No' END, CASE WHEN eCommerce = 1 THEN 'Yes' ELSE 'No' END, 
                        CASE WHEN HomeBased = 1 THEN 'Yes' ELSE 'No' END, CASE WHEN OwnerPurchaseBusiness = 1 THEN 'Yes' ELSE 'No' END, 
                        client.FirstContactDate, client.Latitude, client.Longitude, industry.industry, industry.industryID
  ORDER BY client.Company
  
  option(recompile)
  GOTO ExitTag
  
  
  -------------------------------------------------------------------------------------------------------
  ---     Return Option 11 - EX611(People Info) on Client Scout Views
  -------------------------------------------------------------------------------------------------------
  ReturnOption11:
  SELECT     person.clientID, person.PersonID, person.LastName, person.FirstName, person.LastName + ', ' + person.FirstName AS PersonName, client.Company, 
                        counselor.LastName + ', ' + counselor.FirstName AS CounselorName, person.Address1, person.Address2, person.City, person.State, person.Zip, 
                        person.County, person.Email, person.WorkPhone, person.CellPhone, person.HomePhone, person.Fax, person.BirthDate, 
                        language.LanguageStr AS [Language Preference], gender.Gender, ownerrace.Race, ownerethnicity.OwnerEthnicity AS Ethnicity, 
                        ownermilitarystatus.OwnerMilitaryStatus AS [Military Status], ownerveteranstatus.VeteranStatus, ownerdisabled.OwnerDisabled AS [Disability Status], 
                        ownereducationbackground.EducationBackground, ownerhhincome.OwnerHHIncome AS [HH Income], 
                        ownermaritalstatus.OwnerMaritalStatus AS [Marital Status], person.Owner, person.OwnerParents, person.HeadofHH, person.HHMembers
  FROM         person INNER JOIN
                        [#ClientScoutMembers] ON [#ClientScoutMembers].PersonID = person.PersonID INNER JOIN
                        client ON person.clientID = client.ClientID LEFT OUTER JOIN
                        language ON person.LanguageID = language.LanguageID LEFT OUTER JOIN
                        ownermaritalstatus ON person.MaritalStatusID = ownermaritalstatus.OwnerMaritalStatusID LEFT OUTER JOIN
                        ownerhhincome ON person.HHIncomeID = ownerhhincome.OwnerHHIncomeID LEFT OUTER JOIN
                        ownereducationbackground ON person.EducationBackgroundID = ownereducationbackground.EducationBackgroundID LEFT OUTER JOIN
                        ownerdisabled ON person.DisabledID = ownerdisabled.OwnerDisabledID LEFT OUTER JOIN
                        ownerveteranstatus ON person.VeteranStatusID = ownerveteranstatus.VeteranStatusID LEFT OUTER JOIN
                        ownermilitarystatus ON person.MilitaryStatusID = ownermilitarystatus.OwnerMilitaryStatusID LEFT OUTER JOIN
                        ownerethnicity ON person.EthnicityID = ownerethnicity.OwnerEthnicityID LEFT OUTER JOIN
                        gender ON person.GenderID = gender.GenderID LEFT OUTER JOIN
                        ownerrace ON person.RaceID = ownerrace.RaceID LEFT OUTER JOIN
                        counselor ON client.CounselorID = counselor.CounselorID
  
  option(recompile)
  GOTO ExitTag
  
  
  ExitTag:

  
  

  • Reply
  • Quote