生成两个日期之间的月？(Generating months between two dates?)

我想填补缺失的几个月在SELECT语句，这样我就用一个包含所有月份的另一个表加入我的表计划。我怎样才能生成个月的中的表，重量轻时尚？例如，

CREATE TABLE #TEMP(Timewhen DATETIME, Value INT)

INSERT INTO #TEMP VALUES('2012-02-04', 4)
INSERT INTO #TEMP VALUES('2012-02-06', 4)
INSERT INTO #TEMP VALUES('2012-02-10', 4)
INSERT INTO #TEMP VALUES('2012-04-08', 4)
INSERT INTO #TEMP VALUES('2012-04-12', 4)

SELECT YEAR(Timewhen) EventYear, MONTH(Timewhen) EventMonth, SUM(Value) Total
FROM #TEMP
GROUP BY YEAR(Timewhen), MONTH(Timewhen)

DROP TABLE #TEMP

给我：

EventYear   EventMonth  Total
2012               2    12
2012               4    8

但是我需要：

EventYear   EventMonth  Total
2012        2           12
2012        3           0
2012        4           8

Answer 1:

DECLARE @months     INT, 
        @firstmonth DATE;

SELECT 
    @months     = DATEDIFF(MONTH, MIN(Timewhen), MAX(Timewhen)) + 1, 
    @firstmonth = DATEADD(DAY, 1-DAY(MIN(Timewhen)), MIN(Timewhen))
FROM #temp;

;WITH m(rn) AS 
(
  SELECT TOP (@months) rn = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.objects ORDER BY rn
),
x(d) AS 
(
  SELECT DATEADD(MONTH, rn-1, @firstmonth) FROM m
)
SELECT YEAR(x.d), MONTH(x.d), Total = SUM(COALESCE(t.Value, 0))
  FROM x 
  LEFT OUTER JOIN #temp AS t
  ON t.Timewhen >= x.d AND t.Timewhen < DATEADD(MONTH, 1, x.d)
  GROUP BY YEAR(x.d), MONTH(x.d);

或略少详细的版本：

DECLARE @months     INT, 
        @firstmonth DATE;

SELECT 
    @months     = DATEDIFF(MONTH, MIN(Timewhen), MAX(Timewhen)) + 1, 
    @firstmonth = DATEADD(DAY, 1-DAY(MIN(Timewhen)), MIN(Timewhen))
FROM #temp;

;WITH x(y, m, s, e) AS 
(
  SELECT YEAR(dt), MONTH(dt), dt, DATEADD(MONTH, 1, dt) FROM 
  ( SELECT dt = DATEADD(MONTH, rn-1, @firstmonth) FROM 
    ( SELECT TOP (@months) rn = ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.objects ORDER BY rn
    ) AS z
  ) AS y
)
SELECT EventYear = x.y, EventMonth = x.m, Total = SUM(COALESCE(t.Value, 0))
  FROM x LEFT OUTER JOIN #temp AS t
  ON t.Timewhen >= x.s AND t.Timewhen < x.e
  GROUP BY x.y, x.m;

使用日历表替代的解决方案：

创建使用从指令的日历表在这里。

-- Script to create a calendar table

DROP TABLE dbo.Numbers
DROP TABLE dbo.Calendar
GO

-- Use this to determine the number in the next query
DECLARE @NUMDAYS int
SELECT @NUMDAYS = DATEDIFF(DAY, '20000101', '20291231')

CREATE TABLE dbo.Numbers 
( 
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @NUMDAYS
BEGIN 
    INSERT dbo.Numbers DEFAULT VALUES 
END
GO

CREATE TABLE dbo.Calendar  
(  
    dt SMALLDATETIME NOT NULL 
        PRIMARY KEY CLUSTERED,  

    isWeekday BIT, 
    isHoliday BIT,  
    Y SMALLINT,  
    FY SMALLINT,  
    Q TINYINT,  
    M TINYINT,  
    D TINYINT,  
    DW TINYINT, 
    monthname VARCHAR(9), 
    dayname VARCHAR(9), 
    W TINYINT ,
    HolidayDescription VARCHAR(32)
) 
GO



INSERT Calendar(dt) 
SELECT DATEADD(DAY, Number, '20000101') 
FROM dbo.Numbers 
--WHERE Number <= @NUMDAYS 
ORDER BY Number

GO

--SELECT * FROM Calendar

UPDATE dbo.Calendar SET 

    isWeekday = CASE  
        WHEN DATEPART(DW, dt) IN (1,7)  
        THEN 0 
        ELSE 1 END, 

    isHoliday = 0, 

    Y = YEAR(dt),  

    FY = YEAR(dt), 

    /* 
    -- if our fiscal year 
    -- starts on May 1st: 

    FY = CASE  
        WHEN MONTH(dt) < 5 
        THEN YEAR(dt)-1  
        ELSE YEAR(dt) END, 
    */ 

    Q = CASE 
        WHEN MONTH(dt) <= 3 THEN 1 
        WHEN MONTH(dt) <= 6 THEN 2 
        WHEN MONTH(dt) <= 9 THEN 3 
        ELSE 4 END,  

    M = MONTH(dt),  

    D = DAY(dt),  

    DW = DATEPART(DW, dt),  

    monthname = DATENAME(MONTH, dt),  

    dayname = DATENAME(DW, dt),  

    W = DATEPART(WK, dt)

GO

创建后Calendar表，可以使用以下方法来实现这一目标：

CREATE TABLE #TEMP(Timewhen DATETIME, Value INT)

INSERT INTO #TEMP VALUES('2012-02-04', 4)
INSERT INTO #TEMP VALUES('2012-02-06', 4)
INSERT INTO #TEMP VALUES('2012-02-10', 4)
INSERT INTO #TEMP VALUES('2012-04-08', 4)
INSERT INTO #TEMP VALUES('2012-04-12', 4)

SELECT Y EventYear, M EventMonth, SUM(Value) Total
FROM #TEMP RIGHT OUTER JOIN (SELECT DISTINCT Y,M FROM dbo.dateRange('20120204', '20120412')) X
ON YEAR(Timewhen) = X.Y AND MONTH(Timewhen) = X.M
GROUP BY Y,M

DROP TABLE #TEMP

Answer 2:

使用SQL Server，我会做这样的事情：

select dateadd(month, num, firstdate) as mon
from (select row_number() over (partition by NULL order by (select NULL)) as num
      from Information_Schema.Columns c
     ) n cross join
     (select cast('2001-01-01' as date) firstdate) const

这将创建从第一个日期一堆个月。我只是用列表，让我产生一个序列。

Answer 3:

你可以声明对端点构筑起sys.messages表中的年/月列表：

DECLARE @EventStart datetime='1/1/2001';
DECLARE @EventEnd datetime='12/31/2012';

SELECT TOP (DATEDIFF(MONTH,@EventStart,@EventEnd)+1)
EventYear=DATEPART(YEAR,DATEADD(MONTH,ROW_NUMBER()OVER(ORDER BY message_id)-1,@EventStart))
, EventMonth=DATEPART(MONTH,DATEADD(MONTH,ROW_NUMBER()OVER(ORDER BY message_id)-1,@EventStart))
FROM sys.messages m;

结果：

EventYear   EventMonth
----------- -----------
2001        1
2001        2
2001        3
2001        4
2001        5
.
.
.
2012        7
2012        8
2012        9
2012        10
2012        11
2012        12

Answer 4:

SELECT MIN (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) F_DATE,
         MAX (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) T_DATE,
         TO_CHAR (Actual_Date, 'MM-RRRR') MONTH
    FROM (    SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr')) + LEVEL - 1
                        Actual_Date
                FROM (SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'), 'MM') - 1
                                AS dt
                        FROM DUAL)
          CONNECT BY LEVEL <=
                        (  TO_DATE (:P_TDATE, 'dd-mm-rrrr')
                         - TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'))
                         + 1))
GROUP BY TO_CHAR (Actual_Date, 'MM-RRRR')
ORDER BY 1;

Answer 5:

---如果你正在使用SSRS报告或创建性能仪表盘点，有问题创造

---月份下拉当前或前一个日历年，这里是你的T-SQL脚本。

DROP TABLE #Numbers
DROP TABLE #Calendar
-------------------------------------------------------------------------------
-- Use this to determine the number in the next query
DECLARE @NUMDAYS int
DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE  @year VARCHAR(50)
declare @year_num AS VARCHAR(50)
SET @year_num = 'current' ----'previous'
-------------------------------------------------------------------------------    
  SET @start_date = CASE @year_num
    WHEN 'current' THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
    WHEN 'previous' THEN DATEADD(yy, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
    ELSE DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
  END
  SET @end_date = CASE @year_num
    WHEN 'current' THEN GETDATE() ----DATEADD(yy, DATEDIFF(yy, -1, GETDATE()), -1)
    WHEN 'previous' THEN DATEADD(YY, -1, DATEADD(yy, DATEDIFF(yy, -1, GETDATE()), -1))
    ELSE DATEADD(yy, DATEDIFF(yy, -1, GETDATE()), -1)
  END

  SET @year = CASE @year_num
    WHEN 'current' THEN YEAR(GETDATE())
    WHEN 'previous' THEN YEAR(DATEADD(yy, -1, GETDATE()))
    ELSE YEAR(GETDATE())
  END

SELECT @NUMDAYS = DATEDIFF(DAY, @start_date, @end_date )

CREATE TABLE #Numbers 
( 
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @NUMDAYS
BEGIN 
  INSERT #Numbers DEFAULT VALUES 
END

CREATE TABLE #Calendar  
(  
dt SMALLDATETIME NOT NULL 
    PRIMARY KEY CLUSTERED,  

Y SMALLINT,  
Q TINYINT,  
M TINYINT,  
monthname VARCHAR(9)
) 

INSERT #Calendar(dt) 
SELECT DATEADD(DAY, Number,  @start_date) 
FROM #Numbers 
WHERE Number <= @NUMDAYS 
ORDER BY Number

UPDATE #Calendar 
SET Y = YEAR(dt),  
    Q = CASE WHEN MONTH(dt) <= 3 THEN 1 
             WHEN MONTH(dt) <= 6 THEN 2 
             WHEN MONTH(dt) <= 9 THEN 3 
             ELSE 4 END,  
    M = MONTH(dt),  
    monthname = convert(varchar(3),datename(month,dt))----DATENAME(MONTH, dt) 

select distinct Y,M,monthname 
from #Calendar
WHERE Y = @year
order by  M

文章来源: Generating months between two dates?