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 4 posts - 1 through 3 (of 3 total)

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

Site Owners SSC Guru Points: 80325 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.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

To pull off multiple aggregations in a single pivot, just code it yourself:

WITH CountsAndSums AS (
  SELECT County, DiagnosisGroup, 
         COUNT(MemberID) AS [MemberCount], 
         SUM(AdjudicatedAmount) AS AdjudicatedAmount
    FROM #tempA
   GROUP BY County, DiagnosisGroup
)
  SELECT County, 
         SUM(CASE WHEN DiagnosisGroup = 'MH' THEN MemberCount ELSE 0 END) AS [MH],
         SUM(CASE WHEN DiagnosisGroup = 'SA' THEN MemberCount ELSE 0 END) AS [SA],
         SUM(CASE WHEN DiagnosisGroup = 'DD' THEN MemberCount ELSE 0 END) AS [DD],
         SUM(AdjudicatedAmount) AS AdjudicatedAmount
    FROM CountsAndSums
   GROUP BY County;

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

To follow up on Eddie Wuerch's good post, it's an old but powerful bit of ancient code known as a CROSSTAB. You can get a good introduction to them in the following articles. The first contains the basics and how it compares against (ugh...) Pivot and the second is how to build CrossTabs dynamically.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)