Summing sales dollars for most recent month and 2nd most recent month

Question

For each of the 12 months, I'm looking to create a field that sums the sales dollars at the account level for the most recent month and the 2nd most recent month based on the current date.

For example, given that today's date is 10/6/22, 'MostRecentNovember' would sum up sales from November 2021. '2ndMostRecentNovember' would sum up sales from November 2020. Once the current date moves into November 2022, this query would adjust to pull MostRecentNovember sales from 2022 and 2ndMostRecentNovember sales from 2021.

Conversely, given that today's date is 10/6/22 'MostRecentJune' would sum up sales from June 2022 and '2ndMostRecentJune' would sum up sales from June 2021.

Below is my attempt at this code, I think this gets partially there, but not sure it's exactly what I want

SELECT NovemberMostRecent_Value = 
       sum(case when datepart(year,tran_date) = datepart(year, getdate())
AND DATEPART(month, tran_date) = 11 then value else 0 end)
       NovemberSecondMostRecent_Value = 
       sum(case when datepart(year,tran_date) = datepart(year, getdate())-1
AND DATEPART(month, tran_date) = 11 then value else 0 end)

Here's a snippet of the source data table

account_no	tran_date	value
123	11/22/21	500
123	11/1/21	500
123	11/20/20	1500
123	6/3/22	5000
123	6/4/21	2000
456	11/3/20	525
456	11/4/21	125

Per Request in Comments. A table of desired Results

account_no	NovemberMostRecent	November2ndMostRecent	June MostRecent	June2ndMostRecent
123	1000	1500	5000	2000
456	125	525	0	0

Answer 1

Why don't you just sum up the sales then group by month and year for the last two years? Wouldn't that solve the problem?

Or you can show a table that depicts what you are trying to achieve.

Answer 2

This should work fine. Note: I only assume the account_no is the same for all the rows, if they are different, then you will need to pass it as a condition in the subquery.

WITH CTE AS
(SELECT (SELECT SUM(value) FROM tablename WHERE datepart(year, tran_date) = YEAR(getdate()) AND datepart(month, tran_date) = 11)
AS first_value,
(SELECT SUM(value) FROM tablename WHERE datepart(year, tran_date) = YEAR(getdate())-1 AND datepart(month, tran_date) = 11)
AS second_value,
(SELECT SUM(value) FROM tablename WHERE datepart(year, tran_date) = YEAR(getdate())-2 AND datepart(month, tran_date) = 11)
AS third_value)
SELECT IIF (first_value>0, first_value, second_value) AS NovemberMostRecent_Value,
IIF (first_value>0, second_value, third_value) AS NovemberSecondMostRecent_Value FROM CTE;