monthwise report, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

DECLARE @range_start DATE = '1/1/2017'
DECLARE @range_end DATE = '12/31/2017'

create table client_main (clientcode int);

insert into client_main values (1);
insert into client_main values (2);

create table client_invoices (clientcode int, invnumber int, invoice_revision int, invdate date, tax_credit int);

insert into client_invoices values (1, 1, 0, '20170102', 10);
insert into client_invoices values (1, 1, 1, '20170102', 20);
insert into client_invoices values (1, 1, 1, '20170102', 30);
insert into client_invoices values (1, 1, 0, '20170103', 40);
insert into client_invoices values (1, 1, 0, '20170304', 50);
insert into client_invoices values (2, 1, 0, '20170320', 60);
insert into client_invoices values (2, 1, 0, '20170504', 70);
insert into client_invoices values (2, 1, 1, '20170504', 80);
insert into client_invoices values (2, 1, 0, '20170606', 90);
insert into client_invoices values (1, 1, 0, '20160122', 100);
insert into client_invoices values (2, 1, 0, '20160122', 200);

with last_revision_invoices as
(
  select top(1) with ties
    clientcode, 
    year(invdate) as year, 
    month(invdate) as month, 
    tax_credit
  from client_invoices ci 
  order by case when invoice_revision = max(invoice_revision) over (partition by clientcode, invdate, invnumber) then 1 else 2 end
)
, months as (
	select year(@range_start) as year, month(@range_start) as month
	union all
	select case when month < 12 then year else year + 1 end, (month % 12) + 1
	from months
	where year < year(@range_end) or (year = year(@range_end) and month < month(@range_end))
)
select c.clientcode, m.year, m.month, coalesce(cur.total, 0) as total_then, coalesce(prev.total, 0) as total_previous_year
from client_main c
cross join months m
left join
(
  select clientcode, year, month, sum(tax_credit) as total
  from  last_revision_invoices
  group by clientcode, year, month
) cur on cur.clientcode = c.clientcode and cur.year = m.year and cur.month = m.month
left join
(
  select clientcode, year, month, sum(tax_credit) as total
  from  last_revision_invoices
  group by clientcode, year, month
) prev on prev.clientcode = c.clientcode and prev.year = m.year - 1 and prev.month = m.month
order by c.clientcode, m.year, m.month;

drop table client_invoices;
drop table client_main;

View schema
Execution time: 0,05 sec, rows selected: 26, rows affected: 13, absolute service time: 0,2 sec