如何将两个日期之间的所有年份、月份显示为整数？

Question

在 SQL Server 2000 中，我需要在客户订单的临时表中以整数形式列出年、月。

如果该月份不存在，我仍然需要列出年、月、$0.00。

在 SQL Server 2000 中执行此操作的最佳方法是什么？

01/01/2010 到 07/01/2010 将产生：

__Year__, __Month__ , __Billed__
2010,1,$5000.00
2010,2,$6000.00
2010,3,$8000.00
2010,4,$0.00
2010,5,$4000.00
2010,6,$4500.00

使用此代码：

select grp.* from 
(
    select year(orderdate) as yr, 
           month(orderdate) as mn, 
           sum(billed) 
    from Orders 
    group by year(orderdate), month(orderdate), billed 
) grp
order grp.yr, grp.mn

是否有一个简单的解决方案可以不跳过没有账单的月份并添加 0.00 美元？

Answer 1

对于 2005+（抱歉我错过了 2000 的要求）：

DECLARE @Orders TABLE(OrderDate DATETIME, billed INT);

INSERT @Orders SELECT '20100104', 500
         UNION SELECT '20100106', 700;

DECLARE 
  @year INT, 
  @end_month TINYINT;

SELECT 
  @year = 2010, 
  @end_month = 7;

WITH s(n) AS 
(
  SELECT TOP (@end_month) ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.objects ORDER BY [object_id]
),
m(s) AS
(
  SELECT DATEADD(MONTH, n-1, DATEADD(YEAR, @year-1900, 0)) 
    FROM s
)
SELECT 
    [Year]  = @year, 
    [Month] = MONTH(m.s),
    Billed  = COALESCE(SUM(t.billed), 0)
FROM m
  LEFT OUTER JOIN @Orders AS t
  ON t.OrderDate >= m.s
  AND t.OrderDate < DATEADD(MONTH, 1, m.s)
GROUP BY
  MONTH(m.s)
  ORDER BY [Year], [Month];

对于 2000，它只是略有不同：

CREATE TABLE #Orders(OrderDate DATETIME, billed INT);

INSERT #Orders SELECT '20100104', 500
         UNION SELECT '20100106', 700;

DECLARE 
  @year INT, 
  @end_month TINYINT;

SELECT 
  @year = 2010, 
  @end_month = 7;

SELECT 
    [year]  = @year, 
    [month] = MONTH(m.s),
    billed  = COALESCE(SUM(t.billed), 0)
FROM 
(
    SELECT s = DATEADD(MONTH, n-1, DATEADD(YEAR, @year-1900, 0)) FROM 
    (
        SELECT DISTINCT TOP 12 n = number
          FROM master..spt_values 
          WHERE number BETWEEN 1 AND @end_month
              ORDER BY number
    ) 
    AS s
) AS m
LEFT OUTER JOIN #Orders AS t
ON t.OrderDate >= m.s
AND t.OrderDate < DATEADD(MONTH, 1, m.s)
GROUP BY MONTH(m.s);

现在无可否认，我没有方便测试的 2000 实例 - 这只是即兴的。

Answer 2

制作一个包含 0 到 100,000 或其他数字的数字表。

然后是这样的：

DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE

SELECT YEAR(DATEADD(m, Numbers.N, @StartDate))
    ,MONTH(DATEADD(m, Numbers.N, @StartDate))
    ,ISNULL(OrderSummary.Billed, 0) AS Billed
FROM Numbers
LEFT JOIN (
    SELECT year(orderdate), month(orderdate), sum(billed)
    FROM Orders
    GROUP by year(orderdate), month(orderdate)
) AS OrderSummary (Yr, Mn, Billed)
ON YEAR(DATEADD(m, Numbers.N, @StartDate)) = OrderSummary.Yr
    AND MONTH(DATEADD(m, Numbers.N, @StartDate)) = OrderSummary.Mn
WHERE Numbers.N < DATEDIFF(m, @StartDate, @EndDate)

2005 年使用公用表表达式更容易一些。