1
\$\begingroup\$

We collect customer surveys and one of the questions is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but I'll leave that discussion for a different time.)

We store the surveys in our database, and, since the questions change yearly, I've created this view to get me the critical data for calculating the NPS, an Overall Satisfaction metric that we also collect, and a few other general reporting needs.

CREATE VIEW [dbo].[SatSurvey] AS
  SELECT SSV1.ID, SSV1.ClinicID, CC.ShortName, NULL as HashValue, NULL as LoadDate, CollectionDate, RecommendNPS, RecommendNPSReason, OverallExperience, Comments, NULL as StartDateTime, NULL as EndDateTime, Method,
                 CorporateAffiliation, NULL as DoNotReport
    FROM SatSurveyV1 SSV1 inner join ClinicConfig CC on SSV1.ClinicID = cc.ClinicID
  UNION ALL
  SELECT SSV2.ID, SSV2.ClinicID, CC.ShortName, HashValue, LoadDate, CollectionDate, RecommendNPS, RecommendNPSReason, OverallExperience, Comments, StartDateTime, EndDateTime, Method,
                 CorporateAffiliation, DoNotReport
    FROM SatSurveyV2 SSV2 inner join ClinicConfig CC on SSV2.ClinicID = cc.ClinicID
    UNION ALL
    SELECT SSV2018.ID, SSV2018.ClinicID, cc.ShortName, HashValue, LoadDate, CollectionDate, RecommendNPS, RecommendNPSReason, OverallExperience, Comments, StartDateTime, EndDateTime, Method,
                 CorporateAffiliation, DoNotReport
      FROM SatSurveyV2018 SSV2018 inner join ClinicConfig CC on SSV2018.ClinicID = CC.ClinicID

Note: The HashValue, LoadDate, and DoNotReport columns were added after the first survey change, so they're selected as NULL for the earliest data.

I calculate the NPS score by selecting data from this view in code, but I've decided that it would be more efficient (especially since I now need to calculate a YTD score over multiple different periods) to return it from a Stored Procedure.

I've written this stored procedure to calculate the NPS, but it seems that there may well be a more efficient way of calculating it.

CREATE PROCEDURE [dbo].[CalculateNPSOverPeriod] (
      @NPS decimal (5,2) output,
      @startDate date,
      @endDate date,
      @clinicName nvarchar(50) = NULL)

AS
BEGIN

SET NOCOUNT ON;

SELECT @NPS = ((SUM(Promoters) - SUM(Detractors)) / (SUM(Responses) * 1.0) * 100.0)
  FROM (
        SELECT clinicid, YEAR(CollectionDate) Yr, MONTH(CollectionDate) Mo, COUNT(*) Promoters, 0 Neutrals, 0 Detractors, 0 Responses
          FROM SatSurvey
         WHERE CollectionDate >= @startDate 
           AND CollectionDate < DATEADD(d, 1, @endDate)
           AND RecommendNPS > 8
           AND DoNotReport IS NULL
        GROUP BY ClinicID, YEAR(CollectionDate), MONTH(CollectionDate)
/*
        UNION
        SELECT clinicid, YEAR(CollectionDate), MONTH(CollectionDate), 0 Promoters, COUNT(*)  Neutrals, 0 Detractors, 0 Responses
          FROM SatSurvey
         WHERE CollectionDate >= @startDate 
           AND CollectionDate < DATEADD(d, 1, @endDate)
           AND RecommendNPS BETWEEN 7 AND 8
           AND DoNotReport IS NULL
        GROUP BY ClinicID, YEAR(CollectionDate), MONTH(CollectionDate)
*/
        UNION
        SELECT clinicid, YEAR(CollectionDate), MONTH(CollectionDate), 0 Promoters, 0 Neutrals, COUNT(*) Detractors, 0 Responses
          FROM SatSurvey
         WHERE CollectionDate >= @startDate 
           AND CollectionDate < DATEADD(d, 1, @endDate)
           AND RecommendNPS < 7
           AND DoNotReport IS NULL
        GROUP BY ClinicID, YEAR(CollectionDate), MONTH(CollectionDate)
        UNION
        SELECT clinicid, YEAR(CollectionDate), MONTH(CollectionDate), 0 Promoters, 0 Neutrals, 0 Detractors, COUNT(*) Responses
          FROM SatSurvey
         WHERE CollectionDate >= @startDate 
           AND CollectionDate < DATEADD(d, 1, @endDate)
           AND RecommendNPS IS NOT NULL
           AND DoNotReport IS NULL
        GROUP BY ClinicID, YEAR(CollectionDate), MONTH(CollectionDate)
      ) p INNER JOIN ClinicConfig CC on p.ClinicID = cc.ClinicID
 WHERE cc.GenerateSatSurvey = 1
   AND (@clinicName IS NULL OR (cc.MidName LIKE @clinicName))
 OPTION (Recompile)

 RETURN @NPS

END

I know that the first thing I can do is pull the commented code (as well as all the 0 Neutrals, in the other SELECT statements) because it's not actually necessary to know the number of Neutral responses to calculate the NPS. I was using that previously as I was checking other code to ensure I was getting correct results and for troubleshooting.

  • Is there a better way of doing this than UNIONing all the individual sub-queries?
  • Since it's now going to be in a stored procedure where I can easily write some tSQL around it, would I be better off selecting Promoters, Detractors and Responses each in their own query then doing the math at the end?
  • Would I be best served by having indices on CollectionDate, RecommendNPS, DoNotReport, and ClinicID?
  • I recognize that having the output parameter listed first may be a bit unconventional, however, I've chosen to do that as I have one standard procedure that I use to call stored procedures and I decided that it would be easiest (perhaps lazy) to always have the output parameter (if there is one) be listed first. This is, likely, another refactoring that needs to be done, but is out of scope for this question.
\$\endgroup\$

1 Answer 1

Reset to default
2
\$\begingroup\$

I ended up getting some excellent help in chat (though it seems I couldn't talk anyone into posting an answer), and this is what I came up with:

CREATE PROCEDURE [dbo].[CalculateNPSOverPeriod] (
      @NPS decimal (5,2) output,
      @startDate date,
      @endDate date,
      @clinicName nvarchar(50) = NULL)

AS
BEGIN

    SET NOCOUNT ON;     -- this suppresses the "x rows affected" message which breaks returning the value

    DECLARE @Promoters decimal (10,2)
    SELECT @Promoters= CAST(COUNT(*) as decimal (10,2))
      FROM SatSurvey SS INNER JOIN ClinicConfig CC ON SS.ClinicID = CC.ClinicID
     WHERE CollectionDate >= @startDate 
       AND CollectionDate < DATEADD(d, 1, @endDate)
       AND RecommendNPS > 8
       AND DoNotReport IS NULL
       AND cc.GenerateSatSurvey = 1
       AND (@clinicName IS NULL OR (cc.MidName LIKE @clinicName))
    OPTION (Recompile)

    DECLARE @Detractors decimal (10,2)
    SELECT @Detractors =  CAST(COUNT(*) as decimal (10,2))
      FROM SatSurvey SS INNER JOIN ClinicConfig CC ON SS.ClinicID = CC.ClinicID
     WHERE CollectionDate >= @startDate 
       AND CollectionDate < DATEADD(d, 1, @endDate)
       AND RecommendNPS < 7
       AND DoNotReport IS NULL
       AND cc.GenerateSatSurvey = 1
       AND (@clinicName IS NULL OR (cc.MidName LIKE @clinicName))
    OPTION (Recompile)

    DECLARE @Responses decimal(10,2)
    SELECT @Responses = CAST(COUNT(*) as decimal (10,2))
      FROM SatSurvey SS INNER JOIN ClinicConfig CC ON SS.ClinicID = CC.ClinicID
     WHERE CollectionDate >= @startDate 
       AND CollectionDate < DATEADD(d, 1, @endDate)
       AND RecommendNPS IS NOT NULL
       AND DoNotReport IS NULL
       AND cc.GenerateSatSurvey = 1
       AND (@clinicName IS NULL OR (cc.MidName LIKE @clinicName))
    OPTION (Recompile)

    DECLARE @100Percent as decimal(5,2) = 100.00

    SELECT @NPS = CAST((@Promoters - @Detractors) / NULLIF((@Responses * @100Percent),0) as decimal(5,2))

END

By setting SET STATISTICS IO ON; and SET STATISTICS TIME ON; I was able to determine that this version of the query was about 3ms faster than the UNION version (79 vs 82ms, IIRC).

Including the actual execution plan when I ran the query (prior to actually making an SP out of it) indicated that I needed some indices on the tables underlying the view.

After starting with the recommended index and playing around with column ordering, I ended up with this:

CREATE NONCLUSTERED INDEX [nu_nc_DNR_CD_RNPS_SSV2018] ON [dbo].[table]
(
    [ClinicID] ASC,
    [CollectionDate] ASC,
    [RecommendNPS] ASC,
    [DoNotReport] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And it took me from about 79ms down to about 49ms.

\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.