@@ -1,143 +1,162 @@
DELIMITER $$
USE ` stat`
DROP PROCEDURE IF EXISTS ` usp_Inidimdate`
CREATE DEFINER= ` root` ` %` ` usp_Inidimdate` IN FromDate DATETIME, IN ToDate DATETIME)
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
the_date datetime NOT NULL ,
date varchar (20 ) NOT NULL ,
the_year char (6 ) ,
the_halfyear char (6 ),
the_quarter char (6 ),
the_month char (6 ),
the_day char (4 ),
the_week char (6 ),
the_yearweek char (12 ),
key_quarter smallint ,
key_month smallint ,
key_day smallint ,
key_week smallint ,
key_yearweek smallint ,
key_weekyear smallint ,
PRIMARY KEY ( date )
);
DROP PROCEDURE IF EXISTS usp_Inidimdate;
CREATE PROCEDURE usp_Inidimdate(in FromDate datetime, in ToDate datetime)
BEGIN
DELETE FROM dim_date
WHERE the_date BETWEEN FromDate AND ToDate;
delete from dim_date
where the_date between FromDate and ToDate;
set @tempdate= FromDate;
SET @tempdate= FromDate;
while (@tempdate<= ToDate) do
insert into dim_date (the_date,date ,the_year,key_quarter,key_month,key_day,key_week,key_yearweek,key_weekyear)
values (@tempdate,date_format(@tempdate,' %Y-%m-%d'
month(@tempdate),day(@tempdate),WEEKDAY(@tempdate),
week(@tempdate),year(@tempdate));
set @tempdate= DATE_ADD(@tempdate,INTERVAL 1 DAY);
end while;
WHILE (@tempdate<= ToDate) DO
INSERT INTO dim_date (the_date,DATE ,the_year,key_quarter,key_month,key_day,key_week,key_yearweek,key_weekyear)
VALUES (@tempdate,DATE_FORMAT(@tempdate,' %Y-%m-%d'
MONTH(@tempdate),DAY(@tempdate),WEEKDAY(@tempdate),
WEEK(@tempdate),YEAR(@tempdate));
SET @tempdate= DATE_ADD(@tempdate,INTERVAL 1 DAY);
END WHILE;
update dim_date
set the_halfyear= ' 上半年'
where key_quarter in (' 1' ' 2'
UPDATE dim_date
SET the_halfyear= ' 上半年 '
WHERE key_quarter IN (' 1 ' ' 2 '
update dim_date
set the_halfyear= ' 下半年 '
where key_quarter in (' 3 ' ' 4 '
UPDATE dim_date
SET the_halfyear = ' 下半年 '
WHERE key_quarter IN ( ' 3 ' , ' 4 ' ) ;
update dim_date
set the_quarter = ' 一季度 '
where key_quarter= ' 1 '
UPDATE dim_date
SET the_quarter= ' 一季度 '
WHERE key_quarter= ' 1 '
update dim_date
set the_quarter= ' 二季度 '
where key_quarter= ' 2 '
UPDATE dim_date
SET the_quarter= ' 二季度 '
WHERE key_quarter= ' 2 '
update dim_date
set the_quarter= ' 三季度 '
where key_quarter= ' 3 '
UPDATE dim_date
SET the_quarter= ' 三季度 '
WHERE key_quarter= ' 3 '
update dim_date
set the_quarter= ' 四季度 '
where key_quarter= ' 4 '
UPDATE dim_date
SET the_quarter = ' 四季度 '
WHERE key_quarter = ' 4 '
update dim_date
set the_month = ' 一月 '
where key_month = ' 1 '
UPDATE dim_date
SET the_month= ' 一月 '
WHERE key_month= ' 1 '
update dim_date
set the_month= ' 二月 '
where key_month= ' 2 '
UPDATE dim_date
SET the_month= ' 二月 '
WHERE key_month= ' 2 '
update dim_date
set the_month= ' 三月 '
where key_month= ' 3 '
UPDATE dim_date
SET the_month= ' 三月 '
WHERE key_month= ' 3 '
update dim_date
set the_month= ' 四月 '
where key_month= ' 4 '
UPDATE dim_date
SET the_month= ' 四月 '
WHERE key_month= ' 4 '
update dim_date
set the_month= ' 五月 '
where key_month= ' 5 '
UPDATE dim_date
SET the_month= ' 五月 '
WHERE key_month= ' 5 '
update dim_date
set the_month= ' 六月 '
where key_month= ' 6 '
UPDATE dim_date
SET the_month= ' 六月 '
WHERE key_month= ' 6 '
update dim_date
set the_month= ' 七月 '
where key_month= ' 7 '
UPDATE dim_date
SET the_month= ' 七月 '
WHERE key_month= ' 7 '
update dim_date
set the_month= ' 八月 '
where key_month= ' 8 '
UPDATE dim_date
SET the_month= ' 八月 '
WHERE key_month= ' 8 '
update dim_date
set the_month= ' 九月 '
where key_month= ' 9 '
UPDATE dim_date
SET the_month= ' 九月 '
WHERE key_month= ' 9 '
update dim_date
set the_month= ' 十月 '
where key_month= ' 10 '
UPDATE dim_date
SET the_month= ' 十月 '
WHERE key_month= ' 10 '
update dim_date
set the_month= ' 十一月 '
where key_month= ' 11 '
UPDATE dim_date
SET the_month= ' 十一月 '
WHERE key_month= ' 11 '
update dim_date
set the_month= ' 十二月 '
where key_month= ' 12 '
UPDATE dim_date
SET the_month = ' 十二月 '
WHERE key_month = ' 12 '
update dim_date
set the_week = ' 星期日 '
where key_week = ' 6 '
UPDATE dim_date
SET the_week= ' 星期日 '
WHERE key_week= ' 6 '
update dim_date
set the_week= ' 星期一 '
where key_week= ' 0 '
UPDATE dim_date
SET the_week= ' 星期一 '
WHERE key_week= ' 0 '
update dim_date
set the_week= ' 星期二 '
where key_week= ' 1 '
UPDATE dim_date
SET the_week= ' 星期二 '
WHERE key_week= ' 1 '
update dim_date
set the_week= ' 星期三 '
where key_week= ' 2 '
UPDATE dim_date
SET the_week= ' 星期三 '
WHERE key_week= ' 2 '
update dim_date
set the_week= ' 星期四 '
where key_week= ' 3 '
UPDATE dim_date
SET the_week= ' 星期四 '
WHERE key_week= ' 3 '
update dim_date
set the_week= ' 星期五 '
where key_week= ' 4 '
UPDATE dim_date
SET the_week= ' 星期五 '
WHERE key_week= ' 4 '
update dim_date
set the_week= ' 星期六 '
where key_week= ' 5 '
UPDATE dim_date
SET the_week= ' 星期六'
WHERE key_week= ' 5'
update dim_date
set the_year= CONCAT(the_year,' 年'
UPDATE dim_date
SET the_year = CONCAT(the_year, ' 年 '
update dim_date
set the_day = CONCAT(cast(key_day as char ), ' 日 '
UPDATE dim_date
SET the_day= CONCAT(CAST(key_day AS CHAR ),' 日'
update dim_date
set key_weekyear= key_weekyear+ 1 , key_yearweek= 1
where weekday(date_format(date ,' %Y-12-31' <> 5
and week(date )= week(date_format(date ,' %Y-12-31'
UPDATE dim_date
SET key_weekyear = key_weekyear + 1 , key_yearweek= 1
WHERE WEEKDAY(DATE_FORMAT( DATE , ' %Y-12-31 ' <> 5
AND WEEK( DATE ) = WEEK(DATE_FORMAT( DATE , ' %Y-12-31 ' ));
update dim_date
set key_yearweek= key_yearweek + 1
where weekday(DATE_SUB( date ,INTERVAL dayofyear( date ) - 1 DAY ))<> 6
and not (key_month = 12 and key_yearweek = 1 );
UPDATE dim_date
SET key_yearweek= key_yearweek+ 1
WHERE WEEKDAY(DATE_SUB(DATE ,INTERVAL DAYOFYEAR(DATE )- 1 DAY))<> 6
AND NOT (key_month= 12 AND key_yearweek= 1 );
update dim_date
set the_yearweek= CONCAT(cast(key_weekyear as char ),' 第' as char ),' 周'
UPDATE dim_date
SET the_yearweek= CONCAT(CAST(key_weekyear AS CHAR ),' 第' AS CHAR ),' 周'
END;
END$$
DELIMITER ;
call usp_Inidimdate( ' 2015-01-01 ' , ' 2020-12-31 ' ) ;
zhuhai revised this gist