问:
SSMS SQL - AVG in PIVOT returning totals, not averages
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);

```<code>--------------------
/*
   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;
--------------------
</code>```

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!

答:
Got a version that works as I want. Here's the final code set for a sample set of data;</p>

```<code>--------------------
/*
    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;
--------------------
</code>```

Body:
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!

Body:
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;
--------------------


来源: stackexchange

问:
MS Access SQL - Count of Records that are Currently Open During a Workweek
I have a record set that contains

```<code> unique ID
 open date
 closed date
</code>```

I want a query to find the count of record that were active during each workweek (52 workweeks in each year).  The data spans several years so the workweeks would not be unique, repeating from 1-52 each year.

Some pseudo-Code would look like:

```<code> CountIf( ((OpenDate >= Workweek) & (ClosedDate >= WorkWeek))
         OR ((OpenDate >= Workweek) & IsNull(ClosedDate)) )
</code>```

The end result would be a table with the Years, Workweeks, and Count of Active records for that year/workweek.

I have a query that gives me Opened in a workweek:

```<code>SELECT Year([TABLE].[OpenedDate]) AS [Year], DatePart('ww',TABLE.[OpenedDate])
AS [Work Week], Count(TABLE.[ID]) AS [Count Of IDs Opened]
FROM TABLE
GROUP BY Year([TABLE].[OpenedDate]), DatePart('ww',TABLE.[OpenedDate])
ORDER BY Year([TABLE].[OpenedDate]), DatePart('ww',TABLE.[OpenedDate]);
</code>```

And a similar one for Closed but I just can't wrap my head around how to get Active during each Workweek.

Edited to add:

Example Data:

```<code>No  Opened Date Closed Date
1   20-Apr-15   23-Apr-15
2   22-Apr-15   04-May-15
3   27-Apr-15   01-May-15
4   29-Apr-15   04-May-15
5   29-Apr-15   05-Jun-15
6   04-May-15   20-May-15
7   04-May-15   07-May-15
8   05-May-15   20-May-15
9   05-May-15   20-May-15
10  06-May-15   20-May-15
11  06-May-15   20-May-15
12  06-May-15   20-May-15
13  07-May-15   20-May-15
14  11-May-15   26-May-15
15  11-May-15   26-May-15
16  13-May-15   26-May-15
17  18-May-15   26-May-15
18  20-May-15   01-Jun-15
19  21-May-15   03-Jun-15
20  12-Oct-15   07-Jul-16
21  02-Nov-15   26-Jul-17
22  06-Nov-15   30-Nov-15
23  09-Nov-15   26-Jul-17
24  08-Feb-17   05-May-17
25  13-Feb-17
26  13-Feb-17   14-Aug-17
27  07-Mar-17   17-Mar-17
</code>```

答:
You need a table with all years and weeks, lets call it allYears: {Year, Week}

Also I guess you have a main table for your records, lets call it stores to give it some context. Otherwise you do:

```<code>SELECT DISTINCT ID FROM `active_stores`
</code>```

Finally your actives record will be active_stores

Then your query is:

```<code>SELECT allYears.Year,
       stores.ID,
       COUNT([active_stores].ID) as total_active_weeks
FROM allYears, stores
LEFT JOIN active_stores
  ON allYears.Year = Year([active_stores].[OpenedDate])
 AND allYears.Week >= DatePart('ww',[active_stores].[OpenedDate])
 AND allYears.Week <= DatePart('ww',[active_stores].[ClosedDate])
 AND stores.ID = [active_stores].ID
GROUP BY allYears.Year,
         stores.ID
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I have a record set that contains

 unique ID
 open date
 closed date

I want a query to find the count of record that were active during each workweek (52 workweeks in each year).  The data spans several years so the workweeks would not be unique, repeating from 1-52 each year.

Some pseudo-Code would look like:

 CountIf( ((OpenDate >= Workweek) & (ClosedDate >= WorkWeek))
         OR ((OpenDate >= Workweek) & IsNull(ClosedDate)) )

The end result would be a table with the Years, Workweeks, and Count of Active records for that year/workweek.

I have a query that gives me Opened in a workweek:

SELECT Year([TABLE].[OpenedDate]) AS [Year], DatePart('ww',TABLE.[OpenedDate])
AS [Work Week], Count(TABLE.[ID]) AS [Count Of IDs Opened]
FROM TABLE
GROUP BY Year([TABLE].[OpenedDate]), DatePart('ww',TABLE.[OpenedDate])
ORDER BY Year([TABLE].[OpenedDate]), DatePart('ww',TABLE.[OpenedDate]);

And a similar one for Closed but I just can't wrap my head around how to get Active during each Workweek.

Edited to add:

Example Data:

No  Opened Date Closed Date
1   20-Apr-15   23-Apr-15
2   22-Apr-15   04-May-15
3   27-Apr-15   01-May-15
4   29-Apr-15   04-May-15
5   29-Apr-15   05-Jun-15
6   04-May-15   20-May-15
7   04-May-15   07-May-15
8   05-May-15   20-May-15
9   05-May-15   20-May-15
10  06-May-15   20-May-15
11  06-May-15   20-May-15
12  06-May-15   20-May-15
13  07-May-15   20-May-15
14  11-May-15   26-May-15
15  11-May-15   26-May-15
16  13-May-15   26-May-15
17  18-May-15   26-May-15
18  20-May-15   01-Jun-15
19  21-May-15   03-Jun-15
20  12-Oct-15   07-Jul-16
21  02-Nov-15   26-Jul-17
22  06-Nov-15   30-Nov-15
23  09-Nov-15   26-Jul-17
24  08-Feb-17   05-May-17
25  13-Feb-17
26  13-Feb-17   14-Aug-17
27  07-Mar-17   17-Mar-17

Body:
You need a table with all years and weeks, lets call it allYears: {Year, Week}

Also I guess you have a main table for your records, lets call it stores to give it some context. Otherwise you do:

SELECT DISTINCT ID FROM `active_stores`

Finally your actives record will be active_stores

Then your query is:

SELECT allYears.Year,
       stores.ID,
       COUNT([active_stores].ID) as total_active_weeks
FROM allYears, stores
LEFT JOIN active_stores
  ON allYears.Year = Year([active_stores].[OpenedDate])
 AND allYears.Week >= DatePart('ww',[active_stores].[OpenedDate])
 AND allYears.Week <= DatePart('ww',[active_stores].[ClosedDate])
 AND stores.ID = [active_stores].ID
GROUP BY allYears.Year,
         stores.ID


问:
Compile current .cpp file VS2008
What is the hot-key or where can I go to compile my current file in VS2008 C++? Thx.</p>

答:
Ctrl-F7 - current file

F7 - project

ContentLicense: CC BY-SA 2.5

Title: Compile current .cpp file VS2008

Body:
Ctrl-F7 - current file

F7 - project


问:
CodeIgniter URL not working
I wonder if anyone can help me??? I'm using CodeIgniter to create a small Login Screen. However, every time I call the function to validate the details using the CodeIgniter form helper. A 404 error keeps showing stating that CodeIgniter can't find the URL. My code for the 'View' file looks like:

```<code><!doctype html>
<html>
<head>
    <title>CodeIgniter Prototype</title>

    <!-- CSS/LESS Code Links -->
    <link rel = "stylesheet" href = "<?php echo base_url();?>css/style.css" type = "text/css"/>
</head>

<body>
    <div class = "login_wrapper">
        <h1>ONESG OneInvoice Exporter</h1>
        <?php
            echo form_open('login/check_login');
            echo form_input('username', '', 'placeholder = "Enter Username"');
            echo form_password('password', '', 'placeholder = "Enter Password"');
            echo form_submit('submit', 'Login');
            echo form_close();
        ?>
    </div>
</body>
</html>
</code>```

My Controller Code Looks Like:

```<code>    <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Login extends CI_Controller {

    public function index() {
        $this->load->view('login_form');
    }

    public function check_login() {
        $this->load->model('login_model');
        $query = $this->login_model->validate();

        if ($query) {
            $data = array (
                'username' => $this->input->post('username'),
                'is_logged_in' => true
            );

            $this->sessions->set_userdata($data);
            redirect('site/members_area');
        }

        else {
            $this->index();
        }
    }

}
</code>```

答:
Check your folder structure whether you have .htaccess file.
if not add file name called .htaccess
and add following code to file

```<code>RewriteEngine on
RewriteCond $1 !^(index\.php|resources|robots\.txt)
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L,QSA]
</code>```

Title: CodeIgniter URL not working

Body:
I wonder if anyone can help me??? I'm using CodeIgniter to create a small Login Screen. However, every time I call the function to validate the details using the CodeIgniter form helper. A 404 error keeps showing stating that CodeIgniter can't find the URL. My code for the 'View' file looks like:

<!doctype html>

    <title>CodeIgniter Prototype</title>

    <link rel = "stylesheet" href = "<?php echo base_url();?>css/style.css" type = "text/css"/>

ONESG OneInvoice Exporter

        <?php
            echo form_open('login/check_login');
            echo form_input('username', '', 'placeholder = "Enter Username"');
            echo form_password('password', '', 'placeholder = "Enter Password"');
            echo form_submit('submit', 'Login');
            echo form_close();
        ?>

My Controller Code Looks Like:

    <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Login extends CI_Controller {

    public function index() {
        $this->load->view('login_form');
    }

    public function check_login() {
        $this->load->model('login_model');
        $query = $this->login_model->validate();

        if ($query) {
            $data = array (
                'username' => $this->input->post('username'),
                'is_logged_in' => true
            );

            $this->sessions->set_userdata($data);
            redirect('site/members_area');
        }

        else {
            $this->index();
        }
    }

}

Body:
Check your folder structure whether you have .htaccess file.
if not add file name called .htaccess
and add following code to file

RewriteEngine on
RewriteCond $1 !^(index\.php|resources|robots\.txt)
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L,QSA]


问:
replace a word that contains a string in powershell
I am trying to sanitize a logfile and replace all hostnames containing the word lab with sanitized

If I knew all the possible hostnames, I could loop the replace but there are many hundreds of hostnames yet all contain the word lab eg:

steve-lab-food

two-lab-four

hello-lab-dog

I have tried the following:

```<code>(get-content .\log.txt) -replace '\*lab\*', "sanitized"
(get-content .\log.txt) -replace '*lab*', "sanitized"
</code>```

答:
The -replace operator uses regex so your wildcard attempt will not work, but your method is correct.

```<code>## \w+ captures 1+ word characters
## -lab- captures case-insensitive literally
## .*? captures any characters up to a whitespace
## )+ captures 1+ instances
$Pattern = '(\w+-lab-.*?)+'
(Get-Content -Path 'Logfile.txt') -replace $Pattern,'sanitized_hostname' |
    Out-File -FilePath 'Sanitized_Logfile.txt' -Encoding 'UTF8'
    ## Set-Content -Path 'Logfile.txt' -Encoding 'UTF8'
</code>```

Body:
I am trying to sanitize a logfile and replace all hostnames containing the word lab with sanitized

If I knew all the possible hostnames, I could loop the replace but there are many hundreds of hostnames yet all contain the word lab eg:

steve-lab-food

two-lab-four

hello-lab-dog

I have tried the following:

(get-content .\log.txt) -replace '\*lab\*', "sanitized"
(get-content .\log.txt) -replace '*lab*', "sanitized"

Body:
The -replace operator uses regex so your wildcard attempt will not work, but your method is correct.

## \w+ captures 1+ word characters
## -lab- captures case-insensitive literally
## .*? captures any characters up to a whitespace
## )+ captures 1+ instances
$Pattern = '(\w+-lab-.*?)+'
(Get-Content -Path 'Logfile.txt') -replace $Pattern,'sanitized_hostname' |
    Out-File -FilePath 'Sanitized_Logfile.txt' -Encoding 'UTF8'
    ## Set-Content -Path 'Logfile.txt' -Encoding 'UTF8'


问:
Which file is more suggestable to use jsx or js file in react application and why?
In React, there is two ways to use the react file. one is JSX and another is js file. my doubt is why react created new extension to develop the react application event though js file format is getting support by it.</p>

答:
You can use js extensions for JSX components if you want. But actually JSX is not JS standard. I think thats why they created a new extension. Being strict, JSX is not JS.

Some eslint rules, like airbnt ruleset, force to you to use .jsx extension for React components, and .js extensions for JS 'plain' code.

Here there is a interesing thread discussing about that:

https://github.com/airbnb/javascript/pull/985


问:
error: anachronistic old-style base class initializer [-fpermissive]
I have this constructor which give an error on compilation :-

```<code>Time::Time(short y,short m,short d,short h,short mi,short s):
        (*this).y(y);
        (*this).m(m);
        (*this).d(d);
        (*this).h(h);
        (*this).mi(mi);
        (*this).s(s);   {};
</code>```

here is full error:-

```<code>Time.cpp: In constructor ‘Time::Time(short int, short int, short int, short int, short int, short int)’:
Time.cpp:22:2: error: anachronistic old-style base class initializer [-fpermissive]
  (*this).y(y);
  ^
Time.cpp:21:61: error: unnamed initializer for ‘Time’, which has no base classes
 Time::Time(short y,short m,short d,short h,short mi,short s):
                                                             ^
Time.cpp:22:9: error: expected ‘{’ before ‘.’ token
  (*this).y(y);
         ^
Time.cpp: At global scope:
Time.cpp:22:9: error: expected unqualified-id before ‘.’ token
Time.cpp:23:4: error: expected unqualified-id before ‘this’
  (*this).m(m);
    ^~~~
Time.cpp:23:4: error: expected ‘)’ before ‘this’
Time.cpp:24:4: error: expected unqualified-id before ‘this’
  (*this).d(d);
    ^~~~
Time.cpp:24:4: error: expected ‘)’ before ‘this’
Time.cpp:25:4: error: expected unqualified-id before ‘this’
  (*this).h(h);
    ^~~~
Time.cpp:25:4: error: expected ‘)’ before ‘this’
Time.cpp:26:4: error: expected unqualified-id before ‘this’
  (*this).mi(mi);
    ^~~~
Time.cpp:26:4: error: expected ‘)’ before ‘this’
Time.cpp:27:4: error: expected unqualified-id before ‘this’
  (*this).s(s);  {};
    ^~~~
Time.cpp:27:4: error: expected ‘)’ before ‘this’
Time.cpp:27:17: error: expected unqualified-id before ‘{’ token
  (*this).s(s);  {};
                 ^
</code>```

As a noob I have no idea what is going on. On googling ,I only find one stackoverflow link which also doesn't help.

答:
The way you have written it is not standard C++, hence the compiler diagnostic. Rewrite to the conventional syntax:

```<code>Time::Time(short y,short m,short d,short h,short mi,short s):
    y(y),
    m(m),
    // and so on, without a final comma
{
}
</code>```

The compiler is able to disambiguate between a function parameter and a class member variable in this instance: y(y) initialises member y with parameter y.

Finally, are you sure you want signed types for time-like members?

Body:
I have this constructor which give an error on compilation :-

Time::Time(short y,short m,short d,short h,short mi,short s):
        (*this).y(y);
        (*this).m(m);
        (*this).d(d);
        (*this).h(h);
        (*this).mi(mi);
        (*this).s(s);   {};

here is full error:-

Time.cpp: In constructor ‘Time::Time(short int, short int, short int, short int, short int, short int)’:
Time.cpp:22:2: error: anachronistic old-style base class initializer [-fpermissive]
  (*this).y(y);
  ^
Time.cpp:21:61: error: unnamed initializer for ‘Time’, which has no base classes
 Time::Time(short y,short m,short d,short h,short mi,short s):
                                                             ^
Time.cpp:22:9: error: expected ‘{’ before ‘.’ token
  (*this).y(y);
         ^
Time.cpp: At global scope:
Time.cpp:22:9: error: expected unqualified-id before ‘.’ token
Time.cpp:23:4: error: expected unqualified-id before ‘this’
  (*this).m(m);
    ^~~~
Time.cpp:23:4: error: expected ‘)’ before ‘this’
Time.cpp:24:4: error: expected unqualified-id before ‘this’
  (*this).d(d);
    ^~~~
Time.cpp:24:4: error: expected ‘)’ before ‘this’
Time.cpp:25:4: error: expected unqualified-id before ‘this’
  (*this).h(h);
    ^~~~
Time.cpp:25:4: error: expected ‘)’ before ‘this’
Time.cpp:26:4: error: expected unqualified-id before ‘this’
  (*this).mi(mi);
    ^~~~
Time.cpp:26:4: error: expected ‘)’ before ‘this’
Time.cpp:27:4: error: expected unqualified-id before ‘this’
  (*this).s(s);  {};
    ^~~~
Time.cpp:27:4: error: expected ‘)’ before ‘this’
Time.cpp:27:17: error: expected unqualified-id before ‘{’ token
  (*this).s(s);  {};
                 ^

As a noob I have no idea what is going on. On googling ,I only find one stackoverflow link which also doesn't help.

Body:
The way you have written it is not standard C++, hence the compiler diagnostic. Rewrite to the conventional syntax:

Time::Time(short y,short m,short d,short h,short mi,short s):
    y(y),
    m(m),
    // and so on, without a final comma
{
}

The compiler is able to disambiguate between a function parameter and a class member variable in this instance: y(y) initialises member y with parameter y.

Finally, are you sure you want signed types for time-like members?


问:
Need to submit a form without the onsubmit and include the action
Here is a form

```<code><cfoutput>
<form name    = "xrefform"
    id        = "xrefform"
    action    = ""
    method    = "post"
    onsubmit  = "return submitx('#coltop#', '#col#')">
</cfoutput>
</code>```

There are two different way to submit it:
1) when you want the data in the form to be placed in a MySql Table
2) when you want the data to be deleted from the Mysql Table

For the first case I have

```<code><input type     = "Submit"
        name    = "SubmitXref"
        class   = "submitbut"
        value   = "Submit"
        onclick = "aasubmit('xref2.cfm')">
</code>```

with corresponding javascript:

```<code> function aasubmit(target) {
   document.xrefform.action = target;
 }//end function aasubmit
</code>```

This works fine.

For the delete case I have

```<code><input   type    = "Submit"
         id      = "delbut"
         class   = "onoffbut"
         value   = "delete"
         onclick = "aasubmit('repdel.cfm')">
</code>```

This has a problem, which is that the submitx() javascript runs, and in this case I don't want it to.

I find references that say using the document.form.submit() method will avoid running the onsubmit function.  But I can't figure out how to indicate the action.

Can someone show me how to do this?

After fussing around some more I found the answer:

For the delete button --which needs to evade the onsubmit script --here is the HTML:

```<code>   <input type    = "button"
        id      = "delbut"
        value   = "Delete this item"
        onclick = "buttonsubmit('xrefdel.cfm', 'xrefform')">
</code>```

And here is the javascript.

```<code>function buttonsubmit(target, source) {
var q = document.getElementById(source);
q.action = target;
q.submit();
}
</code>```

This works perfectly.  The ordinary submit honors the onsubmit script, the delete button skips it.

答:
Do something like this in the form:

```<code><input name="action" value="save" id="action" type="hidden">
<button type="submit" class="button button-basic-green" onclick="document.getElementById('action').value='save';"><span class="fa fa-save"></span> Save</button>
<button type="submit" class="button button-basic" onclick="document.getElementById('action').value='reload';"><span class="fa fa-repeat"></span> Save & Reload</button>
<button type="submit" class="button button-basic" onclick="document.location.href='./';return false;"><span class="fa fa-arrow-circle-o-left"></span> Cancel</button>
</code>```

Note the default action of "save". Gets you something like:

Then in the singular form-action page, check to see what the form.action is and process accordingly.

```<code><cfif form.action eq "reload">
    <cfset loc="page.cfm?id=#form.id#">
<cfelse>
    <cfset loc="./">
</cfif>
</code>```

Body:
Here is a form

<cfoutput>
<form name    = "xrefform"
    id        = "xrefform"
    action    = ""
    method    = "post"
    onsubmit  = "return submitx('#coltop#', '#col#')">
</cfoutput>

There are two different way to submit it:
1) when you want the data in the form to be placed in a MySql Table
2) when you want the data to be deleted from the Mysql Table

For the first case I have

<input type     = "Submit"
        name    = "SubmitXref"
        class   = "submitbut"
        value   = "Submit"
        onclick = "aasubmit('xref2.cfm')">

with corresponding javascript:

 function aasubmit(target) {
   document.xrefform.action = target;
 }//end function aasubmit

This works fine.

For the delete case I have

<input   type    = "Submit"
         id      = "delbut"
         class   = "onoffbut"
         value   = "delete"
         onclick = "aasubmit('repdel.cfm')">

This has a problem, which is that the submitx() javascript runs, and in this case I don't want it to.

I find references that say using the document.form.submit() method will avoid running the onsubmit function.  But I can't figure out how to indicate the action.

Can someone show me how to do this?

After fussing around some more I found the answer:

For the delete button --which needs to evade the onsubmit script --here is the HTML:

   <input type    = "button"
        id      = "delbut"
        value   = "Delete this item"
        onclick = "buttonsubmit('xrefdel.cfm', 'xrefform')">

And here is the javascript.

function buttonsubmit(target, source) {
var q = document.getElementById(source);
q.action = target;
q.submit();
}

This works perfectly.  The ordinary submit honors the onsubmit script, the delete button skips it.

Body:
Do something like this in the form:

<input name="action" value="save" id="action" type="hidden">
<button type="submit" class="button button-basic-green" onclick="document.getElementById('action').value='save';"><span class="fa fa-save"></span> Save</button>
<button type="submit" class="button button-basic" onclick="document.getElementById('action').value='reload';"><span class="fa fa-repeat"></span> Save &amp; Reload</button>
<button type="submit" class="button button-basic" onclick="document.location.href='./';return false;"><span class="fa fa-arrow-circle-o-left"></span> Cancel</button>

Note the default action of "save". Gets you something like:

Then in the singular form-action page, check to see what the form.action is and process accordingly.

<cfif form.action eq "reload">
    <cfset loc="page.cfm?id=#form.id#">
<cfelse>
    <cfset loc="./">
</cfif>


问:
Vector filled with different object
I've some trouble with the following task.

At first I had a vector with just one type of objects(bikes).

I could use this objects to put them into a class and used a function to print out the brands and model of those bikes like this:

```<code>for (Bike* b1  : vecBikes) {
        b1->printBrandModel();
    }
</code>```

My class's name is Bike.

From this class I derived two new classes, which are MTB for Mountain Bikes and E_Bikes.
I read the file again and stored them either as MTB or E_Bike objects into my vector like this:

```<code>while (inputFile >> brand >> model >> modelyear >> price >> type) {

    if (type == "MTB") {
        vecBikes.push_back(new MTB(brand, model, modelyear, price));
    }
    else {
       inputFile >> energy;
       vecBikes.push_back(new E_Bike(brand, model, modelyear, price, energy));
    }
    getline(inputFile, buffer);
}
</code>```

Now I want to do pretty the same. But this time I want to print eihter MTB's or E_Bikes from this vector.

Maybe a dynamic_cast is the way to go but I'm a complete beginner on this subject and all those examples books and websites can't help me somehow.
I hope you can help me out on this ;-)

答:
You can use dynamic_cast for determining the object type as below:

```<code>for (Bike* b1  : vecBikes) {
    if(dynamic_cast<MTB*>(b1) != nullptr) {
            b1->print_MBT();
    } else if (dynamic_cast<E_Bikes*>(b1) != nullptr) {
            b1->print_E_Bikes();
    } else cout << "error" << endl;
}
</code>```

Moreover, if you define a pure virtual method printBrandModel in your base class, you can(must) implement it as desired in derived classes. So you can use the first code without determining the instance belongs to which class.

```<code>for (Bike* b1  : vecBikes) {
     b1->printBrandModel();
}
</code>```

Title: Vector filled with different object

Body:
I've some trouble with the following task.

At first I had a vector with just one type of objects(bikes).

I could use this objects to put them into a class and used a function to print out the brands and model of those bikes like this:

for (Bike* b1  : vecBikes) {
        b1->printBrandModel();
    }

My class's name is Bike.

From this class I derived two new classes, which are MTB for Mountain Bikes and E_Bikes.
I read the file again and stored them either as MTB or E_Bike objects into my vector like this:

while (inputFile >> brand >> model >> modelyear >> price >> type) {

    if (type == "MTB") {
        vecBikes.push_back(new MTB(brand, model, modelyear, price));
    }
    else {
       inputFile >> energy;
       vecBikes.push_back(new E_Bike(brand, model, modelyear, price, energy));
    }
    getline(inputFile, buffer);
}

Now I want to do pretty the same. But this time I want to print eihter MTB's or E_Bikes from this vector.

Maybe a dynamic_cast is the way to go but I'm a complete beginner on this subject and all those examples books and websites can't help me somehow.
I hope you can help me out on this ;-)

Body:
You can use dynamic_cast for determining the object type as below:

for (Bike* b1  : vecBikes) {
    if(dynamic_cast<MTB*>(b1) != nullptr) {
            b1->print_MBT();
    } else if (dynamic_cast<E_Bikes*>(b1) != nullptr) {
            b1->print_E_Bikes();
    } else cout << "error" << endl;
}

Moreover, if you define a pure virtual method printBrandModel in your base class, you can(must) implement it as desired in derived classes. So you can use the first code without determining the instance belongs to which class.

for (Bike* b1  : vecBikes) {
     b1->printBrandModel();
}


问:
How to configure Gradle's incremental build with annotation processor
I want to use QueryDSL annotation processor as a part of my build process. How can I get rid of unnecessary annotation processor compile&run everytime i change any class? I want QueryDSL to generate Q-* classes only if some related classes are changed.

This always running annotation processor has negative impact on our build process time and it looks like incremental building is not working if annotation processor has to run.

Thank you.

答:
AFAIK this is currently not possible: see this blog post - section "Incremental compile with annotation processors":

...with annotation processors, Gradle does not know which files they are going to generate. Neither does it know where and based on what conditions. Therefore Grade disables the Java incremental compiler if annotation processors are in use

Related issue for Gradle: Make incremental compile efficient in the presence of Annotation Processors #1320

The Blog post also mentions a possible Workaround:

It is however possible to limit the impact of this to the set of classes that really use annotation processors. In short, you can declare a different source set, with a different compile task, that will use the annotation processor, and leave the other compile tasks without any kind of annotation processing

However, this seems to be quite some work, so I haven't used this yet.


问:
Android How to add ScrollView? ScrollView shits layout
I have a problem with adding ScrollView into my layout. So, when I add ScrollView (match_parent) into my layout, it shifts my layout into right side. Hereby, I see only background picture on the screen of the device. All Buttons, Textviews, and Edittexts shifted into right. By the way, I wrote code for only portrait layout.

```<code><?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context="com.example.rauf.myapplication.Main2Activity"
android:background="@drawable/bii3">
<LinearLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:layout_editor_absoluteY="8dp"
    tools:layout_editor_absoluteX="8dp"
    android:paddingLeft="60dp"
    android:paddingRight="58dp">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="600dp"
        android:layout_weight="2"
        android:gravity="center"
        android:orientation="vertical"
        android:paddingTop="42dp">

        <ImageView
            android:layout_width="150dp"
            android:layout_height="150dp"
            android:layout_gravity="center"
            android:src="@drawable/kamera" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:gravity="center"
        android:orientation="vertical"
        android:layout_weight="0.5"
        android:paddingTop="35dp">
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">

            <ImageView
                android:id="@+id/imageView"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/message" />

            <EditText
                android:id="@+id/email"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textEmailAddress"
                android:background="#00000000"
                android:hint="email address"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/log"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/usrusr"/>
            <EditText
                android:id="@+id/username"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="text"
                android:background="#00000000"
                android:hint="username"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/red"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/pswrd"/>
            <EditText
                android:id="@+id/password"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textPassword"
                android:background="#00000000"
                android:hint="password"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/red2"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/pswrd"/>
            <EditText
                android:id="@+id/passwordconf"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textPassword"
                android:background="#00000000"
                android:hint="password confirm"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <Button
            android:id="@+id/button1"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:background="@drawable/button_background2"
            android:text="SIGN UP"
            android:onClick="clickButton2"
            android:clickable="true"
            android:layout_marginTop="20dp"
            android:textColor="#ffffff"/>
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:gravity="center"
            android:layout_marginTop="30dp"
            android:onClick="clickFunction2"
            android:text="Already have an Account ?"
            android:textColor="#000000" />
    </LinearLayout>
</LinearLayout>
</LinearLayout>
</code>```

I want ScrollView for this:

But I end up with this:

答:
Try this :</p>

```<code><ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/scroller"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:fillViewport="true" >
</code>```

Body:
I have a problem with adding ScrollView into my layout. So, when I add ScrollView (match_parent) into my layout, it shifts my layout into right side. Hereby, I see only background picture on the screen of the device. All Buttons, Textviews, and Edittexts shifted into right. By the way, I wrote code for only portrait layout.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context="com.example.rauf.myapplication.Main2Activity"
android:background="@drawable/bii3">
<LinearLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:layout_editor_absoluteY="8dp"
    tools:layout_editor_absoluteX="8dp"
    android:paddingLeft="60dp"
    android:paddingRight="58dp">
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="600dp"
        android:layout_weight="2"
        android:gravity="center"
        android:orientation="vertical"
        android:paddingTop="42dp">

        <ImageView
            android:layout_width="150dp"
            android:layout_height="150dp"
            android:layout_gravity="center"
            android:src="@drawable/kamera" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:gravity="center"
        android:orientation="vertical"
        android:layout_weight="0.5"
        android:paddingTop="35dp">
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">

            <ImageView
                android:id="@+id/imageView"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/message" />

            <EditText
                android:id="@+id/email"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textEmailAddress"
                android:background="#00000000"
                android:hint="email address"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/log"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/usrusr"/>
            <EditText
                android:id="@+id/username"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="text"
                android:background="#00000000"
                android:hint="username"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/red"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/pswrd"/>
            <EditText
                android:id="@+id/password"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textPassword"
                android:background="#00000000"
                android:hint="password"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:padding="16dp">
            <ImageView
                android:id="@+id/red2"
                android:layout_width="20dp"
                android:layout_height="20dp"
                android:layout_gravity="center"
                android:src="@drawable/pswrd"/>
            <EditText
                android:id="@+id/passwordconf"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:inputType="textPassword"
                android:background="#00000000"
                android:hint="password confirm"
                android:layout_marginLeft="15dp"/>
        </LinearLayout>
        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="#4a5a71"></LinearLayout>
        <Button
            android:id="@+id/button1"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:background="@drawable/button_background2"
            android:text="SIGN UP"
            android:onClick="clickButton2"
            android:clickable="true"
            android:layout_marginTop="20dp"
            android:textColor="#ffffff"/>
        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:gravity="center"
            android:layout_marginTop="30dp"
            android:onClick="clickFunction2"
            android:text="Already have an Account ?"
            android:textColor="#000000" />
    </LinearLayout>
</LinearLayout>
</LinearLayout>

I want ScrollView for this:

But I end up with this:

Body:
Try this :

<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/scroller"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:fillViewport="true" >