I have a query (Below) that has a sum of each month based on the Case statements in the select. I am looking to get a total of the results from the columns. The columns represent each month of the year and I want the last column to be the total of all months. I can seem to figure out how to do it.
Any help is appreciated! Also if you can figure out how to simplify the query, I am open to that too!
SELECT t.Tenant_hmy, Tenant_Code, t.Tenant_Name,
SUM((CASE WHEN (CAST('01/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('12/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 1 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Jan,
SUM((CASE WHEN (CAST('02/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('02/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 2 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Feb,
SUM((CASE WHEN (CAST('03/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('03/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 3 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Mar,
SUM((CASE WHEN (CAST('04/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('04/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 4 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Apr,
SUM((CASE WHEN (CAST('05/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('05/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 5 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) May,
SUM((CASE WHEN (CAST('06/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('06/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 6 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Jun,
SUM((CASE WHEN (CAST('07/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('07/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 7 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Jul,
SUM((CASE WHEN (CAST('08/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('08/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 8 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Aug,
SUM((CASE WHEN (CAST('09/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('09/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 9 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Sep,
SUM((CASE WHEN (CAST('10/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('10/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 10 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Oct,
SUM((CASE WHEN (CAST('11/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('11/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 11 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) Nov,
SUM((CASE WHEN (CAST('12/01/2022' AS date) BETWEEN start_date AND (CASE WHEN end_date is null THEN CAST('12/31/2022' as date) ELSE end_date END) OR (MONTH(start_date) = 12 AND
YEAR(start_date) = 2022)) THEN ISNULL(amount,0) ELSE 0 END)) [Dec]
FROM rent r, tenant t
WHERE r.prop_hmy = 637 AND t.Prop_Hmy = r.Prop_Hmy and T.Tenant_Hmy = R.Tenant_Hmy
AND 2022 BETWEEN YEAR(r.start_date) AND YEAR(r.End_Date)
AND r.Budget_ID = 7 AND r.Budget_ID = t.Budget_ID AND type IN ('base-rnt', 'free-rnt')
GROUP BY t.Tenant_Name, Tenant_Code, t.Tenant_hmy
