Business Intelligence et techniques avec Michel Preti

Voici une source possible pour une dimension heure : 

 

SELECT NOHEURE,
       TXHEUREZERO,
       NOMINUTE,
       TXMINUTEZERO,
       NOSECONDE,
       TXSECONDEZERO,
       CASE WHEN NOHEURE  <= '12' THEN 'AM' ELSE 'PM' END CDAMPM,
       CASE WHEN NOHEURE  <= '12' THEN 'AVANT-MIDI' ELSE 'APRÈS-MIDI' END TXAMPM,
       CASE WHEN NOHEURE BETWEEN '00' AND '06'  THEN 'NUIT'
            WHEN NOHEURE BETWEEN '07' AND '11' THEN 'AVANT-MIDI'
            WHEN NOHEURE BETWEEN '12' AND '17' THEN 'APRÈS-MIDI'
            WHEN NOHEURE BETWEEN '18' AND '23' THEN 'SOIR'             
            ELSE NULL END CD4PARTIEJOUR,
       CASE WHEN NOHEURE BETWEEN '00' AND '07' THEN 'NUIT'
            WHEN NOHEURE BETWEEN '08' AND '16' THEN 'JOUR'
            WHEN NOHEURE BETWEEN '17' AND '23' THEN 'SOIR'             
            ELSE NULL END CD3PARTIEJOUR,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '29'  THEN '00'
            WHEN TXMINUTEZERO BETWEEN '30' AND '59' THEN '30'
            ELSE NULL END CD30MINUTE,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '29'  THEN TXHEUREZERO || ':00'
            WHEN TXMINUTEZERO BETWEEN '30' AND '59' THEN TXHEUREZERO || ':30'
            ELSE NULL END TX30MINUTE,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '14'  THEN '00'
            WHEN TXMINUTEZERO BETWEEN '15' AND '29' THEN '15'
            WHEN TXMINUTEZERO BETWEEN '30' AND '44' THEN '30'
            WHEN TXMINUTEZERO BETWEEN '45' AND '59' THEN '45'
            ELSE NULL END CD15MINUTE,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '14' THEN TXHEUREZERO || ':00'
            WHEN TXMINUTEZERO BETWEEN '15' AND '29' THEN TXHEUREZERO || ':15'
            WHEN TXMINUTEZERO BETWEEN '30' AND '44' THEN TXHEUREZERO || ':30'
            WHEN TXMINUTEZERO BETWEEN '45' AND '59' THEN TXHEUREZERO || ':45'
            ELSE NULL END TX15MINUTE,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '09'  THEN '00'
            WHEN TXMINUTEZERO BETWEEN '10' AND '19'  THEN '10'
            WHEN TXMINUTEZERO BETWEEN '20' AND '29'  THEN '20'
            WHEN TXMINUTEZERO BETWEEN '30' AND '39'  THEN '30'
            WHEN TXMINUTEZERO BETWEEN '40' AND '49'  THEN '40'
            WHEN TXMINUTEZERO BETWEEN '50' AND '59'  THEN '50'
            ELSE NULL END CD10MINUTE,
       CASE WHEN TXMINUTEZERO BETWEEN '00' AND '09'  THEN TXHEUREZERO || ':00'
            WHEN TXMINUTEZERO BETWEEN '10' AND '19'  THEN TXHEUREZERO || ':10'
            WHEN TXMINUTEZERO BETWEEN '20' AND '29'  THEN TXHEUREZERO || ':20'
            WHEN TXMINUTEZERO BETWEEN '30' AND '39'  THEN TXHEUREZERO || ':30'
            WHEN TXMINUTEZERO BETWEEN '40' AND '49'  THEN TXHEUREZERO || ':40'
            WHEN TXMINUTEZERO BETWEEN '50' AND '59'  THEN TXHEUREZERO || ':50'
            ELSE NULL END TX10MINUTE
  FROM (SELECT rownum - 1 NOHEURE,
       LPAD((rownum - 1),2,'0') TXHEUREZERO  
  FROM ( SELECT 1 FROM dual CONNECT BY LEVEL <= '24')) HEURES,
(SELECT rownum - 1 NOMINUTE,
       LPAD((rownum - 1),2,'0') TXMINUTEZERO  
  FROM ( SELECT 1 FROM dual CONNECT BY LEVEL <= '60')) MINUTES,     
(SELECT rownum - 1 NOSECONDE,
       LPAD((rownum - 1),2,'0') TXSECONDEZERO  
  FROM ( SELECT 1 FROM dual CONNECT BY LEVEL <= '60')) SECONDES
ORDER BY NOHEURE, NOMINUTE, NOSECONDE