日期维度表：
SQL计算上月最后一天、上季度最后一天、上年最后一天、到季初天数、到年初天数、当前日期是季度中第几个月、当前日期是季度中第几天等
CREATE PROCEDURE sp_sz_tjrq(
@startdate??????? char(8),??????? -- 开始日期
@years??????????? integer???????? -- 生成几年的数据
)
as
begin
? -- 定义局部变量
? declare?? @dt_begintime??????? datetime?????????? -- 开始时间
? declare?? @dt_endtime????????? datetime?????????? -- 结束时间
? declare?? @i_loopcount???????? integer??????????? -- 循环次数
? declare?? @c_sxrq????????????? char(8)??????????? -- 系统上线日期
? declare?? @c_enddate?????????? char(8)??????????? -- 结束日期
? declare?? @i_count???????????? integer??????????? -- 循环变量
? declare?? @c_tjrq????????????? char(8)??????????? -- 统计日期
? declare?? @c_year????????????? char(4)??????????? -- 日期年
? declare?? @c_month???????????? char(2)??????????? -- 日期月
? declare?? @c_day?????????????? char(2)??????????? -- 日期日
? declare?? @c_daysofmonth?????? char(2)??????????? -- 统计日期
? declare?? @c_daysofseason????? char(3)??????????? -- 月中天数
? declare?? @c_daysofyear??????? char(3)??????????? -- 年中天数
? declare?? @c_monthsofseason??? char(2)??????????? -- 季中月数
? declare?? @c_seasonsofyear???? char(2)??????????? -- 年中季数
? declare?? @c_yesterday???????? char(8)??????????? -- 上日日期
? declare?? @c_pre_month_lastday char(8)??????????? -- 上月月末日期
? declare?? @c_pre_season_lastday char(8)?????????? -- 上季季末日期
? declare?? @c_pre_year_lastday?? char(8)?????????? -- 上年年末日期
? declare?? @c_year_start_day???? char(8)?????????? -- 年度起始日期
? declare?? @c_year_on_year?????? char(8)?????????? -- 同比日期
? -- 初始化变量
? select @c_enddate = convert(char(8),dateadd(day,-1,dateadd(year,isnull(@years,5),@startdate)),112)
? select @i_loopcount = datediff(day,@startdate,@c_enddate)
? select @i_count = 0
?
? if object_id('crmo_sz_rqdz') is null
??? begin
????? create table crmo_sz_rqdz(
????? tjrq????????????? char(8) not null primary key,
????? year????????????? char(4) null,
????? month????????????? char(2) null,
????? day??????????????? char(2) null,
????? daysofmonth??????? char(2) null,
????? daysofseason?????? char(3) null,
????? daysofyear???????? char(3) null,
????? monthsofseason???? char(2) null,
????? seasonsofyear????? char(2) null,
????? yesterday????????? char(8) null,
????? pre_month_lastday? char(8) null,
????? pre_season_lastday char(8) null,
????? pre_year_lastday?? char(8) null,
????? year_start_day???? char(8) null,
????? year_on_year?????? char(8) null)
??? end
? else
??? begin
????? truncate table crmo_sz_rqdz????
??? end
? while @i_count <= @i_loopcount
??? begin
??? ?-- 获取统计日期值
??? ?select @c_tjrq = convert(char(8),dateadd(day,@i_count,@startdate),112)
??? ?-- 截取统计日期年份
??? ?select @c_year = convert(char,year(@c_tjrq))
??? ?-- 截取统计日期月份
??? ?select @c_month = convert(char,month(@c_tjrq))
??? ?-- 截取统计日期天
??? ?select @c_day = convert(char,day(@c_tjrq))
??? ?-- 计算统计日期是本月第几天
??? ?select @c_daysofmonth = convert(char,day(@c_tjrq))
??? ?-- 计算统计日期是本季度第几天
??? ?if substring(@c_tjrq,5,2) >=? '01' and substring(@c_tjrq,5,2) <=? '03'
????? select @c_daysofseason = datediff(day,convert(datetime,left(@c_tjrq,4)+'0101'),convert(datetime,@c_tjrq))+1
????? if substring(@c_tjrq,5,2) >=? '04' and substring(@c_tjrq,5,2) <=? '06'
????? select @c_daysofseason = datediff(day,convert(datetime,left(@c_tjrq,4)+'0401'),convert(datetime,@c_tjrq))+1
????? if substring(@c_tjrq,5,2) >=? '07' and substring(@c_tjrq,5,2) <=? '09'
????? select @c_daysofseason = datediff(day,convert(datetime,left(@c_tjrq,4)+'0701'),convert(datetime,@c_tjrq))+1
????? if substring(@c_tjrq,5,2) >=? '10' and substring(@c_tjrq,5,2) <=? '12'
????? select @c_daysofseason = datediff(day,convert(datetime,left(@c_tjrq,4)+'1001'),convert(datetime,@c_tjrq))+1
????? -- 计算统计日期是本年第几天
????? select @c_daysofyear = convert(char(3),datepart(dy,@c_tjrq))
????? -- 计算统计日期所在月是本季度第几个月 = 月份除以3取余 如果=0 则 是第三个月 如果不是0余数即为季度中的月数
????? select @c_monthsofseason = case mod(month(@c_tjrq),3) when 0 then 3 else mod(month(@c_tjrq),3) end
????? -- 计算统计日期所在季度是本年第几个季度
????? select @c_seasonsofyear = convert(char,datepart(qq,@c_tjrq))
????? -- 计算昨天日期
????? select @c_yesterday = convert(char(8),dateadd(day,-1,@c_tjrq),112)
????? -- 计算上月最后一天日期
????? select @c_pre_month_lastday = convert(char(8),dateadd(day,-1,substring(@c_tjrq,1,6)+'01'),112)
????? -- 计算上季度最后一天 = substring（dateadd(qq,-1,统计日期季度)，1,4）+‘季度最后一天’
????? select @c_pre_season_lastday = convert(char(8),dateadd(qq,-1,@c_tjrq),112)
????? if datepart(qq,@c_pre_season_lastday) = '1'
????? select @c_pre_season_lastday = substring(@c_tjrq,1,4)+'0331'
????? if datepart(qq,@c_pre_season_lastday) = '2'
????? select @c_pre_season_lastday = substring(@c_tjrq,1,4)+'0630'
????? if datepart(qq,@c_pre_season_lastday) = '3'
????? select @c_pre_season_lastday = substring(@c_tjrq,1,4)+'0930'
????? if datepart(qq,@c_pre_season_lastday) = '4'
????? select @c_pre_season_lastday = substring(@c_tjrq,1,4)+'1231'
????? -- 计算上一年最后一天
????? select @c_pre_year_lastday = convert(char(4),(year(@c_tjrq)-1)) + '1231'
????? -- 计算年度起始日期 统计日期向前推364天 如果小于系统上线日期那么 起始日期= 系统上线日期
????? select @c_year_start_day = convert(char(8),dateadd(day,-364,@c_tjrq),112)????????????????????????????????
????? -- 同比日期 = 上一年本月的最后一天的日期
????? select @c_year_on_year = convert(char(8),dateadd(day,-1,(substring(convert(char(8),dateadd(month,-11,@c_tjrq),112),1,6)+'01')),112)
????? -- 将计算结果插入日期对照表中
????? insert into crmo_sz_rqdz(tjrq,year,month,day,daysofmonth,daysofseason,daysofyear,monthsofseason,seasonsofyear,yesterday,pre_month_lastday,pre_season_lastday,pre_year_lastday,year_start_day,year_on_year)
????? values(@c_tjrq,@c_year,@c_month,@c_day ,@c_daysofmonth,@c_daysofseason ,@c_daysofyear,@c_monthsofseason,@c_seasonsofyear,@c_yesterday ,@c_pre_month_lastday,@c_pre_season_lastday ,@c_pre_year_lastday,@c_year_start_day,@c_year_on_year)
????? select @i_count = @i_count + 1?????
??? end
end
GO