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
Advertisement
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];