Add Current YTD / Total Last Year comparison columns

We have two types of records: externals (acc), and internals (referred just as records).

Currently my query returns (acc) monthly totals, and (acc) totals from the date of implementation of the system (inception).

We now need to add total for (acc) Current YTD (separated by a ‘/’ forward slash), Total of (acc) Last Year.

The same for the internal records which in the query are the columns [Last Month], and [Inception Count of Records].

Add total for (internal) Current YTD (separated by a ‘/’ forward slash), Total of (internal) Last Year.

I think what I need to do is to add a few more sub-selects to get the numbers I need, but I am having trouble understanding how that part is structured and then used along what the columns that are already there.

I am including my current query that retrieves only current monthly total, and totals from the date of inception of both record types (external, and internal).

I hope all of this makes sense, because I would like to ask for some help so that I can complete this query. Any example or modification to my query, or some guidance would be awesome.

Query – Revised WHERE clause and AND condition since using sample data already filtered (to certain degree) -.

SELECT
    [RecordTypes].[Agency]                                                          [Agency]
,   [RecordTypes].[Record Type]                                                     [Record Type]
,   ISNULL([MonthlyRecords].[ACA], 0)                                               [ACA]
,   ISNULL([RecordTypes].[Inception Count ACA], 0)                                  [Inception Count ACA]
,   ISNULL([MonthlyRecords].[Count of Records], 0)                                  [Last Month]
,   [RecordTypes].[Inception Count of Records]                                      [Inception Count of Records]
,   ISNULL([RecordTypes].[Date of Last Record], '')                                 [Date of Last record]
,   ISNULL([RecordTypes].[Record Type Last Modified], '')                           [Date Record Type Last Modified]
,   IIF([RecordTypes].[ACA Enabled] IN ('VHAPP', 'VHSP', 'VHAI'), 'True', 'False')  [ACA Enabled]
FROM
    (SELECT
        CASE
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Permitted Use Site Plan Review', 'Minor Alteration to Approved Conditional Use', 'Minor Alteration to Approved Planned Unit Development') THEN 'Zoning'
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN ('Code Enforcement Mileage', 'Renewal Registration', 'Rental Property Emergency Contact', 'W&M License') THEN 'Building Inspection'
            WHEN [R3].[R1_PER_TYPE] IN ('Annexation', 'Condominium', 'Land Management', 'Land Use', 'Landmark', 'Subdivision') THEN 'Planning'          
            WHEN [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Equipment'               
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Vehicle'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Facility'
                OR [R3].[R1_APP_TYPE_ALIAS] = 'As-Built Request'
                OR LEFT([R3].[R1_PER_TYPE], 11) = 'Engineering' THEN 'Engineering'
            WHEN [R3].[R1_APP_TYPE_ALIAS] = 'Edible Landscape Permit' THEN 'Mayor'                      
            WHEN [R3].[R1_PER_GROUP] = 'LICENSES' 
                AND [R3].[R1_PER_TYPE] = 'Building Inspection' THEN 'Building'
            WHEN [R3].[R1_PER_TYPE] = 'Traffic' THEN 'Traffic Engineering'
            WHEN LEFT([R3].[R1_PER_TYPE], '7') = 'Streets' THEN 'Streets'
            WHEN [R3].[R1_PER_GROUP] = 'AnimalServices' THEN 'Health'           
            ELSE [R3].[R1_PER_TYPE]
        END                             [Agency]
     ,  CASE
            WHEN [B1].[B1_PER_SUB_TYPE] NOT IN ('Landlord Registration', 'Foreclosure Registration')
                AND [R3].[R1_PER_TYPE] = 'Building Inspection'
                AND [R3].[R1_PER_GROUP] = 'Licenses' THEN 'License '+[R3].[R1_APP_TYPE_ALIAS]
            ELSE REPLACE([R3].[R1_APP_TYPE_ALIAS], '/NA', '')
        END                             [Record Type]
    ,   COUNT([B1].[B1_ALT_ID])         [Inception Count of Records]
    ,   COUNT([B1].[B1_ACCESS_BY_ACA])  [Inception Count ACA]
    ,   MAX([B1].[B1_FILE_DD])          [Date of Last Record]
    ,   MAX([R3].[REC_DATE])            [Record Type Last Modified]
    ,   MAX([R3].[R1_UDCODE3])          [ACA Enabled]
    FROM
        [R3APPTYP] [R3]
    LEFT OUTER JOIN [B1PERMIT] [B1]
        ON [R3].[SERV_PROV_CODE] =          [B1].[SERV_PROV_CODE]
            AND [R3].[R1_PER_GROUP] =       [B1].[B1_PER_GROUP]
            AND [R3].[R1_PER_TYPE] =        [B1].[B1_PER_TYPE]
            AND [R3].[R1_PER_SUB_TYPE] =    [B1].[B1_PER_SUB_TYPE]
            AND [R3].[R1_PER_CATEGORY] =    [B1].[B1_PER_CATEGORY]          
    WHERE 
        [R3].[SERV_PROV_CODE] = 'MISOULA'
        AND [R3].[REC_STATUS] = 'A'
    GROUP BY
        CASE
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Permitted Use Site Plan Review', 'Minor Alteration to Approved Conditional Use', 'Minor Alteration to Approved Planned Unit Development') THEN 'Zoning'
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Code Enforcement Mileage', 'Renewal Registration', 'Rental Property Emergency Contact', 'W&M License') THEN 'Building Inspection'
            WHEN [R3].[R1_PER_TYPE] IN('Annexation', 'Condominium', 'Land Management', 'Land Use', 'Landmark', 'Subdivision') THEN 'Planning'       
            WHEN [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Equipment'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Vehicle'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Facility'
                OR [R3].[R1_APP_TYPE_ALIAS] = 'As-Built Request'
                OR LEFT([R3].[R1_PER_TYPE], 11) = 'Engineering' THEN 'Engineering'
            WHEN [R3].[R1_APP_TYPE_ALIAS] = 'Edible Landscape Permit' THEN 'Mayor'      
            WHEN [R3].[R1_PER_GROUP] = 'LICENSES'
                AND [R3].[R1_PER_TYPE] = 'Building Inspection' THEN 'Building'
            WHEN [R3].[R1_PER_TYPE] = 'Traffic' THEN 'Traffic Engineering'
            WHEN LEFT([R3].[R1_PER_TYPE], '7') = 'Streets' THEN 'Streets'
            WHEN [R3].[R1_PER_GROUP] = 'AnimalServices' THEN 'Health'
            ELSE [R3].[R1_PER_TYPE]
        END
    ,   CASE
            WHEN [B1].[B1_PER_SUB_TYPE] NOT IN('Landlord Registration', 'Foreclosure Registration')
                AND [R3].[R1_PER_TYPE] = 'Building Inspection'
                AND [R3].[R1_PER_GROUP] = 'Licenses' THEN 'License '+[R3].[R1_APP_TYPE_ALIAS]
            ELSE REPLACE([R3].[R1_APP_TYPE_ALIAS], '/NA', '')
        END) 
            [RecordTypes]
LEFT OUTER JOIN
    (SELECT
        CASE
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Permitted Use Site Plan Review', 'Minor Alteration to Approved Conditional Use', 'Minor Alteration to Approved Planned Unit Development') THEN 'Zoning'
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Code Enforcement Mileage', 'Renewal Registration', 'Rental Property Emergency Contact', 'W&M License') THEN 'Building Inspection'         
            WHEN [R3].[R1_PER_TYPE] IN('Annexation', 'Condominium', 'Land Management', 'Land Use', 'Landmark', 'Subdivision') THEN 'Planning'           
            WHEN [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Equipment'               
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Vehicle'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Facility'              
                OR [R3].[R1_APP_TYPE_ALIAS] = 'As-Built Request'
                OR LEFT([R3].[R1_PER_TYPE], 11) = 'Engineering' THEN 'Engineering'
            WHEN [R3].[R1_APP_TYPE_ALIAS] = 'Edible Landscape Permit' THEN 'Mayor'                               
            WHEN [R3].[R1_PER_GROUP] = 'LICENSES'
                AND [R3].[R1_PER_TYPE] = 'Building Inspection' THEN 'Building'
            WHEN [R3].[R1_PER_TYPE] = 'Traffic' THEN 'Traffic Engineering'
            WHEN LEFT([R3].[R1_PER_TYPE], '7') = 'Streets' THEN 'Streets'
            WHEN [R3].[R1_PER_GROUP] = 'AnimalServices' THEN 'Health'
            ELSE [R3].[R1_PER_TYPE]
        END [Agency]
     ,  CASE
            WHEN [B1].[B1_PER_SUB_TYPE] NOT IN ('Landlord Registration', 'Foreclosure Registration')
                AND [R3].[R1_PER_TYPE] = 'Building Inspection'
                AND [R3].[R1_PER_GROUP] = 'Licenses' THEN 'License '+[R3].[R1_APP_TYPE_ALIAS]
            ELSE REPLACE([R3].[R1_APP_TYPE_ALIAS], '/NA', '')
        END [Record Type]
     ,  COUNT([B1].[B1_ALT_ID]) [Count of Records]
     ,  COUNT([B1].[B1_ACCESS_BY_ACA]) [ACA]
     FROM
        [R3APPTYP] [R3]
     LEFT OUTER JOIN [B1PERMIT] [B1]
        ON [R3].[SERV_PROV_CODE] = [B1].[SERV_PROV_CODE]
            AND [R3].[R1_PER_GROUP] = [B1].[B1_PER_GROUP]
            AND [R3].[R1_PER_TYPE] = [B1].[B1_PER_TYPE]
            AND [R3].[R1_PER_SUB_TYPE] = [B1].[B1_PER_SUB_TYPE]
            AND [R3].[R1_PER_CATEGORY] = [B1].[B1_PER_CATEGORY]
            AND LEFT([B1_ALT_ID], 3) <> 'TMP'
            AND DATEPART([m], [B1].[B1_FILE_DD]) = DATEPART([m], DATEADD([m], -1, GETDATE()))
            AND DATEPART([yyyy], [B1].[B1_FILE_DD]) = DATEPART([yyyy], DATEADD([m], -1, GETDATE()))                   
     WHERE 
        [R3].[SERV_PROV_CODE] = 'MISOULA'
        AND [R3].[REC_STATUS] = 'A' 
     GROUP BY
        CASE
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Permitted Use Site Plan Review', 'Minor Alteration to Approved Conditional Use', 'Minor Alteration to Approved Planned Unit Development') THEN 'Zoning'
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Code Enforcement Mileage', 'Renewal Registration', 'Rental Property Emergency Contact', 'W&M License') THEN 'Building Inspection'         
            WHEN [R3].[R1_PER_TYPE] IN('Annexation', 'Condominium', 'Land Management', 'Land Use', 'Landmark', 'Subdivision') THEN 'Planning'           
            WHEN [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Equipment'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Vehicle'
                OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Facility'
                OR [R3].[R1_APP_TYPE_ALIAS] = 'As-Built Request'
                OR LEFT([R3].[R1_PER_TYPE], 11) = 'Engineering' THEN 'Engineering'
            WHEN [R3].[R1_APP_TYPE_ALIAS] = 'Edible Landscape Permit' THEN 'Mayor'          
            WHEN [R3].[R1_PER_GROUP] = 'LICENSES'
                AND [R3].[R1_PER_TYPE] = 'Building Inspection' THEN 'Building'
            WHEN [R3].[R1_PER_TYPE] = 'Traffic' THEN 'Traffic Engineering'
            WHEN LEFT([R3].[R1_PER_TYPE], '7') = 'Streets' THEN 'Streets'
            WHEN [R3].[R1_PER_GROUP] = 'AnimalServices' THEN 'Health'
            ELSE [R3].[R1_PER_TYPE]
        END
     ,  CASE
            WHEN [B1].[B1_PER_SUB_TYPE] NOT IN('Landlord Registration', 'Foreclosure Registration')
                AND [R3].[R1_PER_TYPE] = 'Building Inspection'
                AND [R3].[R1_PER_GROUP] = 'Licenses' THEN 'License '+[R3].[R1_APP_TYPE_ALIAS]
            ELSE REPLACE([R3].[R1_APP_TYPE_ALIAS], '/NA', '')
        END) 
            [MonthlyRecords]     
    ON [RecordTypes].[Agency] = [MonthlyRecords].[Agency]   
        AND [RecordTypes].[Record Type] = [MonthlyRecords].[Record Type]
ORDER BY
    [RecordTypes].[Agency]
,   [RecordTypes].[Record Type];

Table Definition – B1PERMIT

CREATE TABLE [dbo].[B1PERMIT](
    [SERV_PROV_CODE] [varchar](15) NOT NULL,
    [B1_PER_GROUP] [varchar](30) NOT NULL,
    [B1_PER_TYPE] [varchar](30) NOT NULL,
    [B1_PER_SUB_TYPE] [varchar](30) NOT NULL,
    [B1_PER_CATEGORY] [varchar](30) NOT NULL,
    [B1_FILE_DD] [datetime] NULL,
    [B1_ALT_ID] [varchar](30) NULL,
    [B1_ACCESS_BY_ACA] [varchar](1) NULL
) ON [PRIMARY]

Table Definition – R3APPTYP

CREATE TABLE [dbo].[R3APPTYP](
    [SERV_PROV_CODE] [varchar](15) NOT NULL,
    [R1_PER_GROUP] [varchar](30) NOT NULL,
    [R1_PER_TYPE] [varchar](30) NOT NULL,
    [R1_PER_SUB_TYPE] [varchar](30) NOT NULL,
    [R1_PER_CATEGORY] [varchar](30) NOT NULL,
    [REC_DATE] [datetime] NOT NULL,
    [R1_UDCODE3] [varchar](12) NULL,
    [REC_STATUS] [varchar](1) NULL,
    [R1_APP_TYPE_ALIAS] [varchar](255) NULL
) ON [PRIMARY]

Sample Data – B1PERMIT

MISSOULA    Permitting  Engineering Erosion Control NA  2018-07-16 00:00:00.000 ENG100-2017-04471   NULL
MISSOULA    Permitting  Engineering Sewer Plug  NA  2017-01-12 00:00:00.000 ENGSRP-2016-04904   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00001   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00003   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00004   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00007   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00009   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00012   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-01 00:00:00.000 PRKDGP-2017-00014   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00017   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00021   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00022   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00024   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00028   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-02 00:00:00.000 PRKDGP-2017-00030   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2017-01-03 00:00:00.000 PRKDGP-2017-00032   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-01 00:00:00.000 PRKDGP-2018-00015   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-02 00:00:00.000 PRKDGP-2018-00019   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-04 00:00:00.000 PRKDGP-2018-00156   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-05 00:00:00.000 PRKDGP-2018-00173   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-05 00:00:00.000 PRKDGP-2018-00202   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-06 00:00:00.000 PRKDGP-2018-00224   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-07 00:00:00.000 PRKDGP-2018-00235   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-07 00:00:00.000 PRKDGP-2018-00238   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-07 00:00:00.000 PRKDGP-2018-00241   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-07 00:00:00.000 PRKDGP-2018-00244   Y
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-08 00:00:00.000 PRKDGP-2018-00257   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-08 00:00:00.000 PRKDGP-2018-00258   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-08 00:00:00.000 PRKDGP-2018-00259   NULL
MISSOULA    Permitting  Parks   DiscGolf    NA  2018-01-08 00:00:00.000 PRKDGP-2018-00260   NULL

Sample Data – R3APPTYP

    MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    Licenses    Civil Rights    AA Plan Exempt  2017-04-26 15:56:27.303 VHAPP   A   AA Plan Exemption (14 or fewer)
MISSOULA    _Enforcement    Building Inspection Housing Rental Prop Emergency Contacts  2018-02-26 16:36:45.987 NA  A   _Enforcement/Building Inspection/Housing/Rental Prop Emergency Contacts
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application
MISSOULA    Land    Landmark    NA  NA  2018-04-18 09:13:50.787 VHAPP   A   Certificate of Appropriateness Application

Answer

I think that you can do it this way (without multiple subqueries):

WITH
  BaseData ([Agency], [Record Type], [B1_ALT_ID], [B1_ACCESS_BY_ACA], [B1_FILE_DD], [REC_DATE], [R1_UDCODE3], ThisYear, LastYear, LastMonth) AS (
    SELECT
        CASE
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN('Permitted Use Site Plan Review', 'Minor Alteration to Approved Conditional Use', 'Minor Alteration to Approved Planned Unit Development') THEN 'Zoning'
            WHEN [R3].[R1_APP_TYPE_ALIAS] IN ('Code Enforcement Mileage', 'Renewal Registration', 'Rental Property Emergency Contact', 'W&M License') THEN 'Building Inspection'
            WHEN [R3].[R1_PER_TYPE] IN ('Annexation', 'Condominium', 'Land Management', 'Land Use', 'Landmark', 'Subdivision') THEN 'Planning'          
            WHEN [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Equipment'               
              OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Vehicle'
              OR [R3].[R1_PER_GROUP]+'/'+[R3].[R1_PER_TYPE] = 'AMS/Facility'
              OR [R3].[R1_APP_TYPE_ALIAS] = 'As-Built Request'
              OR LEFT([R3].[R1_PER_TYPE], 11) = 'Engineering' THEN 'Engineering'
            WHEN [R3].[R1_APP_TYPE_ALIAS] = 'Edible Landscape Permit' THEN 'Mayor'                      
            WHEN [R3].[R1_PER_GROUP] = 'LICENSES' 
                AND [R3].[R1_PER_TYPE] = 'Building Inspection' THEN 'Building'
            WHEN [R3].[R1_PER_TYPE] = 'Traffic' THEN 'Traffic Engineering'
            WHEN LEFT([R3].[R1_PER_TYPE], '7') = 'Streets' THEN 'Streets'
            WHEN [R3].[R1_PER_GROUP] = 'AnimalServices' THEN 'Health'           
            ELSE [R3].[R1_PER_TYPE]
        END [Agency]
     ,  CASE
            WHEN [B1].[B1_PER_SUB_TYPE] NOT IN ('Landlord Registration', 'Foreclosure Registration')
             AND [R3].[R1_PER_TYPE] = 'Building Inspection'
             AND [R3].[R1_PER_GROUP] = 'Licenses' THEN 'License '+[R3].[R1_APP_TYPE_ALIAS]
            ELSE REPLACE([R3].[R1_APP_TYPE_ALIAS], '/NA', '')
        END [Record Type]
    ,   [B1].[B1_ALT_ID]
    ,   [B1].[B1_ACCESS_BY_ACA]
    ,   [B1].[B1_FILE_DD]
    ,   [R3].[REC_DATE]
    ,   [R3].[R1_UDCODE3]
    ,   CASE
          WHEN [B1].[B1_FILE_DD] >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
          THEN 1 ELSE 0
        END
    ,   CASE
          WHEN [B1].[B1_FILE_DD] >= DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0)
           AND [B1].[B1_FILE_DD] < DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
          THEN 1 ELSE 0
        END
    , CASE
        WHEN [B1].[B1_FILE_DD] >= DATEADD(m, DATEDIFF(m, 0, GETDATE())-1, 0)
         AND [B1].[B1_FILE_DD] < DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
        THEN 1 ELSE 0
      END

    FROM [R3APPTYP] [R3]
      LEFT OUTER JOIN [B1PERMIT] [B1]
        ON [R3].[SERV_PROV_CODE] =        [B1].[SERV_PROV_CODE]
          AND [R3].[R1_PER_GROUP] =       [B1].[B1_PER_GROUP]
          AND [R3].[R1_PER_TYPE] =        [B1].[B1_PER_TYPE]
          AND [R3].[R1_PER_SUB_TYPE] =    [B1].[B1_PER_SUB_TYPE]
          AND [R3].[R1_PER_CATEGORY] =    [B1].[B1_PER_CATEGORY]          

    WHERE [R3].[SERV_PROV_CODE] = 'MISOULA'
      AND [R3].[REC_STATUS] = 'A'
  )

SELECT
    [Agency]
,   [Record Type]
,   ISNULL(COUNT(CASE LastMonth WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)   [ACA]
,   ISNULL(COUNT(CASE LastYear WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)    [Last Year ACA]
,   ISNULL(COUNT(CASE ThisYear WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)    [YTD ACA]
,   ISNULL(COUNT([B1_ACCESS_BY_ACA]), 0)                                            [Inception Count ACA]
,   ISNULL(COUNT(CASE LastMonth WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)          [Last Month]
,   ISNULL(COUNT(CASE LastYear WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)           [Last Year Count of Records]
,   ISNULL(COUNT(CASE ThisYear WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)           [YTD Count of Records]
,   ISNULL(COUNT([B1_ALT_ID]), 0)                                                   [Inception Count of Records]
,   ISNULL(MAX([B1_FILE_DD]), '')                                                   [Date of Last record]
,   ISNULL(MAX([REC_DATE]), '')                                                     [Date Record Type Last Modified]
,   IIF(MAX([R1_UDCODE3]) IN ('VHAPP', 'VHSP', 'VHAI'), 'True', 'False')            [ACA Enabled]

FROM BaseData
GROUP BY [Agency],  [Record Type]
ORDER BY [Agency],  [Record Type];

If you really need the TMP condition for the “last month” part, you can easily include it in the respective CASE expression.

To combine the new fields (added)

To combine the new fields and make them character strings, I suggest to – for simplicity – use another CTE. Add a comma behind the closing bracket of the first CTE (BaseData) and replace the SELECT statement by a second CTE and a final SELECT statement as follows:

  Results AS (
    SELECT
        [Agency]
    ,   [Record Type]
    ,   ISNULL(COUNT(CASE LastMonth WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)   [ACA]
    ,   ISNULL(COUNT(CASE LastYear WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)    [Last Year ACA]
    ,   ISNULL(COUNT(CASE ThisYear WHEN 1 THEN [B1_ACCESS_BY_ACA] ELSE NULL END), 0)    [YTD ACA]
    ,   ISNULL(COUNT([B1_ACCESS_BY_ACA]), 0)                                            [Inception Count ACA]
    ,   ISNULL(COUNT(CASE LastMonth WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)          [Last Month]
    ,   ISNULL(COUNT(CASE LastYear WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)           [Last Year Count of Records]
    ,   ISNULL(COUNT(CASE ThisYear WHEN 1 THEN [B1_ALT_ID] ELSE NULL END), 0)           [YTD Count of Records]
    ,   ISNULL(COUNT([B1_ALT_ID]), 0)                                                   [Inception Count of Records]
    ,   ISNULL(MAX([B1_FILE_DD]), '')                                                   [Date of Last record]
    ,   ISNULL(MAX([REC_DATE]), '')                                                     [Date Record Type Last Modified]
    ,   IIF(MAX([R1_UDCODE3]) IN ('VHAPP', 'VHSP', 'VHAI'), 'True', 'False')            [ACA Enabled]

    FROM BaseData
    GROUP BY [Agency],  [Record Type]
  )

SELECT
     [Agency]
  ,  [Record Type]
  ,  [ACA]
  ,  [Inception Count ACA]
  ,  CASE [YTD ACA]
       WHEN 0 THEN 'NA' ELSE CONVERT(nvarchar(50), [YTD ACA])
     END 
     + N'/' + 
     CASE [Last Year ACA]
       WHEN 0 THEN 'NA' ELSE CONVERT(nvarchar(50), [Last Year ACA])
     END [YTD/LY ACA]
  ,  [Last Month]
  ,  [Inception Count of Records]
  ,  CASE [YTD Count of Records]
       WHEN 0 THEN 'NA' ELSE CONVERT(nvarchar(50), [YTD Count of Records])
     END 
     + N'/' + 
     CASE [Last Year Count of Records]
       WHEN 0 THEN 'NA' ELSE CONVERT(nvarchar(50), [Last Year Count of Records])
     END [YTD/LY of Records]
  ,  [Date of Last record]
  ,  [Date Record Type Last Modified]
  ,  [ACA Enabled]

FROM Results
ORDER BY [Agency],  [Record Type];