cti
/
eco-gitlab
12
0
Fork 0
Code Issues 0 Pull Requests 0 Projects 0 Releases 0 Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3 Commits
1 Branch
5.8 MiB
 
 
 
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
eco-gitlab/db/schema2/views/v_alias_periode_1.yaml

747 lines
39 KiB
Raw Permalink Blame History

src: |
WITH ctl_dates AS (
SELECT now.now,
to_char(now.now::timestamp with time zone, 'DD/MM/YYYY'::text) AS now_l,
(now.now - '6 days'::interval)::date AS now_7,
date_trunc('YEAR'::text, now.now::timestamp with time zone)::date AS first_day_current_year,
CASE now.now
WHEN (date_trunc('MONTH'::text, now.now::timestamp with time zone)::date + '1 mon -1 days'::interval)::date THEN now.now
ELSE (date_trunc('MONTH'::text, now.now::timestamp with time zone)::date - '1 day'::interval)::date
END AS last_day_of_last_month,
now.now AS current_day,
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN now.now::timestamp without time zone
ELSE to_date(to_char(now.now::timestamp with time zone, 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) - '7 days'::interval
END::date AS last_day_of_last_week,
to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '-1'::text
ELSE '0'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) AS last_day_of_week_0,
to_char(to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '-1'::text
ELSE '0'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS last_day_of_weekn_0,
to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '0'::text
ELSE '1'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) AS last_day_of_week_1,
to_char(to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '0'::text
ELSE '1'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS last_day_of_weekn_1,
to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '1'::text
ELSE '2'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) AS last_day_of_week_2,
to_char(to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '1'::text
ELSE '2'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS last_day_of_weekn_2,
to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '2'::text
ELSE '3'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) AS last_day_of_week_3,
to_char(to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '2'::text
ELSE '3'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS last_day_of_weekn_3,
to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '3'::text
ELSE '4'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text) AS last_day_of_week_4,
to_char(to_date(to_char(now.now - ((
CASE to_char(now.now::timestamp with time zone, 'ID'::text)
WHEN 7 THEN '3'::text
ELSE '4'::text
END || ' week'::text)::interval), 'IYYYIW'::text) || '7'::text, 'IYYYIWID'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS last_day_of_weekn_4,
CASE
WHEN to_char(now.now::timestamp with time zone, 'DD'::text) = base.cti_last_day(now.now)::text THEN now.now
WHEN to_char(now.now::timestamp with time zone, 'DD'::text) >= 20::text THEN to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '20'::text, 'YYYYMMDD'::text)
WHEN to_char(now.now::timestamp with time zone, 'DD'::text) >= 10::text THEN to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '10'::text, 'YYYYMMDD'::text)
ELSE base.cti_last_day((now.now - '1 mon'::interval)::date)
END AS decade_last,
base.cti_last_day(now.now) AS decade_0,
to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '20'::text, 'YYYYMMDD'::text) AS decade_1,
to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '10'::text, 'YYYYMMDD'::text) AS decade_2,
base.cti_last_day((now.now - '1 mon'::interval)::date) AS decade_3,
to_date(to_char(now.now - '1 mon'::interval, 'YYYYMM'::text) || '20'::text, 'YYYYMMDD'::text) AS decade_4,
to_date(to_char(now.now - '1 mon'::interval, 'YYYYMM'::text) || '10'::text, 'YYYYMMDD'::text) AS decade_5,
base.cti_last_day((now.now - '2 mons'::interval)::date) AS decade_6,
to_char(base.cti_last_day(now.now)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_0_l,
to_char(to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '20'::text, 'YYYYMMDD'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_1_l,
to_char(to_date(to_char(now.now::timestamp with time zone, 'YYYYMM'::text) || '10'::text, 'YYYYMMDD'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_2_l,
to_char(base.cti_last_day((now.now - '1 mon'::interval)::date)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_3_l,
to_char(to_date(to_char(now.now - '1 mon'::interval, 'YYYYMM'::text) || '20'::text, 'YYYYMMDD'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_4_l,
to_char(to_date(to_char(now.now - '1 mon'::interval, 'YYYYMM'::text) || '10'::text, 'YYYYMMDD'::text)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_5_l,
to_char(base.cti_last_day((now.now - '2 mons'::interval)::date)::timestamp with time zone, 'DD/MM/YYYY'::text) AS decade_6_l
FROM ( SELECT t_divers.valeur_date AS now
FROM eco.t_divers
WHERE t_divers.code = 'NOW'::text) now
)
SELECT 1000 AS tri,
1 AS lvl,
'Cumul au dernier mois'::text AS txt,
'CUM_LAST_MONTH'::text AS cod,
ctl_dates.first_day_current_year AS deb,
CASE
WHEN ctl_dates.first_day_current_year < ctl_dates.last_day_of_last_month THEN ctl_dates.last_day_of_last_month::timestamp with time zone
ELSE ctl_dates.current_day::timestamp without time zone::timestamp with time zone
END::date AS fin
FROM ctl_dates
UNION
SELECT 1001 AS tri,
2 AS lvl,
'Janvier à décembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_DEC'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1201'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1002 AS tri,
2 AS lvl,
'Janvier à novembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_NOV'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1101'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1003 AS tri,
2 AS lvl,
'Janvier à octobre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_OCT'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1001'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1004 AS tri,
2 AS lvl,
'Janvier à septembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_SEP'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0901'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1005 AS tri,
2 AS lvl,
'Janvier à août '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_AUG'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0801'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1006 AS tri,
2 AS lvl,
'Janvier à juillet '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_JUL'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1007 AS tri,
2 AS lvl,
'Janvier à juin '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_JUN'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0601'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1008 AS tri,
2 AS lvl,
'Janvier à mai '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_MAY'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0501'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1009 AS tri,
2 AS lvl,
'Janvier à avril '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_APR'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1010 AS tri,
2 AS lvl,
'Janvier à mars '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_MAR'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0301'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1011 AS tri,
2 AS lvl,
'Janvier à février '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_FEB'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0201'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 1012 AS tri,
2 AS lvl,
'Janvier à janvier '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'CURRENT_YEAR_JAN'::text AS cod,
ctl_dates.first_day_current_year AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0101'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 2000 AS tri,
1 AS lvl,
'Cumul à dernière décade'::text AS txt,
'CUM_LAST_DECADE'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_last AS fin
FROM ctl_dates
UNION
SELECT 2001 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_0_l AS txt,
'CUM_LAST_DECADE_0'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_0 AS fin
FROM ctl_dates
UNION
SELECT 2002 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_1_l AS txt,
'CUM_LAST_DECADE_1'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_1 AS fin
FROM ctl_dates
UNION
SELECT 2003 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_2_l AS txt,
'CUM_LAST_DECADE_2'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_2 AS fin
FROM ctl_dates
UNION
SELECT 2004 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_3_l AS txt,
'CUM_LAST_DECADE_3'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_3 AS fin
FROM ctl_dates
UNION
SELECT 2005 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_4_l AS txt,
'CUM_LAST_DECADE_4'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_4 AS fin
FROM ctl_dates
UNION
SELECT 2006 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_5_l AS txt,
'CUM_LAST_DECADE_5'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_5 AS fin
FROM ctl_dates
UNION
SELECT 2007 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.decade_6_l AS txt,
'CUM_LAST_DECADE_6'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.decade_6 AS fin
FROM ctl_dates
UNION
SELECT 3000 AS tri,
1 AS lvl,
'Cumul à dernière semaine'::text AS txt,
'CUM_LAST_WEEK'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_last_week AS fin
FROM ctl_dates
UNION
SELECT 3001 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.last_day_of_weekn_0 AS txt,
'CUM_LAST_MONTH_W0'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_week_0 AS fin
FROM ctl_dates
UNION
SELECT 3002 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.last_day_of_weekn_1 AS txt,
'CUM_LAST_MONTH_W1'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_week_1 AS fin
FROM ctl_dates
UNION
SELECT 3003 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.last_day_of_weekn_2 AS txt,
'CUM_LAST_MONTH_W2'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_week_2 AS fin
FROM ctl_dates
UNION
SELECT 3004 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.last_day_of_weekn_3 AS txt,
'CUM_LAST_MONTH_W3'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_week_3 AS fin
FROM ctl_dates
UNION
SELECT 3005 AS tri,
2 AS lvl,
'Cumul au '::text || ctl_dates.last_day_of_weekn_4 AS txt,
'CUM_LAST_MONTH_W4'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.last_day_of_week_4 AS fin
FROM ctl_dates
UNION
SELECT 4000 AS tri,
1 AS lvl,
'Cumul au '::text || ctl_dates.now_l AS txt,
'CUM_NOW'::text AS cod,
ctl_dates.first_day_current_year AS deb,
ctl_dates.now AS fin
FROM ctl_dates
UNION
SELECT 4500 AS tri,
1 AS lvl,
'12 derniers mois glissants'::text AS txt,
'CUM_LAST_12_MONTH'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.last_day_of_last_month - '11 mons'::interval)::date AS deb,
ctl_dates.last_day_of_last_month AS fin
FROM ctl_dates
UNION
SELECT 4501 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '11 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(ctl_dates.now::timestamp with time zone, 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_0'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '11 mons'::interval)::date AS deb,
base.cti_last_day(ctl_dates.now) AS fin
FROM ctl_dates
UNION
SELECT 4502 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 mon'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_1'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '1 mon'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4503 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 1 mon'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '2 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_2'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 1 mon'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '2 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4504 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 2 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '3 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_3'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 2 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '3 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4505 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 3 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '4 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_4'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 3 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '4 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4506 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 4 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '5 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_5'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 4 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '5 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4507 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 5 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '6 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_6'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 5 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '6 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4508 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 6 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '7 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_7'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 6 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '7 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4509 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 7 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '8 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_8'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 7 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '8 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4510 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 8 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '9 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_9'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 8 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '9 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4511 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 9 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '10 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_10'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 9 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '10 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 4512 AS tri,
2 AS lvl,
(base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '1 year 10 mons'::interval), 'Month'::text)) || ' à '::text) || base.cti_to_french(to_char(date_trunc('MONTH'::text, ctl_dates.now - '11 mons'::interval), 'Month YYYY'::text)) AS txt,
'CUM_LAST_12_MONTH_11'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.now - '1 year 10 mons'::interval)::date AS deb,
base.cti_last_day(date_trunc('MONTH'::text, ctl_dates.now - '11 mons'::interval)::date) AS fin
FROM ctl_dates
UNION
SELECT 5000 AS tri,
1 AS lvl,
'7 derniers jours'::text AS txt,
'LAST_7_DAYS'::text AS cod,
ctl_dates.now_7 AS deb,
ctl_dates.now AS fin
FROM ctl_dates
UNION
SELECT 7000 AS tri,
1 AS lvl,
'Cumul année précédente'::text AS txt,
'LAST_YEAR'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1201'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7001 AS tri,
2 AS lvl,
'Janvier à décembre '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_DEC'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1201'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7002 AS tri,
2 AS lvl,
'Janvier à novembre '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_NOV'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1101'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7003 AS tri,
2 AS lvl,
'Janvier à octobre '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_OCT'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1001'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7004 AS tri,
2 AS lvl,
'Janvier à septembre '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_SEP'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0901'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7005 AS tri,
2 AS lvl,
'Janvier à août '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_AUG'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0801'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7006 AS tri,
2 AS lvl,
'Janvier à juillet '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_JUL'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7007 AS tri,
2 AS lvl,
'Janvier à juin '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_JUN'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0601'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7008 AS tri,
2 AS lvl,
'Janvier à mai '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_MAY'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0501'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7009 AS tri,
2 AS lvl,
'Janvier à avril '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_APR'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7010 AS tri,
2 AS lvl,
'Janvier à mars '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_MAR'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0301'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7011 AS tri,
2 AS lvl,
'Janvier à février '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_FEB'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0201'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 7012 AS tri,
2 AS lvl,
'Janvier à janvier '::text || to_char(ctl_dates.first_day_current_year - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_JAN'::text AS cod,
(ctl_dates.first_day_current_year - '1 year'::interval)::date AS deb,
(base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0101'::text)::date) - '1 year'::interval)::date AS fin
FROM ctl_dates
UNION
SELECT 8000 AS tri,
1 AS lvl,
'Dernier semestre'::text AS txt,
'LAST_HALF'::text AS cod,
CASE
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 7 THEN ctl_dates.first_day_current_year
ELSE (to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0701'::text)::date
END AS deb,
CASE
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 7 THEN (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0630'::text)::date
ELSE (to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '1231'::text)::date
END AS fin
FROM ctl_dates
UNION
SELECT 8001 AS tri,
2 AS lvl,
'1ème semestre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'HALF_1'::text AS cod,
ctl_dates.first_day_current_year AS deb,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0630'::text)::date AS fin
FROM ctl_dates
WHERE to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 7
UNION
SELECT 8002 AS tri,
2 AS lvl,
'2ème semestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_HALF_2'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0701'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '1231'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 8003 AS tri,
2 AS lvl,
'1er semestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_HALF_1'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0101'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0630'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9000 AS tri,
1 AS lvl,
'Dernier trimestre'::text AS txt,
'LAST_QUARTER'::text AS cod,
CASE
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 10 THEN (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 7 THEN (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date
ELSE ctl_dates.first_day_current_year
END AS deb,
CASE
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 10 THEN (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0930'::text)::date
WHEN to_char(ctl_dates.now::timestamp with time zone, 'MM'::text)::integer >= 7 THEN (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0630'::text)::date
ELSE (to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0331'::text)::date
END AS fin
FROM ctl_dates
UNION
SELECT 9002 AS tri,
2 AS lvl,
'3ème trimestre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'QUARTER_3'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date AS deb,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0930'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9003 AS tri,
2 AS lvl,
'2ème trimestre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'QUARTER_2'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date AS deb,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0630'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9004 AS tri,
2 AS lvl,
'1er trimestre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'QUARTER_1'::text AS cod,
ctl_dates.first_day_current_year AS deb,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0331'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9011 AS tri,
2 AS lvl,
'4ème trimestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_QUARTER_4'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '1001'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '1231'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9012 AS tri,
2 AS lvl,
'3ème trimestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_QUARTER_3'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0701'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0930'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9013 AS tri,
2 AS lvl,
'2ème trimestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_QUARTER_2'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0401'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0630'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 9014 AS tri,
2 AS lvl,
'1er trimestre '::text || to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) AS txt,
'LAST_YEAR_QUARTER_1'::text AS cod,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0101'::text)::date AS deb,
(to_char(ctl_dates.now - '1 year'::interval, 'YYYY'::text) || '0331'::text)::date AS fin
FROM ctl_dates
UNION
SELECT 10000 AS tri,
1 AS lvl,
'Dernier mois'::text AS txt,
'LAST_MONTH'::text AS cod,
date_trunc('MONTH'::text, ctl_dates.last_day_of_last_month::timestamp with time zone)::date AS deb,
ctl_dates.last_day_of_last_month AS fin
FROM ctl_dates
UNION
SELECT 10001 AS tri,
2 AS lvl,
'Décembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_DEC'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1201'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1201'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10002 AS tri,
2 AS lvl,
'Novembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_NOV'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1101'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1101'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10003 AS tri,
2 AS lvl,
'Octobre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_OCT'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1001'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '1001'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10004 AS tri,
2 AS lvl,
'Septembre '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_SEP'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0901'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0901'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10005 AS tri,
2 AS lvl,
'Août '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_AUG'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0801'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0801'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10006 AS tri,
2 AS lvl,
'Juillet '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_JUL'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0701'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10007 AS tri,
2 AS lvl,
'Juin '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_JUN'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0601'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0601'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10008 AS tri,
2 AS lvl,
'Mai '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_MAY'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0501'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0501'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10009 AS tri,
2 AS lvl,
'Avril '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_APR'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0401'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10010 AS tri,
2 AS lvl,
'Mars '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_MAR'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0301'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0301'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10011 AS tri,
2 AS lvl,
'Février '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_FEB'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0201'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0201'::text)::date) AS fin
FROM ctl_dates
UNION
SELECT 10012 AS tri,
2 AS lvl,
'Janvier '::text || to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) AS txt,
'MONTH_JAN'::text AS cod,
(to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0101'::text)::date AS deb,
base.cti_last_day((to_char(ctl_dates.now::timestamp with time zone, 'YYYY'::text) || '0101'::text)::date) AS fin
FROM ctl_dates
ORDER BY 1;