在两个日期之间生成月份?
我想在 SELECT 语句中填写缺失的月份，所以我计划将我的表与另一个包含所有月份的表连接起来.如何以轻量级方式生成月份表?例如，
I want to fill in the missing months in a SELECT statement so I was planning on joining my table with another table that contains all months. How can I generate a table of months in a light-weight fashion? For instance,
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
推荐答案
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);
或者稍微不那么冗长的版本:
Or a slightly less verbose version:
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表后，可以使用以下方法来实现:
After creating a Calendar table, one can use the following to achieve this:
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
相关文章