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
October 29, 2024 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply