MIcrosoft Server 2005 questions sur les heures

**blaise4714** · 16/03/2009, 13h14

Salut à tous,
J ai un tableau qui contient:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Nr        Debut        Fin              Prix
1         06:00        08:00           0.50 €
2         08:00        20:00           1,50 €
3         20:00        06:00           2,00 €

A partir de la, j aimerai savoir le prix d une session qui commence 19:00h et finit à 05:00h. et aussi une autre session qui commence à 15:00 et finit à 23:00
Auriez vous une idee comment je peux faire pour comparer les heures de debut et de fin? Ou bien comment dois je proceder pour avoir ces prix?
Merci d avance

**elsuket** · 16/03/2009, 14h43

Bonjour,

Quelles sont les colonnes de votre table ?
Dans quel type de données sont stockées les heures ?

@++

**blaise4714** · 16/03/2009, 15h07

salut
les colonnes sont
Nr Debut Fin Prix

comme le type time n existe pas chez MS Server 2005, j utilise nvarchar, et que je convertit en datetime pour faire les comparaisons
Merci

**SQLpro** · 16/03/2009, 15h08

Si vos données horaires sont stockées sous forme numérique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE T_TARIF_TRF
(TRF_ID      INT NOT NULL PRIMARY KEY,
 TRF_DEBUT   FLOAT,
 TRF_FIN     FLOAT,
 TRF_PRIX   DECIMAL(16,2));
 GO
 
INSERT INTO T_TARIF_TRF VALUES (1,         6.0,         8.0,           0.50);
INSERT INTO T_TARIF_TRF VALUES (2,         8.0,        20.0,           1.50);
INSERT INTO T_TARIF_TRF VALUES (3,         20.0,        6.0,           2.00);
GO

La requête suivante vous donne la décomposition des couts des différentes tranches horaires :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH 
T_T AS
(SELECT TRF_ID, TRF_DEBUT,
        CASE 
           WHEN TRF_FIN < TRF_DEBUT THEN TRF_FIN + 24.0
           ELSE TRF_FIN
        END AS TRF_FIN,
        TRF_PRIX
 FROM   T_TARIF_TRF),
T_TRF AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX, 
         TRF_FIN - TRF_DEBUT AS TRF_NB_H 
 FROM    T_T),
T_P AS
(SELECT  19.0 AS PRM_DEBUT, 5.0 AS PRM_FIN 
 -- FROM DUAL -- si vous êtes sous Oracle
),
T_PRM AS
(SELECT  PRM_DEBUT,
         CASE
            WHEN PRM_FIN < PRM_DEBUT THEN PRM_FIN + 24.0
            ELSE PRM_FIN
         END AS PRM_FIN
 FROM    T_P),
T_LIF AS
(SELECT TRF_DEBUT, TRF_FIN, TRF_PRIX,
        CASE 
           WHEN PRM_DEBUT BETWEEN TRF_DEBUT AND TRF_FIN THEN PRM_DEBUT
           ELSE TRF_DEBUT
        END AS PRM_DEBUT,
        CASE 
           WHEN PRM_FIN BETWEEN TRF_DEBUT AND TRF_FIN THEN PRM_FIN
           ELSE TRF_FIN
        END AS PRM_FIN,
        CASE
           WHEN PRM_FIN NOT BETWEEN TRF_DEBUT AND TRF_FIN THEN TRF_FIN - PRM_DEBUT
           ELSE PRM_FIN - TRF_DEBUT
        END AS NB_FAC   
 FROM   T_TRF AS T
        INNER JOIN T_PRM AS P
              ON T.TRF_DEBUT BETWEEN P.PRM_DEBUT AND P.PRM_FIN
                 OR T.TRF_FIN BETWEEN P.PRM_DEBUT AND P.PRM_FIN)
SELECT * 
FROM   T_LIF

Et le résultat est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
TRF_DEBUT              TRF_FIN                TRF_PRIX                                PRM_DEBUT              PRM_FIN                NB_FAC
---------------------- ---------------------- --------------------------------------- ---------------------- ---------------------- ----------------------
8                      20                     1.50                                    19                     20                     1
20                     30                     2.00                                    20                     29                     9

Si vous voulez la somme des prix par tranches, alors changez juste la dernière requête comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH 
T_T AS
(SELECT TRF_ID, TRF_DEBUT,
        CASE 
           WHEN TRF_FIN < TRF_DEBUT THEN TRF_FIN + 24.0
           ELSE TRF_FIN
        END AS TRF_FIN,
        TRF_PRIX
 FROM   T_TARIF_TRF),
T_TRF AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX, 
         TRF_FIN - TRF_DEBUT AS TRF_NB_H 
 FROM    T_T),
T_P AS
(SELECT  19.0 AS PRM_DEBUT, 5.0 AS PRM_FIN 
 -- FROM DUAL -- si vous êtes sous Oracle
),
T_PRM AS
(SELECT  PRM_DEBUT,
         CASE
            WHEN PRM_FIN < PRM_DEBUT THEN PRM_FIN + 24.0
            ELSE PRM_FIN
         END AS PRM_FIN
 FROM    T_P),
T_LIF AS
(SELECT TRF_DEBUT, TRF_FIN, TRF_PRIX,
        CASE 
           WHEN PRM_DEBUT BETWEEN TRF_DEBUT AND TRF_FIN THEN PRM_DEBUT
           ELSE TRF_DEBUT
        END AS PRM_DEBUT,
        CASE 
           WHEN PRM_FIN BETWEEN TRF_DEBUT AND TRF_FIN THEN PRM_FIN
           ELSE TRF_FIN
        END AS PRM_FIN,
        CASE
           WHEN PRM_FIN NOT BETWEEN TRF_DEBUT AND TRF_FIN THEN TRF_FIN - PRM_DEBUT
           ELSE PRM_FIN - TRF_DEBUT
        END AS NB_FAC   
 FROM   T_TRF AS T
        INNER JOIN T_PRM AS P
              ON T.TRF_DEBUT BETWEEN P.PRM_DEBUT AND P.PRM_FIN
                 OR T.TRF_FIN BETWEEN P.PRM_DEBUT AND P.PRM_FIN)
SELECT SUM(NB_FAC * TRF_PRIX) AS TOTAL 
FROM   T_LIF

Qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
TOTAL
----------------------
19,5

Bien entendu en changeant seulement les données 19.0 et 5.0 de la 3e CTE, vous pouvez faire n'importe quel calcul. Je vous conseille de faire ceci soit dans une requête paramétrée (UDF table pas exemple) soit dans une procédure stockée.

J'attire cependant vote attention sur le fait que votre modèle est mal foutu : vous ne devriez pas avoir des bornes en heures pour vos calcul, mais en date heure. En effet, rien ne dit qu'une session de 19h à 5h ne représente que 10h. En effet si elle se prolonge sur plusieurs jours, elle peut donc faire au choix :
34, 58, 82... heures ou plus exactement n*24 + 10 (n étant >= 0).

A +

PS : pour apprendre SQL, mon bouquin, comme mon site web peut vous y aider. En particulier pour les CTE, lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...te-recursives/

**blaise4714** · 16/03/2009, 17h26

wow, t es fort
Merci pour l aide.

**blaise4714** · 17/03/2009, 10h34

salut
Je viens de tester le code dans differents situations. Il y a quelques erreurs,
entre 23H et 7h Le prix total doit etre 14.50 mais j ai 14 en retour. Et aussi lorsque je cherche le prix entre 23H et 01:00H le total c est 0.
Comment dois je faire contouner ces problemes?
Merci d avance

**SQLpro** · 19/03/2009, 15h35

Pour gérer tes cas un peu particulier,voici la correction.
En principe il faut que dans la table de tarification ;
les heures de debut aille de 0 à 23 et les heures de fin de 1 à 24. Sinon, les calculs seront faux.
Donc, contraintes CHECK à mettre au niveau de la saisie !

-- requête pour voir les lignes de calculs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
WITH 
T_T AS
(SELECT TRF_ID, TRF_DEBUT,
        CASE 
           WHEN TRF_FIN < TRF_DEBUT THEN TRF_FIN + 24.0
           ELSE TRF_FIN
        END AS TRF_FIN,
        TRF_PRIX
 FROM   T_TARIF_TRF),
T_TRF24 AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX, 
         TRF_FIN - TRF_DEBUT AS TRF_NB_H 
 FROM    T_T),
T_TRF AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN <= 24
 UNION   ALL
 SELECT  TRF_ID, TRF_DEBUT, 24, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN > 24
 UNION   ALL
 SELECT  TRF_ID, 0, TRF_FIN - 24, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN > 24
), 
T_P AS
(SELECT  23 AS PRM_DEBUT, 7.0 AS PRM_FIN 
 -- FROM DUAL -- si vous êtes sous Oracle
),
T_PRM24 AS
(SELECT  PRM_DEBUT, 
         CASE
            WHEN PRM_FIN < PRM_DEBUT THEN PRM_FIN + 24.0
            ELSE PRM_FIN
         END AS PRM_FIN
 FROM    T_P),
T_PRM AS
(SELECT  PRM_DEBUT, PRM_FIN
 FROM    T_PRM24
 WHERE   PRM_FIN <= 24
 UNION   ALL
 SELECT  PRM_DEBUT, 24
 FROM    T_PRM24
 WHERE   PRM_FIN > 24
 UNION   ALL
 SELECT  0, PRM_FIN - 24
 FROM    T_PRM24
 WHERE   PRM_FIN > 24) , 
T_LIF AS
(SELECT TRF_DEBUT, TRF_FIN, TRF_PRIX,
        CASE 
           WHEN PRM_DEBUT BETWEEN TRF_DEBUT 
                              AND TRF_FIN THEN PRM_DEBUT
           ELSE TRF_DEBUT
        END AS PRM_DEBUT,
        CASE 
           WHEN PRM_FIN BETWEEN TRF_DEBUT 
                            AND TRF_FIN THEN PRM_FIN
           ELSE TRF_FIN
        END AS PRM_FIN
 FROM   T_TRF AS T
        INNER JOIN T_PRM AS P
               ON    (    T.TRF_DEBUT >= P.PRM_DEBUT 
                      AND T.TRF_DEBUT < P.PRM_FIN)
                  OR (    T.TRF_FIN   > P.PRM_DEBUT 
                      AND T.TRF_FIN   <= P.PRM_FIN))
SELECT *, PRM_FIN - PRM_DEBUT AS NB_H 
FROM   T_LIF

-- requête de calcul somme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
WITH 
T_T AS
(SELECT TRF_ID, TRF_DEBUT,
        CASE 
           WHEN TRF_FIN < TRF_DEBUT THEN TRF_FIN + 24.0
           ELSE TRF_FIN
        END AS TRF_FIN,
        TRF_PRIX
 FROM   T_TARIF_TRF),
T_TRF24 AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX, 
         TRF_FIN - TRF_DEBUT AS TRF_NB_H 
 FROM    T_T),
T_TRF AS
(SELECT  TRF_ID, TRF_DEBUT, TRF_FIN, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN <= 24
 UNION   ALL
 SELECT  TRF_ID, TRF_DEBUT, 24, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN > 24
 UNION   ALL
 SELECT  TRF_ID, 0, TRF_FIN - 24, TRF_PRIX
 FROM    T_TRF24
 WHERE   TRF_FIN > 24
), 
T_P AS
(SELECT  23 AS PRM_DEBUT, 7.0 AS PRM_FIN 
 -- FROM DUAL -- si vous êtes sous Oracle
),
T_PRM24 AS
(SELECT  PRM_DEBUT, 
         CASE
            WHEN PRM_FIN < PRM_DEBUT THEN PRM_FIN + 24.0
            ELSE PRM_FIN
         END AS PRM_FIN
 FROM    T_P),
T_PRM AS
(SELECT  PRM_DEBUT, PRM_FIN
 FROM    T_PRM24
 WHERE   PRM_FIN <= 24
 UNION   ALL
 SELECT  PRM_DEBUT, 24
 FROM    T_PRM24
 WHERE   PRM_FIN > 24
 UNION   ALL
 SELECT  0, PRM_FIN - 24
 FROM    T_PRM24
 WHERE   PRM_FIN > 24) , 
T_LIF AS
(SELECT TRF_DEBUT, TRF_FIN, TRF_PRIX,
        CASE 
           WHEN PRM_DEBUT BETWEEN TRF_DEBUT 
                              AND TRF_FIN THEN PRM_DEBUT
           ELSE TRF_DEBUT
        END AS PRM_DEBUT,
        CASE 
           WHEN PRM_FIN BETWEEN TRF_DEBUT 
                            AND TRF_FIN THEN PRM_FIN
           ELSE TRF_FIN
        END AS PRM_FIN
 FROM   T_TRF AS T
        INNER JOIN T_PRM AS P
               ON    (    T.TRF_DEBUT >= P.PRM_DEBUT 
                      AND T.TRF_DEBUT < P.PRM_FIN)
                  OR (    T.TRF_FIN   > P.PRM_DEBUT 
                      AND T.TRF_FIN   <= P.PRM_FIN))
SELECT SUM(TRF_PRIX * ( PRM_FIN - PRM_DEBUT)) AS TRF_TOTAL
FROM   T_LIF

A +

MIcrosoft Server 2005 questions sur les heures

MS SQL Server

Discussions similaires

Partager

Partager