如何根据 T-SQL 中前几个月的数据确定缺失月份的值

我无法通过手机访问 BOL，所以这是一个粗略的指南......

首先，您需要为没有数据的月份生成缺失的行。您可以将 OUTER 联接到具有所需时间跨度的固定表或临时表，也可以从以编程方式创建的数据集（存储过程等）中使用 OUTER 联接

其次，您应该查看新的 SQL 2008 “分析”函数，例如 MAX（value） OVER （ partition clause ） 以获取以前的值。

（我知道 Oracle 可以做到这一点，因为我需要它来计算交易日期之间的复利计算 - 真的是同样的问题）

希望这为您指明了正确的方向......

（避免将其放入临时表中并在其上光标。太粗糙了!!）

这是我想出的

declare @Transactions table (TransactionDate datetime, TransactionValue int)

declare @MinDate datetime
declare @MaxDate datetime
declare @iDate datetime
declare @Month int
declare @count int
declare @i int
declare @PrevLvl int

insert into @Transactions (TransactionDate, TransactionValue)
select '1/1/09',1

insert into @Transactions (TransactionDate, TransactionValue)
select '3/1/09',2

insert into @Transactions (TransactionDate, TransactionValue)
select '5/1/09',3


select @MinDate = min(TransactionDate) from @Transactions
select @MaxDate = max(TransactionDate) from @Transactions

set @count=datediff(mm,@MinDate,@MaxDate)
set @i=1
set @iDate=@MinDate


while (@i<=@count)
begin

    set @iDate=dateadd(mm,1,@iDate)

    if (select count(*) from @Transactions where TransactionDate=@iDate) < 1
    begin

        select @PrevLvl = TransactionValue from @Transactions where TransactionDate=dateadd(mm,-1,@iDate)

        insert into @Transactions (TransactionDate, TransactionValue)
        select @iDate, @prevLvl

    end


    set @i=@i+1
end

select *
from @Transactions
order by TransactionDate

要以基于集合的方式执行此操作，您需要为所有数据或信息设置集合。在这种情况下，有一个被忽视的数据，即“有几月份？在数据库中将“日历”表和“数字”表作为实用程序表非常有用。

以下是使用这些方法之一的解决方案。第一部分代码设置日历表。您可以使用光标或手动或其他方式填充它，并且可以将其限制为您的业务所需的任何日期范围（回到 1900-01-01 或只是回到 1970-01-01 以及您想要的未来）。您还可以添加对您的业务有用的任何其他列。

CREATE TABLE dbo.Calendar
(
     date           DATETIME     NOT NULL,
     is_holiday     BIT          NOT NULL,
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (date)
)

INSERT INTO dbo.Calendar (date, is_holiday) VALUES ('2009-01-01', 1)  -- New Year
INSERT INTO dbo.Calendar (date, is_holiday) VALUES ('2009-01-02', 1)
...

现在，使用这个表，你的问题变得微不足道：

SELECT
     CAST(MONTH(date) AS VARCHAR) + '/' + CAST(YEAR(date) AS VARCHAR) AS [Month],
     T1.TransactionValue AS [Value]
FROM
     dbo.Calendar C
LEFT OUTER JOIN dbo.Transactions T1 ON
     T1.TransactionDate <= C.date
LEFT OUTER JOIN dbo.Transactions T2 ON
     T2.TransactionDate > T1.TransactionDate AND
     T2.TransactionDate <= C.date
WHERE
     DAY(C.date) = 1 AND
     T2.TransactionDate IS NULL AND
     C.date BETWEEN '2009-01-01' AND '2009-12-31'  -- You can use whatever range you want

我首先构建一个数字表，其中包含从 1 到 100 万左右的连续整数。一旦你掌握了窍门，它们就会派上用场。

例如，以下是获取 2008 年每个月的 1 号的方法：

select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
from Numbers
where n <= 12;

现在，您可以使用 OUTER APPLY 将其放在一起，以查找每个日期的最新交易，如下所示：

with Dates as (
    select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
    from Numbers
    where n <= 12
)
select d.firstOfMonth, t.TransactionValue
from Dates d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t;

这应该能给你提供你要找的东西，但你可能不得不在谷歌上搜索一下，以找到创建数字表的最佳方法。

约翰·吉布（John Gibb）发布了一个很好的答案，已经接受了，但我想稍微扩展一下：

• 取消一年的限制，
• 在更多 明确的方式，以及
• 无需单独 数字表。

这种细微的变化使用递归公用表表达式来建立一组 Dates，该集表示 DateRange 中定义的日期和日期之后的每个月的第一天。请注意，使用 MAXRECURSION 选项来防止堆栈溢出 （！）;根据需要进行调整，以适应预期的最大月数。此外，请考虑添加备用日期程序集逻辑，以支持周、季度甚至日常。

with 
DateRange(FromDate, ToDate) as (
  select 
    Cast('11/1/2008' as DateTime), 
    Cast('2/15/2010' as DateTime)
),
Dates(Date) as (
  select 
    Case Day(FromDate) 
      When 1 Then FromDate
      Else DateAdd(month, 1, DateAdd(month, ((Year(FromDate)-1900)*12)+Month(FromDate)-1, 0))
    End
  from DateRange
  union all
  select DateAdd(month, 1, Date)
  from Dates
  where Date < (select ToDate from DateRange)
)
select 
  d.Date, t.TransactionValue
from Dates d
outer apply (
  select top 1 TransactionValue
  from Transactions
  where TransactionDate <= d.Date
  order by TransactionDate desc
) t
option (maxrecursion 120);

如果您经常进行这种类型的分析，您可能会对我为此目的而组合的 SQL Server 函数感兴趣：

if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go

create function fn_daterange
   (
   @MinDate as datetime,
   @MaxDate as datetime,
   @intval  as datetime
   )
returns table
--**************************************************************************
-- Procedure: fn_daterange()
--    Author: Ron Savage
--      Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date        Init. Description
-- 12/16/2008  RS    Created.
-- **************************************************************************
as
return
   WITH times (startdate, enddate, intervl) AS
      (
      SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
         UNION ALL
      SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
      FROM times
      WHERE startdate + intervl <= @MaxDate
      )
   select startdate, enddate from times;

go

这是对这个问题的回答，其中也有一些示例输出。

-----替代方式------

select 
    d.firstOfMonth,
    MONTH(d.firstOfMonth) as Mon,
    YEAR(d.firstOfMonth) as Yr, 
    t.TransactionValue
from (
    select 
        dateadd( month, inMonths - 1, '1/1/2009') as firstOfMonth 
        from (
            values (1), (2), (3), (4), (5), (7), (8), (9), (10), (11), (12)
        ) Dates(inMonths)
) d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t