T-SQL Wrapping an increment date around a query

Question

I need to get a set of data from our database. This set needs to have one entry per month in a date range, even if that value is null. The total is a sum of all the previous totals to the beginning of the year. I managed to make one that I can use for a single month for all clients, but I needed it for a year range.

I found this post ->Generate a resultset of incrementing dates in TSQL

And modified it to read as:

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

SELECT DATEADD(day, number, @range_start
FROM 
    (SELECT DISTINCT number FROM master.dbo.spt_values
     WHERE name IS NULL
    ) n
WHERE dateadd(MONTH, number, @range_start) < @range_end    

And now I'm trying to wrap it around my query.

DECLARE @Year SMALLINT = 2017
DECLARE @Month TINYINT = 1

SELECT CLIENTCODE 
,(
    SELECT SUM(Tax_Credit) 
    FROM client_invoices ci 

    WHERE cm.CLIENTCODE = ci.CLIENTCODE 
        AND YEAR(InvDate) = @year 
        AND MONTH(InvDate) <= @month 
        AND Invoice_Revision = 
            (
                SELECT MAX(Invoice_Revision) 
                FROM client_invoices ci2 
                WHERE ci2.CLIENTCODE = ci.CLIENTCODE 
                    AND ci2.InvDate = ci.InvDate 
                    AND ci2.InvNumber = ci.InvNumber)

    ) AS Year_2017_Tax_Credit_Totals
,(
    SELECT SUM(Tax_Credit) 
    FROM client_invoices ci 

    WHERE cm.CLIENTCODE = ci.CLIENTCODE 
        AND YEAR(InvDate) = @year -1
        AND MONTH(InvDate) <= @month 
        AND Invoice_Revision = 
            (
                SELECT MAX(Invoice_Revision) 
                FROM client_invoices ci2 
                WHERE ci2.CLIENTCODE = ci.CLIENTCODE 
                    AND ci2.InvDate = ci.InvDate 
                    AND ci2.InvNumber = ci.InvNumber)

    ) AS Year_2016_Tax_Credit_Totals
FROM client_main cm

The idea is to replace the;

DECLARE @Year smallint = 2017
DECLARE @Month tinyint = 1

in the query with the values from the month list. I just can't wrap my head around how to do it without doing something crazy like a loop or pointers or a procedure with scalar-valued functions all over the place.

How do I do this?

Edit:

Here is a sort of example of what I'm trying to do.

Base Data;

Clientname|Invoice Revision|Invoice Date|Invoice Amount
----------+----------------+------------+--------------
Client #1 |               0|    2/1/2017|            20
Client #1 |               0|    3/1/2017|            20
Client #1 |               1|    2/1/2017|            20
Client #1 |               1|    2/1/2017|            20
Client #1 |               1|    2/1/2017|            20
Client #2 |               0|    2/1/2017|            20
Client #2 |               0|    2/1/2017|            20
Client #1 |               0|    2/1/2016|            20
Client #1 |               0|    2/1/2016|            20
Client #1 |               0|    2/1/2016|            20

Final Result;

Clientname|Date      |This_Years_Totals|Last_Years_Totals
----------+----------+-----------------+-----------------
Client #1 |  Jan 2017|                0|                0
Client #1 |  Feb 2017|               60|               60
Client #1 |  Mar 2017|               80|                0
...
Client #2 |  Jan 2017|                0|                0
Client #2 |  Feb 2017|               40|                0
Client #2 |  Mar 2017|               40|                0
...

Answer 1

You want one row per client and month. So first cross join the two to get all combinations. Then outer join your figures.

I'm using one WITH clause for the invoices you are interested in (matching the maximum revision) and one for a recursive query to generate the months.

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;

Rextester demo: http://rextester.com/NUOM2966