Oracle

ID #1090

Source SQL pour une dimension heures minutes secondes

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

Tags: espace oracle cha√ģne

Related entries:

DerniŤre mise ŗ jour: 2011-09-01 15:11
Auteur: Michel Preti
Rťvision: 1.0

Digg it! Imprimer cet article Envoyer ŗ un ami version PDF
Propose a translation for Propose a translation for
Merci de noter cette entrťe:

Moyenne des notes: 1 sur 5 (1 Votes)

complŤtement inutile 1 2 3 4 5 indispensable

Vous ne pouvez pas commenter cet enregistrement

Commentť par RachelleStr:
Quality posts is the secret to interest the users to pay a quick visit the site, that's what this website is providing.
Added at: 2015-01-14 04:22