如何在 SQL Server 2000 中生成填充日期的临时表? [英] How can I generate a temporary table filled with dates in SQL Server 2000?

问题描述

我需要制作一个包含日期范围的临时表，以及一些包含占位符值 (0) 以备将来使用的列.我需要的日期是 $startDate 和 $endDate 之间每个月的第一天，其中这些变量可能相隔几年.

I need to make a temporary table that holds of range of dates, as well as a couple of columns that hold placeholder values (0) for future use. The dates I need are the first day of each month between $startDate and $endDate where these variables can be several years apart.

我原来的sql语句是这样的:

My original sql statement looked like this:

select dbo.FirstOfMonth(InsertDate) as Month, 0 as Trials, 0 as Sales
into #dates
from customer
group by dbo.FirstOfMonth(InsertDate)

FirstOfMonth"是我创建的一个用户定义函数，它几乎按照它所说的去做，返回所提供日期的月份第一天，时间正好是午夜.

"FirstOfMonth" is a user-defined function I made that pretty much does what it says, returning the first day of the month for the provided date with the time at exactly midnight.

这几乎产生了我需要的东西，直到我发现我的日期偶尔会出现间隔，因为我有几个月没有记录插入日期.由于我的结果肯定还有缺失的月份，我需要一种不同的方法.

This produced almost exactly what I needed until I discovered there were occasionally gaps in my dates where I had a few months were there were no records insert dates. Since my result must still have the missing months I need a different approach.

我已将以下声明添加到存储过程中，预计它们需要我需要的日期范围......

I have added the following declarations to the stored procedure anticipating their need for the range of the dates I need ...

declare $startDate set $startDate = select min(InsertDate) from customer
declare $endDate set $endDate = select max(InsertDate) from customer

...但我不知道从这里开始做什么.

... but I have no idea what to do from here.

我知道这个问题是类似于这个问题 但是，坦率地说，这个答案超出了我的脑海(我不经常使用 SQL，当我这样做时往往是在旧版本的 SQL Server 上)，并且有一些细微的差异让我望而却步.

I know this question is similar to this question but, quite frankly, that answer is over my head (I don't often work with SQL and when I do it tends to be on older versions of SQL Server) and there are a few minor differences that are throwing me off.

推荐答案

这将快速填充一个包含 170 年日期的表格.

This will quickly populate a table with 170 years worth of dates.

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2000',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

然后，您可以通过 LEFT 加入该表来使用它，用于您需要的日期范围.

You can then use it by LEFT joining on to that table, for the range of dates you require.

这篇关于如何在 SQL Server 2000 中生成填充日期的临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！