un mese | SQL & MySQL

Vorrei creare una procedura che mi inserisca in una tabella tutte le date di un mese. In un campo ENUM (Y-N) mi inserisca se e' festivo o no, compresa quindi la Pasqua. Suggerimenti?

Ciao Febo,

ma che vuoi dire quando parli di giorno festivo, da quale calendario vuoi prendere i dati?

spiegati meglio

algweb

Ciao. Dopo un po di ricerche ho trovato qualcosa. Qui si calcola anche il giorno di Pasqua in quanto a me interessava il lunedì dopo la Pasqua che è appunto festivo. Ho aggiunto qualcosa ed è così:

delimiter //

DROP PROCEDURE IF EXISTS p_load_dim_date
//

CREATE PROCEDURE p_load_dim_date (
p_from_date DATE
, p_to_date DATE
)
BEGIN
DECLARE v_date DATE DEFAULT p_from_date;
DECLARE v_month tinyint;
CREATE TABLE IF NOT EXISTS dim_date (
date_key int primary key
, date_value date
, date_iso char(10)
, year smallint
, quarter tinyint
, quarter_name char(2)
, month tinyint
, month_name varchar(10)
, month_abbreviation varchar(10)
, week char(2)
, day_of_month tinyint
, day_of_year smallint
, day_of_week smallint
, day_name varchar(10)
, day_abbreviation varchar(10)
, is_weekend tinyint
, is_weekday tinyint
, is_today tinyint
, is_yesterday tinyint
, is_this_week tinyint
, is_last_week tinyint
, is_this_month tinyint
, is_last_month tinyint
, is_this_year tinyint
, is_last_year tinyint
, is_festivo ENUM('Y','N')
);
WHILE v_date < p_to_date DO
SET v_month := month(v_date);
INSERT INTO dim_date(
date_key
, date_value
, date_iso
, year
, quarter
, quarter_name
, month
, month_name
, month_abbreviation
, week
, day_of_month
, day_of_year
, day_of_week
, day_name
, day_abbreviation
, is_weekend
, is_weekday
, is_festivo
) VALUES (
v_date + 0
, v_date
, DATE_FORMAT(v_date, '%y-%c-%d')
, year(v_date)
, ((v_month - 1) DIV 3) + 1
, CONCAT('Q', ((v_month - 1) DIV 3) + 1)
, v_month
, DATE_FORMAT(v_date, '%M')
, DATE_FORMAT(v_date, '%b')
, DATE_FORMAT(v_date, '%u')
, DATE_FORMAT(v_date, '%d')
, DATE_FORMAT(v_date, '%j')
, DATE_FORMAT(v_date, '%w') + 1
, DATE_FORMAT(v_date, '%W')
, DATE_FORMAT(v_date, '%a')
, IF(DATE_FORMAT(v_date, '%w') IN (0,6), 1, 0)
, IF(DATE_FORMAT(v_date, '%w') IN (0,6), 0, 1)
, IF(DATE_FORMAT(v_date, '%W')= 'Saturday'
OR DATE_FORMAT(v_date, '%W')= 'Sunday'
OR v_date = `f_easter`( year(v_date))+ INTERVAL 1 DAY,'Y','N')
);
SET v_date := v_date + INTERVAL 1 DAY;
END WHILE;
CALL p_update_dim_date();
END;
//

DROP PROCEDURE IF EXISTS p_update_dim_date;
//

CREATE PROCEDURE p_update_dim_date()
UPDATE dim_date
SET is_today = IF(date_value = current_date, 1, 0)
, is_yesterday = IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
, is_this_week = IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
, is_last_week = IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
, is_this_month = IF(year = year(current_date) AND month = month(current_date), 1, 0)
, is_last_month = IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
, is_this_year = IF(year = year(current_date), 1, 0)
, is_last_year = IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
WHERE is_today
OR is_yesterday
OR is_this_week
OR is_last_week
OR is_this_month
OR is_last_month
OR is_this_year
OR is_last_year
OR IF(date_value = current_date, 1, 0)
OR IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
OR IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
OR IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
OR IF(year = year(current_date) AND month = month(current_date), 1, 0)
OR IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
OR IF(year = year(current_date), 1, 0)
OR IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
;
//

delimiter ;

DELIMITER //

DROP FUNCTION IF EXISTS f_easter
//
CREATE FUNCTION f_easter(
p_year YEAR
)
RETURNS VARCHAR(255)
DETERMINISTIC NO SQL
COMMENT 'Calculates easter day for a given year.'
BEGIN
--
-- code taken from
-- http://en.wikipedia.org/wiki/Computus#Anonymous_Gregorian_algorithm
--
DECLARE a SMALLINT DEFAULT p_year % 19;
DECLARE b SMALLINT DEFAULT p_year DIV 100;
DECLARE c SMALLINT DEFAULT p_year % 100;
DECLARE d SMALLINT DEFAULT b DIV 4;
DECLARE e SMALLINT DEFAULT b % 4;
DECLARE f SMALLINT DEFAULT (b + 8) DIV 25;
DECLARE g SMALLINT DEFAULT (b - f + 1) DIV 3;
DECLARE h SMALLINT DEFAULT (19*a + b - d - g + 15) % 30;
DECLARE i SMALLINT DEFAULT c DIV 4;
DECLARE k SMALLINT DEFAULT c % 4;
DECLARE L SMALLINT DEFAULT (32 + 2*e + 2*i - h - k) % 7;
DECLARE m SMALLINT DEFAULT (a + 11*h + 22*L) DIV 451;
DECLARE v100 SMALLINT DEFAULT h + L - 7*m + 114;

RETURN STR_TO_DATE(
CONCAT(
p_year
, '-'
, v100 DIV 31
, '-'
, (v100 % 31) + 1
)
, '%Y-%c-%e'
);
END;
//

delimiter ;

Ultima modifica 18 Ottobre, 2011 19:59 di Febo

Perchè non ne fai una un tip ?

Voglio aggiungere che se il campo v_festivo è un campo ENUM('Y','N') posso settarlo se il giorno è festivo o no . Ho sostituito parte del codice della procedura sopra con questo:

, IF(
#01/01
DAYOFYEAR(v_date)=1
#06/01
OR DAYOFYEAR(v_date)=6
#25/4
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,116,115)
#01/05
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,122,121)
#02/06
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,154,153)
#15/08
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,228,227)
#11/01
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,306,305)
#08/12
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,343,342)
#25/12
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,360,359)
#26/12
OR DAYOFYEAR(v_date)= IF( is_leap_year(v_date) = 1,361,360)
#Lunedì di Pasqua
OR v_date = `f_easter`( year(v_date))+ INTERVAL 1 DAY
OR DATE_FORMAT(v_date, '%W')= 'Saturday'
OR DATE_FORMAT(v_date, '%W')= 'Sunday'

,'Y','N')

questa è la funz. is_leap_year per l'anno bisestile.

CREATE DEFINER = 'root'@'localhost' FUNCTION `is_leap_year`(
TODAY DATE
)
RETURNS tinyint(1)
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE leap_year BOOLEAN;
IF ((SELECT MOD(YEAR(today),4) = 0) AND (SELECT MOD(YEAR(today),100) != 0))
THEN SET leap_year = 1;
ELSEIF (SELECT MOD(YEAR(today),400) = 0) THEN SET leap_year = 1;
ELSE SET leap_year =0;
END IF;
RETURN leap_year;
END;

la funz. tiene conto anche dell'inizio secolo. Ho fatto qualche esperimento e funziona. Cosa ve ne sembra?

Ultima modifica 21 Ottobre, 2011 06:29 di Febo