T-SQL 将增量日期包装在查询周围

Question

我需要从我们的数据库中获取一组数据。此集需要在日期范围内每月有一个条目，即使该值为 null 也是如此。总数是截至年初的所有先前总数的总和。我设法为所有客户制作了一个可以使用一个月的产品，但我需要它一年。

我找到了这篇文章->在 TSQL 中生成递增日期的结果集

并将其修改为：

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    

现在我正在尝试将其包装在我的查询中。

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

这个想法是取代;

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

在包含"月份"列表中的值的查询中。我只是无法在不做一些疯狂的事情(例如循环或指针或到处具有标量值函数的过程(的情况下完成此操作。

我该怎么做？

编辑：

这是我正在尝试做的一个例子。

基础数据;

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

最终结果;

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

您希望每个客户端和每月一行。因此，首先交叉连接两者以获得所有组合。然后外部加入您的图形。

我为您感兴趣的发票使用一个WITH子句(匹配最大修订版(，另一个用于递归查询以生成月份。

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 演示：http://rextester.com/NUOM2966