Our database is on SQL Server 2014. I had the exact same issue as SQL join table to itself to get data for previous year, and was so happy to find the answer. So I setup my query:
select distinct
coalesce (a.year, b.year+1) [Year]
, coalesce(a.Month, b.Month) [Month]
, coalesce(a.account, b.account) [Account]
, case when a.sales > 0
then a.sales
else 0 end [Sales MTD]
, case when b.sales > 0
then b.sales
else 0 end [Sales Previous Year]
, (case when b.sales!= 0
then cast((case when a.sales > 0
then a.sales
else 0 end-b.sales)/b.sales*100 as decimal(10,1))
else 0 end) [% Difference]
, sum(a.sales) over (Partition by a.account, a.year order by a.month) [Account Sales YTD]
from yourtable a
full join yourtable b
on a.account = b.account
and a.month = b.month
and a.year = b.year+1
The output is giving me results for months in 2022 that haven't happened yet (and starting 2023 too). https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=9a2c29f1af08f525a1acee16b0efa2fd
I tried adding and b.[year]+1<=year(getdate()) to my join filters, but that doesn't seem to affect output at all. I also tried writing a where clause, that ended up with a case that made my head spin.
How can I prevent future dates from showing up in the end result? Bonus question: Can I make my YTD calculation accept a 0 as a value for any lines added due to sales history?
Add a WHERE clause
...
where datefromparts(coalesce(a.year, b.year+1), coalesce(a.Month, b.Month), 1) <= cast(getdate() as date)