SSMS SQL - AVG in PIVOT returning totals, not averages

Question

OK, this one is difficult. I'm sure that some use of the PIVOT statement is the answer, but I'm not knowledgeable enough to form the proper query. I think AVG is also throwing me for a loop.

I'm looking for just the total costs for each month / the number of members active that month

I have a query that will return exactly what I want, but it gives results in the form of one monthly period per row and I want a single row of averages with the monthly period as column headings.

I had gotten a means to create a dynamic query to specify the necessary fields to be used for column headings and for use in the SELECT and PIVOT statements of the query, and that works fine, but, rather than averaging, the query seems to be simply providing the SUM of the payments for that period.

In the code below, the results from the first query, the CTE one, contains the proper information, but as rows, rather than as columns. Essentially, I need to be able to pivot the data to provide the monthly period (Eff_Period) as the column headers and the average cost (Clms_PMPM) for the specific period as the value for the column.

Here is the whole query with sample temp tables declared and populated for use within it (I hope I'm doing this right);

--------------------
/*
   Create member table.
*/
CREATE TABLE #Mbr_Data
   (
   Member_Name nvarchar(50) NULL,
   MemberID nvarchar(4) NULL,
   Eff_Period nvarchar(6) NULL,
   );
--------------------
/*
   Create claim table.
*/
CREATE TABLE #Clm_Data
   (
   Clm_Dt date NULL,
   Clm_Amt money NULL,
   Mbr_ID nvarchar(6) NULL,
   );
--------------------
/*
   Populate member table.
*/
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201601');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201602');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201603');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201604');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201605');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201606');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201607');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201608');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201609');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201610');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201611');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201612');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201701');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201702');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201703');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201704');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201705');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201706');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201707');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201708');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201709');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201710');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201711');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201712');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201607');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201608');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201609');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201610');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201611');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201612');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201701');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201702');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201703');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201704');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201705');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201706');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201707');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201708');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201709');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201710');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201711');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201712');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201601');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201602');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201603');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201604');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201605');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201606');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201607');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201608');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201609');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201610');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201611');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201612');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201701');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201702');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201703');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201704');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201705');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201706');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201607');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201608');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201609');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201610');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201611');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201612');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201701');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201702');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201703');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201704');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201602');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201603');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201604');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201605');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201606');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201607');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201608');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201609');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201610');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201707');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201708');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201709');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201710');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201711');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201712');
--------------------
/*
   Populate claim table.
*/
INSERT INTO #Clm_Data VALUES ('2017-02-27', '523.37', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-24', '815.58', '0005');
INSERT INTO #Clm_Data VALUES ('2017-01-08', '541.91', '0004');
INSERT INTO #Clm_Data VALUES ('2016-12-08', '775.45', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-12', '177.44', '0003');
INSERT INTO #Clm_Data VALUES ('2017-01-19', '253.02', '0003');
INSERT INTO #Clm_Data VALUES ('2017-05-12', '539.29', '0003');
INSERT INTO #Clm_Data VALUES ('2016-08-23', '86.85', '0002');
INSERT INTO #Clm_Data VALUES ('2017-11-24', '869.67', '0002');
INSERT INTO #Clm_Data VALUES ('2016-10-15', '263.96', '0004');
INSERT INTO #Clm_Data VALUES ('2017-08-21', '111.27', '0001');
INSERT INTO #Clm_Data VALUES ('2016-06-17', '716.69', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-21', '754.21', '0002');
INSERT INTO #Clm_Data VALUES ('2016-06-12', '330.57', '0001');
INSERT INTO #Clm_Data VALUES ('2016-04-11', '770.02', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-13', '629', '0002');
INSERT INTO #Clm_Data VALUES ('2016-08-22', '876.02', '0001');
INSERT INTO #Clm_Data VALUES ('2017-03-21', '72.06', '0005');
INSERT INTO #Clm_Data VALUES ('2017-02-13', '249.11', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-13', '922.89', '0002');
INSERT INTO #Clm_Data VALUES ('2017-03-29', '760.59', '0003');
INSERT INTO #Clm_Data VALUES ('2017-01-10', '901.51', '0002');
INSERT INTO #Clm_Data VALUES ('2016-02-04', '432.17', '0003');
INSERT INTO #Clm_Data VALUES ('2017-02-14', '628.23', '0003');
INSERT INTO #Clm_Data VALUES ('2017-08-08', '718.05', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-28', '931.81', '0004');
INSERT INTO #Clm_Data VALUES ('2016-10-10', '973.44', '0002');
INSERT INTO #Clm_Data VALUES ('2017-08-26', '910.89', '0005');
INSERT INTO #Clm_Data VALUES ('2017-03-09', '613.04', '0004');
INSERT INTO #Clm_Data VALUES ('2017-07-14', '490.26', '0002');
INSERT INTO #Clm_Data VALUES ('2017-02-23', '941.18', '0005');
INSERT INTO #Clm_Data VALUES ('2017-06-19', '506.74', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-04', '970.9', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-13', '205.61', '0004');
INSERT INTO #Clm_Data VALUES ('2017-03-12', '580.84', '0002');
INSERT INTO #Clm_Data VALUES ('2016-09-25', '661.63', '0002');
INSERT INTO #Clm_Data VALUES ('2016-07-14', '391.89', '0004');
INSERT INTO #Clm_Data VALUES ('2016-12-16', '353.55', '0002');
INSERT INTO #Clm_Data VALUES ('2016-04-04', '284.09', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-16', '479.56', '0003');
INSERT INTO #Clm_Data VALUES ('2016-09-28', '276.65', '0002');
INSERT INTO #Clm_Data VALUES ('2016-08-06', '145.05', '0004');
INSERT INTO #Clm_Data VALUES ('2016-09-14', '947.97', '0001');
INSERT INTO #Clm_Data VALUES ('2016-09-24', '18.18', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-14', '489.54', '0001');
INSERT INTO #Clm_Data VALUES ('2017-08-06', '260.04', '0005');
INSERT INTO #Clm_Data VALUES ('2016-09-02', '224.2', '0001');
INSERT INTO #Clm_Data VALUES ('2017-12-23', '301.42', '0005');
INSERT INTO #Clm_Data VALUES ('2016-10-05', '636.12', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-26', '126.39', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-18', '156.15', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-21', '425.58', '0004');
INSERT INTO #Clm_Data VALUES ('2016-04-29', '619.03', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-07', '847.73', '0003');
INSERT INTO #Clm_Data VALUES ('2016-12-17', '143.94', '0001');
INSERT INTO #Clm_Data VALUES ('2016-02-24', '897.7', '0001');
INSERT INTO #Clm_Data VALUES ('2017-02-08', '130.13', '0002');
INSERT INTO #Clm_Data VALUES ('2017-01-02', '400.07', '0001');
INSERT INTO #Clm_Data VALUES ('2017-03-26', '289.36', '0004');
INSERT INTO #Clm_Data VALUES ('2016-07-26', '193.25', '0003');
INSERT INTO #Clm_Data VALUES ('2017-03-27', '585.15', '0005');
INSERT INTO #Clm_Data VALUES ('2016-11-06', '885.48', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-30', '104.55', '0003');
INSERT INTO #Clm_Data VALUES ('2017-10-08', '554.54', '0002');
INSERT INTO #Clm_Data VALUES ('2017-04-15', '37.34', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-17', '742.19', '0002');
INSERT INTO #Clm_Data VALUES ('2016-11-23', '527.78', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-06', '116.95', '0002');
INSERT INTO #Clm_Data VALUES ('2016-09-13', '408.24', '0004');
INSERT INTO #Clm_Data VALUES ('2016-09-12', '520.77', '0002');
INSERT INTO #Clm_Data VALUES ('2017-04-03', '325', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-13', '16.53', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-06', '845.77', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-21', '604.06', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-12', '47', '0001');
INSERT INTO #Clm_Data VALUES ('2017-05-06', '778.23', '0001');
INSERT INTO #Clm_Data VALUES ('2017-06-03', '507.67', '0002');
INSERT INTO #Clm_Data VALUES ('2016-10-20', '572.65', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-30', '764.08', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-19', '153.46', '0001');
INSERT INTO #Clm_Data VALUES ('2016-08-26', '491.57', '0005');
INSERT INTO #Clm_Data VALUES ('2016-04-18', '673.71', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-15', '830.9', '0001');
INSERT INTO #Clm_Data VALUES ('2017-10-06', '688.26', '0005');
INSERT INTO #Clm_Data VALUES ('2016-04-25', '676.57', '0001');
INSERT INTO #Clm_Data VALUES ('2016-04-11', '622.61', '0003');
INSERT INTO #Clm_Data VALUES ('2016-11-04', '723.65', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-31', '579.48', '0005');
INSERT INTO #Clm_Data VALUES ('2017-03-03', '831.47', '0003');
INSERT INTO #Clm_Data VALUES ('2016-12-22', '568.48', '0002');
INSERT INTO #Clm_Data VALUES ('2016-05-29', '800.18', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-19', '439.59', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-07', '396.01', '0001');
INSERT INTO #Clm_Data VALUES ('2016-10-26', '538.43', '0004');
INSERT INTO #Clm_Data VALUES ('2017-07-04', '382.15', '0005');
INSERT INTO #Clm_Data VALUES ('2017-12-30', '126.15', '0005');
INSERT INTO #Clm_Data VALUES ('2016-10-30', '195.71', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-06', '446.5', '0001');
INSERT INTO #Clm_Data VALUES ('2016-10-06', '484.34', '0003');
INSERT INTO #Clm_Data VALUES ('2016-11-20', '254.37', '0004');
--------------------
/*
   CTE method.
*/
WITH
   Mbrs_CTE AS
      (
      SELECT
         Eff_Period,
         COUNT(DISTINCT MemberID) AS Mbr_Cnt
      FROM
         #Mbr_Data
      GROUP BY
         Eff_Period
      ),
   Clms_CTE AS
      (
      SELECT DISTINCT
         FORMAT(Clm_Dt, 'yyyyMM') AS Eff_Period,
         SUM(Clm_Amt) OVER (PARTITION BY FORMAT(Clm_Dt, 'yyyyMM')) AS Clm_Pmts
      FROM
         #Clm_Data
      )
   SELECT
      Mbrs.Eff_Period,
      Mbrs.Mbr_Cnt,
      Clm_S.Clm_Pmts,
      Clm_S.Clm_Pmts / Mbrs.Mbr_Cnt AS Clms_PMPM
   FROM
      Mbrs_CTE AS Mbrs
      JOIN Clms_CTE AS Clm_S
         ON Clm_S.Eff_Period = Mbrs.Eff_Period;
--------------------
/*
   Dynamic query method.
*/
DECLARE
   @columns NVARCHAR(MAX),
   @sql NVARCHAR(MAX);

SET @columns = '';

SELECT
   @columns += ',' + QUOTENAME(Eff_Period)
FROM
   (
   SELECT DISTINCT TOP 100 PERCENT
      Mbrs.Eff_Period
   FROM
      #Mbr_Data AS Mbrs
      INNER JOIN #Clm_Data AS Clms
         ON
            Mbrs.Eff_Period = FORMAT(Clms.Clm_Dt, 'yyyyMM')
            AND YEAR(Clms.Clm_Dt) = 2016
   ORDER BY
      Mbrs.Eff_Period
   ) AS Raw_Data
ORDER BY
   Eff_Period;

PRINT @columns


SET @sql =
   '
   WITH
      Mbrs_CTE AS
         (
         SELECT
            Eff_Period,
            COUNT(DISTINCT MemberID) AS Mbr_Cnt
         FROM
            #Mbr_Data
         GROUP BY
            Eff_Period
         ),
      Clms_CTE AS
         (
         SELECT
            Mbrs.Eff_Period,
            Mbrs.Mbr_Cnt,
            SUM(Clms.Clm_Amt) AS Sum_Amt
         FROM
            Mbrs_CTE AS Mbrs 
            LEFT OUTER JOIN #Clm_Data AS Clms
               ON
                  Mbrs.Eff_Period = FORMAT(Clms.Clm_Dt, ''yyyyMM'')
                  AND YEAR(Clms.Clm_Dt) = 2016
         GROUP BY
            Mbrs.Eff_Period,
            Mbrs.Mbr_Cnt
         )
      SELECT
         ' + STUFF(@columns, 1, 1, '') + '
      FROM
         (
         SELECT
            Clms.Eff_Period,
            Clms.sum_Amt
         FROM
            Mbrs_CTE AS Mbrs
            INNER JOIN Clms_CTE AS Clms
               ON Clms.Eff_Period = Mbrs.Eff_Period

         ) AS sor
      PIVOT
         (
         AVG(sum_Amt) FOR Eff_Period IN
            ('
            + STUFF(@columns, 1, 1, '')
            + ')
         ) AS pvt;
   ';
PRINT @sql;
EXEC sp_executesql @sql;
--------------------

The form of the second query is correct, it's just that it seems to be providing the SUMs, rather than the AVGs.

I don't so much need the answer, as one put it, "spoonfed" to me; just some indication of what I'm doing wrong in the PIVOT query would be helpful.

Let me say in advance that any help would be greatly appreciated. Thanx!

Actually, that edit is wrong. In the healthcare industry, there is a value assigned to providers called the PMPM; Per Member Per Month. It is not a value for each member, but a means to see how much is being spent per member for the entire facility. So, if the healthcare costs are, say, $200 for October, and there are 50 active members that month, the PMPM is $200 / 50, or $4. It's not listed for each member, but is grouped by doctor, facility, hospital, whatever the entity for which the value is being determined. Rereading what I posted, though, I could have worded that part better. — AdamQuark, Nov 10 '17 at 21:40

pim · Accepted Answer · 2017-11-10T21:20:27.393

Edit: to provide result set as a means of Claim Average / Member Count within the month.

What a doozy! If I understand correctly I think the following should help guide you. It outputs the entire years grid regardless if a claim was made in the month.

Note that I removed the dynamic sql part, as I'm leaving that part to you to interpret leveraging the pivoted results below as input. I've also hard-coded in a specific year filter in the second CTE, which could be trivially replaced with a variable and/or made more complex to account for month, day etc.

;with memberClaimsByMonth as
(
    select
        m.MemberID as MemberId
        ,year(c.Clm_dt) as ClaimYear
        ,month(c.Clm_dt) as ClaimMonth
        ,c.Clm_Amt as ClaimAmount
    from
        #Mbr_Data m
    join
        #Clm_Data c
        on c.Mbr_ID = m.MemberID
),
memberAvgClaimByMonth as (
    select
        c.ClaimMonth
        ,count(c.MemberId) as MemberCount
        ,avg(c.ClaimAmount) as ClaimAvg
    from
        memberClaimsByMonth c
    where
        c.ClaimYear = 2016
    group by
        c.ClaimMonth
),
claimAvgOverMemberCount as (
    select  
        c.ClaimMonth
        ,c.ClaimAvg / c.MemberCount as ClaimAvgOverCount
    from 
        memberAvgClaimByMonth c
)

select 
    *
from 
    claimAvgOverMemberCount c
pivot 
(
    avg(c.ClaimAvgOverCount)
    for ClaimMonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p

I believe the key takeaway is the PIVOT leveraging AVG() for all the months in the year. To filter dynamically, you can encapsulate the pivot query into a CTE (or chuck the results in a temp table) and use dynamic sql to grab the columns (months) you're looking for.

Thanx for the post, pim, I'm fiddling with it now. Your code is giving a separate row for each member, but I'm looking for just the total costs for each month / the number of members active that month. I modified the code to return just the one row, but I'm not sure how it's getting its values. Still working on that. I'd post the way I modified it, but that doesn't seem to be an option in the comment field. Thanx again, though, I'll plug along on it! — AdamQuark, Nov 10 '17 at 21:12
@AdamQuark I've updated my response, which includes a third cte and a modification to the group by of the second cte. I think this more closely approximates what you're looking for. — pim, Nov 10 '17 at 21:21
@AdamQuark no worries. Would appreciate the upvote/acceptance if this helped you! — pim, Nov 10 '17 at 21:43
I thought I already had, but appears not. So, rectified that situation. Thanx again! — AdamQuark, Nov 13 '17 at 14:38

score 0 · Answer 2 · answered Nov 14 '17 at 15:33

Got a version that works as I want. Here's the final code set for a sample set of data;

--------------------
/*
    Create member table.
*/
CREATE TABLE #Mbr_Data
    (
    Member_Name nvarchar(50) NULL,
    MemberID nvarchar(4) NULL,
    Eff_Period nvarchar(6) NULL,
    );
--------------------
/*
    Create claim table.
*/
CREATE TABLE #Clm_Data
    (
    Clm_Dt date NULL,
    Clm_Amt money NULL,
    Mbr_ID nvarchar(6) NULL,
    );
--------------------
/*
    Populate member table.
*/
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201601');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201602');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201603');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201604');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201605');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201606');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201607');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201608');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201609');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201610');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201611');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201612');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201701');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201702');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201703');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201704');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201705');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201706');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201707');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201708');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201709');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201710');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201711');
INSERT INTO #Mbr_Data VALUES ('Bob', '0001', '201712');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201607');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201608');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201609');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201610');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201611');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201612');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201701');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201702');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201703');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201704');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201705');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201706');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201707');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201708');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201709');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201710');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201711');
INSERT INTO #Mbr_Data VALUES ('Marie', '0002', '201712');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201601');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201602');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201603');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201604');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201605');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201606');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201607');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201608');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201609');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201610');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201611');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201612');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201701');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201702');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201703');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201704');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201705');
INSERT INTO #Mbr_Data VALUES ('Antoine', '0003', '201706');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201607');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201608');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201609');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201610');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201611');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201612');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201701');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201702');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201703');
INSERT INTO #Mbr_Data VALUES ('Frank', '0004', '201704');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201602');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201603');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201604');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201605');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201606');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201607');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201608');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201609');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201610');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201707');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201708');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201709');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201710');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201711');
INSERT INTO #Mbr_Data VALUES ('Sue', '0005', '201712');
--------------------
/*
    Populate claim table.
*/
INSERT INTO #Clm_Data VALUES ('2017-02-27', '523.37', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-24', '815.58', '0005');
INSERT INTO #Clm_Data VALUES ('2017-01-08', '541.91', '0004');
INSERT INTO #Clm_Data VALUES ('2016-12-08', '775.45', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-12', '177.44', '0003');
INSERT INTO #Clm_Data VALUES ('2017-01-19', '253.02', '0003');
INSERT INTO #Clm_Data VALUES ('2017-05-12', '539.29', '0003');
INSERT INTO #Clm_Data VALUES ('2016-08-23', '86.85', '0002');
INSERT INTO #Clm_Data VALUES ('2017-11-24', '869.67', '0002');
INSERT INTO #Clm_Data VALUES ('2016-10-15', '263.96', '0004');
INSERT INTO #Clm_Data VALUES ('2017-08-21', '111.27', '0001');
INSERT INTO #Clm_Data VALUES ('2016-06-17', '716.69', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-21', '754.21', '0002');
INSERT INTO #Clm_Data VALUES ('2016-06-12', '330.57', '0001');
INSERT INTO #Clm_Data VALUES ('2016-04-11', '770.02', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-13', '629', '0002');
INSERT INTO #Clm_Data VALUES ('2016-08-22', '876.02', '0001');
INSERT INTO #Clm_Data VALUES ('2017-03-21', '72.06', '0005');
INSERT INTO #Clm_Data VALUES ('2017-02-13', '249.11', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-13', '922.89', '0002');
INSERT INTO #Clm_Data VALUES ('2017-03-29', '760.59', '0003');
INSERT INTO #Clm_Data VALUES ('2017-01-10', '901.51', '0002');
INSERT INTO #Clm_Data VALUES ('2016-02-04', '432.17', '0003');
INSERT INTO #Clm_Data VALUES ('2017-02-14', '628.23', '0003');
INSERT INTO #Clm_Data VALUES ('2017-08-08', '718.05', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-28', '931.81', '0004');
INSERT INTO #Clm_Data VALUES ('2016-10-10', '973.44', '0002');
INSERT INTO #Clm_Data VALUES ('2017-08-26', '910.89', '0005');
INSERT INTO #Clm_Data VALUES ('2017-03-09', '613.04', '0004');
INSERT INTO #Clm_Data VALUES ('2017-07-14', '490.26', '0002');
INSERT INTO #Clm_Data VALUES ('2017-02-23', '941.18', '0005');
INSERT INTO #Clm_Data VALUES ('2017-06-19', '506.74', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-04', '970.9', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-13', '205.61', '0004');
INSERT INTO #Clm_Data VALUES ('2017-03-12', '580.84', '0002');
INSERT INTO #Clm_Data VALUES ('2016-09-25', '661.63', '0002');
INSERT INTO #Clm_Data VALUES ('2016-07-14', '391.89', '0004');
INSERT INTO #Clm_Data VALUES ('2016-12-16', '353.55', '0002');
INSERT INTO #Clm_Data VALUES ('2016-04-04', '284.09', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-16', '479.56', '0003');
INSERT INTO #Clm_Data VALUES ('2016-09-28', '276.65', '0002');
INSERT INTO #Clm_Data VALUES ('2016-08-06', '145.05', '0004');
INSERT INTO #Clm_Data VALUES ('2016-09-14', '947.97', '0001');
INSERT INTO #Clm_Data VALUES ('2016-09-24', '18.18', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-14', '489.54', '0001');
INSERT INTO #Clm_Data VALUES ('2017-08-06', '260.04', '0005');
INSERT INTO #Clm_Data VALUES ('2016-09-02', '224.2', '0001');
INSERT INTO #Clm_Data VALUES ('2017-12-23', '301.42', '0005');
INSERT INTO #Clm_Data VALUES ('2016-10-05', '636.12', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-26', '126.39', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-18', '156.15', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-21', '425.58', '0004');
INSERT INTO #Clm_Data VALUES ('2016-04-29', '619.03', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-07', '847.73', '0003');
INSERT INTO #Clm_Data VALUES ('2016-12-17', '143.94', '0001');
INSERT INTO #Clm_Data VALUES ('2016-02-24', '897.7', '0001');
INSERT INTO #Clm_Data VALUES ('2017-02-08', '130.13', '0002');
INSERT INTO #Clm_Data VALUES ('2017-01-02', '400.07', '0001');
INSERT INTO #Clm_Data VALUES ('2017-03-26', '289.36', '0004');
INSERT INTO #Clm_Data VALUES ('2016-07-26', '193.25', '0003');
INSERT INTO #Clm_Data VALUES ('2017-03-27', '585.15', '0005');
INSERT INTO #Clm_Data VALUES ('2016-11-06', '885.48', '0002');
INSERT INTO #Clm_Data VALUES ('2016-12-30', '104.55', '0003');
INSERT INTO #Clm_Data VALUES ('2017-10-08', '554.54', '0002');
INSERT INTO #Clm_Data VALUES ('2017-04-15', '37.34', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-17', '742.19', '0002');
INSERT INTO #Clm_Data VALUES ('2016-11-23', '527.78', '0004');
INSERT INTO #Clm_Data VALUES ('2017-06-06', '116.95', '0002');
INSERT INTO #Clm_Data VALUES ('2016-09-13', '408.24', '0004');
INSERT INTO #Clm_Data VALUES ('2016-09-12', '520.77', '0002');
INSERT INTO #Clm_Data VALUES ('2017-04-03', '325', '0001');
INSERT INTO #Clm_Data VALUES ('2016-11-13', '16.53', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-06', '845.77', '0004');
INSERT INTO #Clm_Data VALUES ('2016-08-21', '604.06', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-12', '47', '0001');
INSERT INTO #Clm_Data VALUES ('2017-05-06', '778.23', '0001');
INSERT INTO #Clm_Data VALUES ('2017-06-03', '507.67', '0002');
INSERT INTO #Clm_Data VALUES ('2016-10-20', '572.65', '0003');
INSERT INTO #Clm_Data VALUES ('2017-06-30', '764.08', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-19', '153.46', '0001');
INSERT INTO #Clm_Data VALUES ('2016-08-26', '491.57', '0005');
INSERT INTO #Clm_Data VALUES ('2016-04-18', '673.71', '0001');
INSERT INTO #Clm_Data VALUES ('2016-07-15', '830.9', '0001');
INSERT INTO #Clm_Data VALUES ('2017-10-06', '688.26', '0005');
INSERT INTO #Clm_Data VALUES ('2016-04-25', '676.57', '0001');
INSERT INTO #Clm_Data VALUES ('2016-04-11', '622.61', '0003');
INSERT INTO #Clm_Data VALUES ('2016-11-04', '723.65', '0005');
INSERT INTO #Clm_Data VALUES ('2016-07-31', '579.48', '0005');
INSERT INTO #Clm_Data VALUES ('2017-03-03', '831.47', '0003');
INSERT INTO #Clm_Data VALUES ('2016-12-22', '568.48', '0002');
INSERT INTO #Clm_Data VALUES ('2016-05-29', '800.18', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-19', '439.59', '0005');
INSERT INTO #Clm_Data VALUES ('2016-08-07', '396.01', '0001');
INSERT INTO #Clm_Data VALUES ('2016-10-26', '538.43', '0004');
INSERT INTO #Clm_Data VALUES ('2017-07-04', '382.15', '0005');
INSERT INTO #Clm_Data VALUES ('2017-12-30', '126.15', '0005');
INSERT INTO #Clm_Data VALUES ('2016-10-30', '195.71', '0005');
INSERT INTO #Clm_Data VALUES ('2017-08-06', '446.5', '0001');
INSERT INTO #Clm_Data VALUES ('2016-10-06', '484.34', '0003');
INSERT INTO #Clm_Data VALUES ('2016-11-20', '254.37', '0004');
--------------------
/*
    Dynamic query method.
*/
DECLARE
    @columns NVARCHAR(MAX),
    @columns2 NVARCHAR(MAX),
    @sql NVARCHAR(MAX);

SET @columns = '';

SELECT
    @columns += ',' + QUOTENAME(Eff_Period)
FROM
    (
    SELECT DISTINCT TOP 100 PERCENT
        Mbrs.Eff_Period
    FROM
        #Mbr_Data AS Mbrs
        INNER JOIN #Clm_Data AS Clms
            ON
                Mbrs.Eff_Period = FORMAT(Clms.Clm_Dt, 'yyyyMM')
                AND YEAR(Clms.Clm_Dt) = 2016
    ORDER BY
        Mbrs.Eff_Period
    ) AS Raw_Data
ORDER BY
    Eff_Period;

SET @columns2 = REPLACE(REPLACE(REPLACE(STUFF(@columns, 1, 1, ''), '],[', ''','''),'[' , ''''), ']', '''');

PRINT @columns
PRINT @columns2

SET @sql =
    '
    WITH
        Mbrs_CTE AS
            (
            SELECT
                Eff_Period,
                COUNT(DISTINCT MemberID) AS Mbr_Cnt
            FROM
                #Mbr_Data
            GROUP BY
                Eff_Period
            ),
        Clms_CTE AS
            (
            SELECT DISTINCT
                FORMAT(Clm_Dt, ''yyyyMM'') AS Eff_Period,
                SUM(Clm_Amt) OVER (PARTITION BY FORMAT(Clm_Dt, ''yyyyMM'')) AS Clm_Pmts
            FROM
                #Clm_Data
            ),
        PMPM_CTE AS
            (
            SELECT
                Mbrs.Eff_Period,
                Clm_S.Clm_Pmts / Mbrs.Mbr_Cnt AS Clms_PMPM
            FROM
                Mbrs_CTE AS Mbrs
                JOIN Clms_CTE AS Clm_S
                    ON Clm_S.Eff_Period = Mbrs.Eff_Period
            WHERE
                Clm_S.Eff_Period IN (' + @columns2 + ')
            )
        SELECT
            ' + STUFF(@columns, 1, 1, '') + '
        FROM
            PMPM_CTE AS S
            PIVOT(MAX(Clms_PMPM) FOR Eff_Period IN ('    + STUFF(@columns, 1, 1, '')    + ')) AS PMPM;
    ';

PRINT @sql;
EXEC sp_executesql @sql;
--------------------
DROP TABLE #Mbr_Data;
DROP TABLE #Clm_Data;
--------------------

SSMS SQL - AVG in PIVOT returning totals, not averages

2 Answers2