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

我首先构建一个数字表,其中包含从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;

这应该会给你你想要的,但是你可能需要搜索一下,以找到创建数字表的最佳方法。

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

约翰·吉布(John Gibb)给出了一个很好的答案,已经被接受了,但我想进一步说明:

• 在更大的范围内公开日期范围 明确的方式,以及
• 不需要单独的 数字表。

这个微小的变化使用了 recursive common table expression 建立一组日期,表示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

这就是答案 question ,它也有一些示例输出。

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

首先,您需要为没有数据的月份生成缺少的行。您可以使用固定表或临时表的外部联接(具有所需的时间跨度),也可以从编程创建的数据集(存储过程或类似的数据集)进行联接

其次,您应该查看新的SQL2008“分析”函数,如MAX(value)OVER(partition子句),以获取以前的值。

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

希望这能为你指明正确的方向。。。

(避免把它扔进临时表,并在上面大骂。太粗糙了!!!)

-----替代方法------

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