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

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