今天做商城訂單某時間段的銷售趨勢圖，訂單中在該日期範圍內可能沒有改天的記錄，可以使用一個日曆表來做關聯實現。
CREATE TABLE [dbo].[time_dimension] (
[time_id] [int] IDENTITY (1, 1) NOT Null ,
[the_date] [datetime] Null ,
[the_day] [Nvarchar] (15) Null ,
[the_month] [Nvarchar] (15) Null ,
[the_year] [Smallint] Null ,
[day_of_month] [Smallint] Null ,
[week_of_year] [Smallint] Null ,
[month_of_year] [Smallint] Null ,
[quarter] [Nvarchar] (2) Null ,
[fiscal_period] [Nvarchar] (20) Null
) ON [PRIMARY]
DECLARE @WeekString Varchar(12),
@dDate SMALLDATETIME,
@sMonth Varchar(20),
@iYear Smallint,
@iDayOfMonth Smallint,
@iWeekOfYear Smallint,
@iMonthOfYear Smallint,
@sQuarter Varchar(2),
@sSQL Varchar(100),
@adddays int
SELECT @adddays = 1 --日期增量（可以自由設定）
SELECT @dDate = '01/01/2002 ' --開始日期
WHILE @dDate < '12/31/2004 ' --結束日期
BEGIN
SELECT @WeekString = DATENAME (dw, @dDate)
SELECT @sMonth=DATENAME(mm,@dDate)
SELECT @iYear= DATENAME (yy, @dDate)
SELECT @iDayOfMonth=DATENAME (dd, @dDate)
SELECT @iWeekOfYear= DATENAME (week, @dDate)
SELECT @iMonthOfYear=DATEPART(month, @dDate)
SELECT @sQuarter = 'Q ' + CAST(DATENAME (quarter, @dDate)as Varchar(1))
INSERT INTO time_dimension(the_date, the_day, the_month, the_year,
day_of_month,
week_of_year, month_of_year, quarter) VALUES
(@dDate, @WeekString, @sMonth, @iYear, @iDayOfMonth, @iWeekOfYear,
@iMonthOfYear, @sQuarter)
SELECT @dDate = @dDate + @adddays
END
GO
select * from time_dimension
將查詢結果與該表某範圍的資料做一次連接即可。
來源:http://www.cnblogs.com/dooom/archive/2010/12/22/1914067.html
全站熱搜
留言列表
