problem with PIVOT Table

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  October 28, 2024 at 8:31 pm

  #4474721

  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