Stored Procedure Returning Duplicate Invalid Row

Question

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[spGetQualityReport]
 (
  @providerKey INT          
 )

AS


-- declare @providerKey INT          

-- set @providerKey = 1;

--Get Database providerId first
DECLARE @realProvId INT;
SET @realProvId = (SELECT TOP(1) providerId from providerKeyTranslation where keyValue LIKE @providerKey)-- Nine to be replaced by datasource

DECLARE @ProviderName varchar(500);
SET @ProviderName = (SELECT name FROM provider WHERE id = @realProvId)

DECLARE @cmsId varchar(50);
SET @cmsId = (select top(1) KeyValue from ProviderKeyTranslation pkt where @realProvId = pkt.ProviderId)

DECLARE @ProviderType varchar(200);
SET @ProviderType = (select name from providertype where id = (select providertypeid from provider where id =@realProvId))

DECLARE @stateValue INT;
SET @stateValue = (SELECT TOP(1) KeyValue from providerKeyTranslation where ProviderId = @realProvId)

(
SELECT DISTINCT
(select d.Name + ' - ' + d.Description from DataSource d where pcmv.DataSourceId = d.Id) as [Source],
m.Id as [Measure Key],
m.Name AS MeasureName,
cmm.Ordering as [Measure Sort Order], --(SELECT DISTINCT ordering FROM dbo.fnGetProviderMeasureSortOrder(@realProvId,pcmv.CategoryId,pcmv.MeasureId)), --(select Ordering from Category_Measure_Map cmm where cmm.MeasureId = pcmv.MeasureId and cmm.CategoryId = pcmv.CategoryId) as [Measure Sort Order],
(select Name from DataSource_Locale ds where pcmv.ProviderId = @realProvId and pcmv.DataSourceId = ds.Id) as [Dates of Measures],
@stateValue AS [State Value],
m.UnitTypeId,
@realProvId AS [Provider Id], 
@ProviderName AS [Provider Name],
@ProviderType as [Provider Type],
@cmsId as [CMS ID],
Value AS [Value],
pcmv.ConditionId,
pcmv.Denominator,  
(select Name from Condition c where c.Id = pcmv.ConditionId) as [Condition],
(select TOP(1)rg.Name from Report r, ReportGrouping rg where r.ConditionId = pcmv.ConditionId AND r.ReportGroupingId = rg.Id) as [Condition Type],
(select Ordering from Report r where r.ConditionId = pcmv.ConditionId) as [Condition Sort Order],
case when (r.rating = 1) then '$'
when (r.rating = 2) then '$$'
when (r.rating = 3) then '$$$'
when (r.rating = 4) then '$$$$' end
AS Rating,
sig.Name AS Significance, 
f.Name AS FootNote,
pcmv.Deaths,
pcmv.ObsRate,
pcmv.ExpRate,
pcmv.LowCI,
pcmv.UpperCI
FROM Measure m
RIGHT JOIN
(
providerConditionMeasureValue pcmv   
INNER JOIN Provider p on p.Id = pcmv.ProviderId and p.Id = @realProvId
INNER JOIN dbo.Category_Measure_Map cmm ON cmm.MeasureId = pcmv.MeasureId AND cmm.CategoryId = pcmv.CategoryId AND pcmv.ProviderId = @realProvId
LEFT JOIN Rating r 
 on pcmv.Rating = r.Id
LEFT JOIN Footnote f 
 on pcmv.FootnoteId = f.Id 
LEFT JOIN Significance sig 
 on pcmv.SignificanceId = sig.Id  

) ON m.Id = pcmv.MeasureId
--CROSS JOIN dbo.DataSource AS d
where m.MeasureTypeId = 1  /*Quality Measure Type */
--Group BY m.Id, m.Name, m.UnitTypeId, pcmv.MeasureId, pcmv.DataSourceId, pcmv.CategoryId, pcmv.providerId, p.Name, Value,pcmv.ConditionId,r.rating, sig.Name, f.Name, pcmv.Deaths, pcmv.ObsRate, pcmv.ExpRate, pcmv.LowCI, pcmv.UpperCI, pcmv.Denominator
)
UNION
(
SELECT 
(select d.Name + ' - ' + d.Description from DataSource d where pmv.DataSourceId = d.Id) as [Source],
m.Id as [Measure Key],
m.Name AS MeasureName,
cmm.Ordering as [Measure Sort Order],--(select Ordering from Category_Measure_Map cmm where cmm.MeasureId = pmv.MeasureId and cmm.CategoryId = pmv.CategoryId) as [Measure Sort Order],
(select Name from DataSource_Locale ds where pmv.ProviderId = @realProvId and pmv.DataSourceId = ds.Id) as [Dates of Measures],
@stateValue AS [State Value],
m.UnitTypeId,
@realProvId AS [Provider Id], 
@ProviderName AS [Provider Name],
@ProviderType as [Provider Type],
@cmsId as [CMS ID],
Value AS [Value],  
null,
pmv.Denominator,
NULL,
null,
NULL,
case when (r.rating = 1) then '$'
when (r.rating = 2) then '$$'
when (r.rating = 3) then '$$$'
when (r.rating = 4) then '$$$$' end
AS Rating,
sig.Name AS Significance, 
f.Name AS FootNote,
pmv.Deaths,
pmv.ObsRate,
pmv.ExpRate,
pmv.LowCI,
pmv.UpperCI

FROM Measure m
RIGHT JOIN
(
providerMeasureValue pmv   
INNER JOIN Provider p on p.Id = pmv.ProviderId and p.Id = @realProvId
INNER JOIN dbo.Category_Measure_Map cmm ON cmm.MeasureId = pmv.MeasureId AND cmm.CategoryId = pmv.CategoryId AND pmv.ProviderId = @realProvId
LEFT JOIN Rating r 
 on pmv.Rating = r.Id
LEFT JOIN Footnote f 
 on pmv.FootnoteId = f.Id 
LEFT JOIN Significance sig 
 on pmv.SignificanceId = sig.Id  

) ON m.Id = pmv.MeasureId
--CROSS JOIN dbo.DataSource AS d
where m.MeasureTypeId = 1/*Quality Measure Type */
--Group BY m.Id, m.Name, pmv.MeasureId, pmv.DataSourceId, pmv.CategoryId, m.UnitTypeId, pmv.providerId, p.Name, Value,r.rating, sig.Name, f.Name,  pmv.Deaths, pmv.ObsRate, pmv.ExpRate,pmv.LowCI,pmv.UpperCI, pmv.Denominator
)

Working on a stored procedure and it keeps returning the multiple measure sort order even though there is only one mapping in the category measure table. Help!

Answer 1

You need to break it down and build it back up carefully - one of your joins is creating an inadvertent many-to-one join - perhaps you've left out a condition in the join or one of your assumptions is not holding.