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
...

replace them--with a join or cross apply or... what's the sample data and expected result? You've asked an [X, Y question](http://xyproblem.info/). State the actual problem at it's core--there's likely a simpler way to get your intended results. — S3S, Mar 15 '18 at 21:21

score 1 · Accepted Answer · answered Mar 16 '18 at 14:11

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

It's almost what I need. I'm trying to figure out how it works right now. How would I get it to total up the previous months of the year? Like if Jan 2017 had 10 and Feb had 20, make it so Feb would show 30 instead of 20? — Kayot, Mar 16 '18 at 15:35
You are looking for running totals (Jan -> Jan+Feb -> Jan+Feb+Mar -> ...)? Then you'd use `coalesce(sum(cur.total) over (partition by c.clientcode order by m.year, m.month), 0)` instead of `coalesce(cur.total, 0)`. Same for `coalesce(prev.total, 0)` of course. (If you want this per year, then change this slightly to `coalesce(sum(cur.total) over (partition by c.clientcode, m.year order by m.month), 0)`.) — Thorsten Kettner, Mar 16 '18 at 16:16

T-SQL Wrapping an increment date around a query

1 Answers1