problem with PIVOT Table

Question

problem with PIVOT Table

SSCarpal Tunnel

Points: 4298

October 28, 2024 at 8:31 pm

Thanks in Advance! I'm having trouble grouping all counts and sums on one line for each county which should not look like (Code attached) Note one county is repeated and most are like that:

County MH SA DD Cost

Camden 0 0 1 152.88

Pitt 0 0 1 2751.45

Pender 3 1 0 24.96

Guilford 1 1 0 34.60

Robeson 0 0 1 3745.80

Carteret 1 0 0 285.60

Randolph 1 0 0 8.60

Randolph 1 1 0 401.80

DECLARE 
 @StartDate datetime
, @EndDate datetime


SET @StartDate = '03/01/2024'
SET @EndDate = '09/01/2024'

-----------------------------------------------------------------------
-- using SK values as local variables makes for simpler code
-- we can use the SK directly instead of joining to DimDate

DECLARE @StartDateSK int
,@EndDateSK int

SELECT @StartDateSK = Date_SK
FROM [EDM].[dm].[DimDate]
WHERE DateValue = @StartDate

SELECT @EndDateSK = Date_SK
FROM [EDM].[dm].[DimDate] 
WHERE DateValue = @EndDate


-- QM query used County from EligCoa; we do the same here
-- this also limits things to members with EligCoa

IF OBJECT_ID('tempdb..#tempA') IS NOT NULL drop table #tempA

SELECT N4.County
  ,N5.DiagnosisGroup
  ,N1.MemberID
  ,AdjudicatedAmount = SUM(N1.AdjudicatedAmount)
    INTO #tempA

FROM EDM.summary.Claim N1

INNER JOIN EDM.dm.FactMemberEligibility_SKAligned N3
ON N3.Member_SK = N1.Member_SK
AND N1.ServiceDate_SK BETWEEN N3.EffectiveDate_SK AND N3.ExpirationDate_SK

INNER JOIN EDM.dm.DimCounty N4
ON N4.County_SK = N3.County_SK

INNER JOIN EDM.dm.DimGLAccount N5
ON N5.GLAccount_SK = N1.GLAccount_SK

WHERE ((N1.IsCapitated = 1
AND N1.ServiceDate_SK BETWEEN @StartDateSK AND @EndDateSK)

OR (N1.IsPaid = 1
AND N1.PaidDate_SK BETWEEN @StartDateSK AND @EndDateSK))
GROUP BY County, DiagnosisGroup, MemberID, AdjudicatedAmount

-- Pivot table
SELECT County,
       [MH] as 'MH', 
   [SA] as 'SA', 
   [DD] as 'DD',
   SUM(AdjudicatedAmount)
   
FROM
(
SELECT County, DiagnosisGroup, MemberID, AdjudicatedAmount
 FROM #tempA
 GROUP BY County, DiagnosisGroup, MemberID, AdjudicatedAmount
 ) p

PIVOT
(
COUNT(MemberID)
FOR DiagnosisGroup IN ([MH], [SA], [DD])
) AS pvt

Group by County, MH, SA, DD, AdjudicatedAmount

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.