SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ME
-- Create date: July 16, 2009
-- Description:	Gets different sets of touch point data
-- =============================================
ALTER PROCEDURE [dbo].[GetTouchPointData] 
	-- Add the parameters for the stored procedure here
	@Country nvarchar(50) = NULL,
	@Division nvarchar(50) = NULL,
	@District nvarchar(50) = NULL,
	@Year nvarchar(4),
	@Month nvarchar(2),
	@CustomerType nvarchar(50),
	@TouchPointDataCursor CURSOR VARYING OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	
	IF (@Country is not NULL AND @Division = '%' AND @District is NULL AND @CustomerType = 'Promoters')
	BEGIN
	    SET @TouchPointDataCursor = CURSOR
        FORWARD_ONLY STATIC FOR
          SELECT main.Country Country,
                 op."Opportunity 1" Opportunity, 
                 count(main.Tone) ToneCount,
                 main.Tone Tone,
                 main.Month Month,
                 main.Year Year,
                 'Promoters' "Customer Type"
            FROM ( SELECT dis.Country Country,
                          dis.[ES/FS] [ES/FS],
                          tone.Tone Tone,
                          datepart(month,sp.date_of_survey) Month,
                          datepart(year,sp.date_of_survey) Year,
                          ovs.opportunityID opportunityID,
                          sp.promoters promoters
                     FROM [Surveyed Pop] sp INNER JOIN Districts dis
                       ON sp.DES1 = dis.DES1 INNER JOIN OpportunityVSurvey ovs
                       ON sp.ID = ovs.SurveyID INNER JOIN [Tone of Customer] tone
                       ON ovs.ToneID = tone.ID
                    WHERE ovs.ToneID = 1 
                      AND datepart(year,sp.Date_of_Survey) = @Year
                      AND datepart(month,sp.Date_of_Survey) = @Month
                      AND (sp.tb0_10_Score Is Not Null) 
                      AND (dis."ES/FS" LIKE @Division )
                      AND (dis.Country = @Country)
                      AND sp.Promoters = 1
                 ) main RIGHT OUTER JOIN Opportunities op
              ON main.OpportunityID = op.ID
        Group By main.Country,
                 op."Opportunity 1",
                 main.Tone,
                 main.Month,
                 main.Year,
                 main.Promoters
        Order by op."Opportunity 1";
        OPEN @TouchPointDataCursor;
    END 
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ME
-- Create date: July 16, 2009
-- Description:	populates touchpoints table
-- =============================================
ALTER PROCEDURE [dbo].[PopulateTouchPointsTable] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

    -- Insert statements for procedure here
	
	-- Delare our variables for the returned fields
	DECLARE @Country nvarchar(50), @Division nvarchar(50), @District nvarchar(50), @Opportunity nvarchar(50),
	        @ToneCount int, @Tone nvarchar(50), @Month nvarchar(50), @Year nvarchar(50), @CustomerType nvarchar(50)
	        
	DECLARE @MyCursor CURSOR
    EXEC dbo.GetTouchPointData @Country = 'U.S.A.', @Division = '%', @Year = '2007', @Month = '06', @CustomerType = 'Promoters', @TouchPointDataCursor = @MyCursor OUTPUT
    FETCH NEXT FROM @MyCursor
         INTO @Country, @Opportunity, @ToneCount, @Tone, @Month, @Year, @CustomerType
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
           
                                     
         Print @Country + ' ' + @Opportunity + ' ' + cast(@ToneCount as nvarchar) + ' ' + @Tone + ' ' + @Month + ' ' + @Year + ' ' + @CustomerType;
         
         FETCH NEXT FROM @MyCursor
         INTO @Country, @Opportunity, @ToneCount, @Tone, @Month, @Year, @CustomerType
    END
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
	        
END

U.S.A.	Ability to Meet Scheduling Needs	2	Positive	6	2007	Promoters
U.S.A.	Effective Communications	1	Positive	6	2007	Promoters
NULL	Effectiveness of Documentation	0	NULL	NULL	NULL	Promoters
U.S.A.	Effectiveness of Service	21	Positive	6	2007	Promoters
U.S.A.	Employee Development	18	Positive	6	2007	Promoters
U.S.A.	Pre-Call Preparation 	2	Positive	6	2007	Promoters
NULL	Price / Value	0	NULL	NULL	NULL	Promoters
NULL	Product Quality Reliability	0	NULL	NULL	NULL	Promoters
U.S.A.	Project Management	1	Positive	6	2007	Promoters
NULL	Repaired First Attempt	0	NULL	NULL	NULL	Promoters
U.S.A.	Speed of Response	2	Positive	6	2007	Promoters

Warning: Null value is eliminated by an aggregate or other SET operation.
U.S.A. Ability to Meet Scheduling Needs 2 Positive 6 2007 Promoters
U.S.A. Effective Communications 1 Positive 6 2007 Promoters
 
U.S.A. Effectiveness of Service 21 Positive 6 2007 Promoters
U.S.A. Employee Development 18 Positive 6 2007 Promoters
U.S.A. Pre-Call Preparation  2 Positive 6 2007 Promoters
 
 
U.S.A. Project Management 1 Positive 6 2007 Promoters
 
U.S.A. Speed of Response 2 Positive 6 2007 Promoters

(1 row(s) affected)