グリッドでカレンダーの配置が必要になり、いろいろ応用が効いて重宝してます。
[ 実行と結果 ]
[ コード ]
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar`(
IN `ym` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '単月カレンダー'
BEGIN
DECLARE ymprev INT;
DECLARE ymnext INT;
SET ymprev = ym - 1;
SET ymnext = ym + 1;
IF (ymprev % 100 = 0) THEN
SET ymprev = ymprev - 88;
END IF;
IF (ymnext % 100 = 13) THEN
SET ymnext = ymnext + 88;
END IF;
SELECT
YWK AS id,
YWK,
MON,
TUE, WED, THU, FRI, SAT,
SUN,
DTMIN, DTMAX,
YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
FROM
(
SELECT
YWK,
MIN(YM) AS YMMIN,
MAX(YM) AS YMMAX,
MAX(SUN) AS SUN,
MAX(MON) AS MON,
MAX(TUE) AS TUE,
MAX(WED) AS WED,
MAX(THU) AS THU,
MAX(FRI) AS FRI,
MAX(SAT) AS SAT,
MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
FROM
(
SELECT
dt,
CASE WHEN (WEEKOFYEAR(dt) = 53) THEN
(YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)
END AS YWK,
YEAR(dt) * 100 + MONTH(dt) AS YM,
CASE WHEN (DAYOFWEEK(dt) = 1) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS SUN,
CASE WHEN (DAYOFWEEK(dt) = 2) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS MON,
CASE WHEN (DAYOFWEEK(dt) = 3) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS TUE,
CASE WHEN (DAYOFWEEK(dt) = 4) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS WED,
CASE WHEN (DAYOFWEEK(dt) = 5) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS THU,
CASE WHEN (DAYOFWEEK(dt) = 6) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS FRI,
CASE WHEN (DAYOFWEEK(dt) = 7) THEN
DATE_FORMAT(dt, '%e')
ELSE '' END AS SAT
FROM
m_date
WHERE
YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
) a
GROUP BY
YWK
) b
WHERE
FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR
FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
ORDER BY
DTMIN;
END
CREATE TABLE `m_date` (
`dt` DATE NOT NULL COMMENT '日付',
`IS_HOLIDAY` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '祝日フラグ',
PRIMARY KEY (`dt`)
)
COMMENT='連続日付マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
|
|
