Filtering years out of data

Grafana Ver:9.5.1
I have a heatmap that is counting the coallace (which ever one is present) between two date fields.

Now that the year has changed, it is just combining 2023 and 2024’s data. Since I am taking either of the values for Resolved or Closed, how could I set this to either:

  1. Only show the most recent years data in the table
  2. Filter the data for a selected time frame (idk how thats possible since its one of two potential fields)

Would transforms be able to filter out the data?

Query A:

SELECT
CASE

    WHEN PG = 'Tier4' THEN 'Tier4'

    ELSE CAST(PG AS CHAR)

END AS PGs,

MAX(CASE WHEN Month = '1' THEN 0 ELSE NULL END) AS `January`,

MAX(CASE WHEN Month = '2' THEN 0 ELSE NULL END) AS `Feburary`,

MAX(CASE WHEN Month = '3' THEN 0 ELSE NULL END) AS `March`,

MAX(CASE WHEN Month = '4' THEN 0 ELSE NULL END) AS `April`,

MAX(CASE WHEN Month = '5' THEN 0 ELSE NULL END) AS `May`,

MAX(CASE WHEN Month = '6' THEN 0 ELSE NULL END) AS `June`,

MAX(CASE WHEN Month = '7' THEN 0 ELSE NULL END) AS `July`,

MAX(CASE WHEN Month = '8' THEN 0 ELSE NULL END) AS `August`,

MAX(CASE WHEN Month = '9' THEN 0 ELSE NULL END) AS `September`,

MAX(CASE WHEN Month = '10' THEN 0 ELSE NULL END) AS `October`,

MAX(CASE WHEN Month = '11' THEN 0 ELSE NULL END) AS `November`,

MAX(CASE WHEN Month = '12' THEN 0 ELSE NULL END) AS `December`

FROM (

SELECT 'Tier4' AS PG
UNION SELECT 'Tier3' 
UNION SELECT 'Tier1' 
UNION SELECT 'Tier9' 
UNION SELECT 'Tier8' 
UNION SELECT 'User'

) AS PGs

CROSS JOIN (

SELECT 'January' AS Month UNION SELECT 'Feburary' UNION SELECT 'March' UNION

SELECT 'April' UNION SELECT 'May' UNION SELECT 'June' UNION SELECT 'July' UNION

SELECT 'August' UNION SELECT 'September' UNION SELECT 'October' UNION SELECT 'November' UNION

SELECT 'December'

) AS months

GROUP BY PG

Query B:

select
CustomValue1 as PGs,
#if(CategoryId is NOT NULL,(Select Name From CatTable where CategoryId = Table.CategoryId), “No Category”) As Work_Unit_Category,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 1 THEN 1 ELSE 0 END), 0) AS January,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 2 THEN 1 ELSE 0 END), 0) AS Feburary,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 3 THEN 1 ELSE 0 END), 0) AS March,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 4 THEN 1 ELSE 0 END), 0) AS April,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 5 THEN 1 ELSE 0 END), 0) AS May,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 6 THEN 1 ELSE 0 END), 0) AS June,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 7 THEN 1 ELSE 0 END), 0) AS July,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 8 THEN 1 ELSE 0 END), 0) AS August,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 9 THEN 1 ELSE 0 END), 0) AS September,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 10 THEN 1 ELSE 0 END), 0) AS October,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 11 THEN 1 ELSE 0 END), 0) AS November,
COALESCE(SUM(CASE WHEN (SELECT MONTH(MIN(Convert_TZ(Created, ‘UTC’,‘US/Eastern’))) FROM History WHERE Id = Id AND (Detail like ‘% to ‘‘Resolved’’%’ or Detail like ‘% to ‘‘Closed’’%’)) = 12 THEN 1 ELSE 0 END), 0) AS December
FROM Table
WHERE
TenantId in ($TenantId)
AND TypeId = 3
AND (CategoryId NOT LIKE ‘27’ or CategoryId is null)
AND (Customvalue3 NOT LIKE ‘Monitoring’ or Customvalue3 is NULL)
AND (Customvalue3 NOT LIKE ‘Test’ or Customvalue3 is NULL)
AND (Description not like ‘Low/Informational %’ or Description is NULL)
AND (Description not like ‘Medium severity %’ or Description is NULL)
AND (Description not like ‘High Severity %’ or Description is NULL)
GROUP BY CustomValue1

I found that I needed to add a where statement that would only allow the data for the current month to be from the current year. This will give a true 12 month rolling chart.

AND ((YEAR(ResolvedDate) = YEAR(NOW()) AND MONTH(ResolvedDate) = MONTH(NOW()) AND ResolvedDate >= DATE_FORMAT(NOW(), ‘%Y-%m-01’))
OR
(YEAR(ResolvedDate) = YEAR(NOW()) - 1 AND ResolvedDate < DATE_FORMAT(NOW(), ‘%Y-%m-01’))
OR
(YEAR(ClosedDate) = YEAR(NOW()) AND MONTH(ClosedDate) = MONTH(NOW()) AND ClosedDate >= DATE_FORMAT(NOW(), ‘%Y-%m-01’))
OR
(YEAR(ClosedDate) = YEAR(NOW()) - 1 AND ClosedDate < DATE_FORMAT(NOW(), ‘%Y-%m-01’)))