SQL:按年份切片 DateRange 数据(棘手)

Question

我有一个非常棘手的问题，我现在正试图弄清楚它，

我有这个查询结果集

    SELECT * FROM Orders

    OrderID | OrderAmount | OrderDate | Expiry Date
    1        $100          2008-01-01    2009-12-31
    2        $200          2009-01-01    2010-12-31
    3        $300          2010-01-01    2011-12-31
    4        $3            2010-01-01    2010-06-31
    5        $400          2007-01-01    2009-05-31

现在，我如何按 OrderDate - ExpiryDate daterange 每年分解每个订单

我希望在我的 RDLC 报告中得到类似这样的结果

    ORDERS CONSUMED PER YEAR    

    OrderID  |  YEAR     |    Consumed Amount
     1         2008           $50
     1         2009           $50
     2         2009           $100
     2         2010           $100
     3         2010           $150
     3         2011           $150  
     4         2010           $3
     5         2007           $160
     5         2008           $160
     5         2009           $80   <---- another tricky part

计算基于术语 eq。 (2 年 300 美元的期限意味着每年 150 美元)

我如何在 MS-SQL 查询中执行此操作？

** 我知道标题好像不对^^，我就是找不到合适的标题

已编辑:添加了更多样本和解释

Answer 1

如果这是 SQL Server 2005 或更高版本，您可以使用通用表表达式递归构建月份表，并使用它来计算每年消耗的订单量。

一些注意事项:

1. 此解决方案假定时间段基于整月。方法是确定每月消耗的订单量，然后将其乘以一年中的月数。如果需要，您可以修改它以使用每天消耗的量。

2. 在最后的 select 中，我将 money 转换为精度更高的 decimal 以尝试避免舍入问题。您可能需要对此进行调整以满足您的需要，但我不确定您是否能够完全避免舍入问题。

3. OrderID 5 的结果与您的示例结果不匹配。这是因为该示例在 2009 年有 5 个月，而不是 6 个月。

---

create table #Orders
(
    OrderID int,
    OrderAmount money,
    OrderDate datetime,
    ExpiryDate datetime
)

insert into #Orders values(1, 100, '2008-01-01', '2009-12-31'),
                          (2, 200, '2009-01-01', '2010-12-31'),
                          (3, 300, '2010-01-01', '2011-12-31'),
                          (4, 3,   '2010-01-01', '2010-06-30'),
                          (5, 400, '2007-01-01', '2009-05-31')

;with cte_months
as
(
    select OrderID, OrderDate, year(OrderDate) OrderYear
    from #Orders
    union all
    select m.OrderID, dateadd(month, 1, m.OrderDate), year(dateadd(month, 1, m.OrderDate)) 
    from cte_months m
        inner join #Orders o on m.OrderID = o.OrderID
    where dateadd(month, 1, m.OrderDate) <= o.ExpiryDate
)
select m.OrderId, m.OrderYear, cast(sum(o.MonthlyAmount) as money) as ConsumedAmount
from
(
    select OrderID, cast(OrderAmount as decimal(12,6)) / (datediff(month, orderdate, ExpiryDate) + 1) as MonthlyAmount
    from #Orders
) o inner join cte_months m on o.OrderID = m.OrderID
group by m.OrderID, m.OrderYear

drop table #Orders

结果:

OrderId     OrderYear   ConsumedAmount
----------- ----------- ---------------------
1           2008        50.00
1           2009        50.00
2           2009        100.00
2           2010        100.00
3           2010        150.00
3           2011        150.00
4           2010        3.00
5           2007        165.5172
5           2008        165.5172
5           2009        68.9655