Requête SQL avec date Range

Version imprimable

Bonjour,

Voila j'ai un formulaire qui va chercher un tarif dans une table.
La table possède 3 champs : le prix, début et fin
Dans mon formulaire j'ai deux datepicker pour sélectionner les dates de début et de fin.
Mon but est récupèrer les prix selon la date choisie, c'est à dire si je sélectionne 01/12/2016 et 15/12/2016, je souhaite afficher un tableau avec les prix entre ces deux dates.

Exemple :

Si j'ai 3 tarifs du genre :

350€ début 2016-12-01 fin 2016-12-10
250€ début 2016-12-04 fin 2016-12-14
150€*début 2016-12-09 fin 2016-12-21

Et que dans mon formulaire je sélectionne la plage 2016-12-02 jusqu'à 2016-12-11 alors je dois avoir un tableau: 9 prix à 350€, 8 prix à 250€ et 3 pris à 150€ (toutes ces données sous forme de tableau regourpé par jour)

Pour le moment j'ai trouvé ça :
Code:

1 2 3 4 5 6 7 8 select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where selected_date between '2016-12-02' and '2016-12-11'
qui me permet d'avoir une liste des jours d'une plage donnée, donc l'étape d'après est de vérifier pour chaque jour si la date se trouve entre début et fin et donc récupérer le prix...

Je ne sais pas du tout si c'est la meilleure façon de faire, alors je suis ouvert à toute suggestion.

J'espère que c'est assez clair :/

Merci énormément pour votre aide,

20/12/2016, 08h17
vttman

Hello Afourti,

ça a l'air clair ...

Maintenant je suppose que ces tarifs sont stockés dans une table ?

Citation:

350€ début 2016-12-01 fin 2016-12-10
250€ début 2016-12-04 fin 2016-12-14
150€*début 2016-12-09 fin 2016-12-21

Et si je choisis le jour 2016-12-5 par exemple, mon tarif sera de 350, de 350 + 250, de 250 ?
Et pour 2016-12-10 ?
20/12/2016, 09h26
afourti

Hello,

Merci pour ta réponse.
Si on choisit le 2016-12-05 alors le tarifs qui va apparaitre est 350 et 250. Et si on choisit le 2016-12-10 alors les 3 tarifs vont s'afficher (ce n'est pas une addition)

En ce cas la réponse est très simple :

Code:

1
2
3
4
5
 
Select col1, col2, ..., coln
from matable
where date_deb <= @madate
  and date_fin >= @madate

20/12/2016, 10h09
afourti

Ah ben j'aurai bien aimé que ce soit comme ça...sauf que la variable @madate est un range aussi... :D

Voici une solution non élégante mais qui peut donner des idées ...

Code:

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
 
CREATE TABLE `tarif` (
  `mt` int(11) NOT NULL,
  `ti` date NOT NULL,
  `tf` date NOT NULL,
  `id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `tarif`
--
 
INSERT INTO `tarif` (`mt`, `ti`, `tf`, `id`) VALUES
(350, '2016-12-01', '2016-12-10', 1),
(550, '2016-12-04', '2016-12-14', 2),
(150, '2016-12-09', '2016-12-21', 3);
 
 
CREATE function ctarifw (datetest DATE) RETURNS varchar(50)  
BEGIN
    declare montant1 int;
    declare montant2 int;
    declare montant3 int;
	set montant1 = -1;
	set montant2 = -1;
	set montant3 = -1;
 
    SELECT mt   into montant1
    FROM tarif
    WHERE id = 1 and datetest between ti and tf;
    SELECT mt  into montant2
    FROM tarif
    WHERE id = 2 and datetest between ti and tf;
    SELECT mt   into montant3
    FROM tarif
    WHERE id = 3 and datetest between ti and tf;
    return concat (montant1, '*', montant2,'*',montant3)  ;
END |
DELIMITER ;  -- On remet le délimiteur par défaut
 
select selected_date,ctarifW(selected_date) from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2016-12-02' and '2016-12-11';

Résultat

Citation:

2016-12-02 350*-1*-1
2016-12-03 350*-1*-1
2016-12-04 350*550*-1
2016-12-05 350*550*-1
2016-12-06 350*550*-1
2016-12-07 350*550*-1
2016-12-08 350*550*-1
2016-12-09 350*550*150
2016-12-10 350*550*150
2016-12-11 -1*550*150

21/12/2016, 22h51
afourti

Hello,

Désolé, pour ma réponse tardive.
Merci pour le début de solution, je vais essayé de trouver un moyen pour l'améliorer

Salut Afourti.

Code:

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `prix`
--------------
 
--------------
CREATE TABLE `prix`
( `id`           integer unsigned not null auto_increment primary key,
  `prix`         decimal(15,2)    not null,
  `datedeb`      date             not null,
  `datefin`      date             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `prix` (`prix`,`datedeb`,`datefin`) values
  (350.00, '2016-12-01', '2016-12-10'),
  (250.00, '2016-12-04', '2016-12-14'),
  (150.00, '2016-12-09', '2016-12-21')
--------------
 
--------------
select * from prix
--------------
 
+----+--------+------------+------------+
| id | prix   | datedeb    | datefin    |
+----+--------+------------+------------+
|  1 | 350.00 | 2016-12-01 | 2016-12-10 |
|  2 | 250.00 | 2016-12-04 | 2016-12-14 |
|  3 | 150.00 | 2016-12-09 | 2016-12-21 |
+----+--------+------------+------------+
--------------
DROP TABLE IF EXISTS `travail`
--------------
 
--------------
CREATE TABLE `travail`
( `datejour`     date             not null,
  `prix`         decimal(15,2)    not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
drop procedure if exists `trait`
--------------
 
--------------
create procedure `trait`
(
        IN  _datedeb  date,
        IN  _datefin  date
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _date DATE          DEFAULT NULL;
  DECLARE _prix DECIMAL(15,2) DEFAULT 0;
 
  DECLARE _fin  INTEGER       DEFAULT 1;
  DECLARE _tab  CURSOR FOR SELECT prix FROM prix where datedeb <= _date and _date <= datefin;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  SET _date = _datedeb;
  WHILE (_date <= _datefin)
  DO
        SET   _fin = 1;
    OPEN  _tab;
    FETCH _tab INTO _prix;
    WHILE (_fin)
    DO
      INSERT INTO travail (`datejour`,`prix`) values (_date, _prix);
      FETCH _tab INTO _prix;
    END WHILE;
    CLOSE _tab;
    SET _date = date_add(_date, interval 1 day);
  END WHILE;
END
--------------
 
--------------
call trait('2016-12-02', '2016-12-11')
--------------
 
--------------
select * from travail order by prix, datejour
--------------
 
+------------+--------+
| datejour   | prix   |
+------------+--------+
| 2016-12-09 | 150.00 |
| 2016-12-10 | 150.00 |
| 2016-12-11 | 150.00 |
| 2016-12-04 | 250.00 |
| 2016-12-05 | 250.00 |
| 2016-12-06 | 250.00 |
| 2016-12-07 | 250.00 |
| 2016-12-08 | 250.00 |
| 2016-12-09 | 250.00 |
| 2016-12-10 | 250.00 |
| 2016-12-11 | 250.00 |
| 2016-12-02 | 350.00 |
| 2016-12-03 | 350.00 |
| 2016-12-04 | 350.00 |
| 2016-12-05 | 350.00 |
| 2016-12-06 | 350.00 |
| 2016-12-07 | 350.00 |
| 2016-12-08 | 350.00 |
| 2016-12-09 | 350.00 |
| 2016-12-10 | 350.00 |
+------------+--------+
--------------
select datejour,
       max(prix1) as prix1,
       max(prix2) as prix2,
       max(prix3) as prix3
from (
    select datejour,
           case prix when 350.00 then prix else null end as prix1,
           case prix when 250.00 then prix else null end as prix2,
           case prix when 150.00 then prix else null end as prix3
    from travail) as x
group by datejour
--------------
 
+------------+--------+--------+--------+
| datejour   | prix1  | prix2  | prix3  |
+------------+--------+--------+--------+
| 2016-12-02 | 350.00 |   NULL |   NULL |
| 2016-12-03 | 350.00 |   NULL |   NULL |
| 2016-12-04 | 350.00 | 250.00 |   NULL |
| 2016-12-05 | 350.00 | 250.00 |   NULL |
| 2016-12-06 | 350.00 | 250.00 |   NULL |
| 2016-12-07 | 350.00 | 250.00 |   NULL |
| 2016-12-08 | 350.00 | 250.00 |   NULL |
| 2016-12-09 | 350.00 | 250.00 | 150.00 |
| 2016-12-10 | 350.00 | 250.00 | 150.00 |
| 2016-12-11 |   NULL | 250.00 | 150.00 |
+------------+--------+--------+--------+
--------------
select datejour, group_concat(prix separator ';') as prix
from travail
group by datejour
--------------
 
+------------+----------------------+
| datejour   | prix                 |
+------------+----------------------+
| 2016-12-02 | 350.00               |
| 2016-12-03 | 350.00               |
| 2016-12-04 | 350.00;250.00        |
| 2016-12-05 | 250.00;350.00        |
| 2016-12-06 | 350.00;250.00        |
| 2016-12-07 | 350.00;250.00        |
| 2016-12-08 | 250.00;350.00        |
| 2016-12-09 | 350.00;250.00;150.00 |
| 2016-12-10 | 350.00;250.00;150.00 |
| 2016-12-11 | 250.00;150.00        |
+------------+----------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

22/12/2016, 00h44
afourti

Waou!!! Juste nikel!!!
Un grand merci à toi!!!
22/12/2016, 01h17
Artemus24

Salut Afourti.

Il est nécessaire de passer par une table de travail afin de mettre en forme les lignes produites par la procédure stockée.
C'est dommage, à l'inverse de FireBird, que MySql ne sache par exécuter un select directement à partir d'une procédure stockée.

Le premier select sert à faire un vidage brut du contenu de la table Travail.
Le deuxième select fait une présentation sous forme de colonnes de tes résultats.
Le troisième et dernier select fait presque la même chose que le deuxième select, mais en se servant d'un group_concat.
Il sera alors nécessaire en php, d'extraire ce dont tu as besoin et de le mettre en forme dans la présentation de tes résultats.

La fonction donnée par VTTMAN ne permet pas de résoudre ton problème, car une fonction produit un résultat pour une ligne et une colonne données.
Il est nécessaire de passer par une procédure stockée afin de créer, dans une table de travail, toutes les lignes répondant à l'intervalle que tu demandes.
Et puis, le select est trop compliqué à mettre en oeuvre.

@+
22/12/2016, 12h09
afourti

Yes! Merci pour tes éxplication.
Je m'en doutais que ça allait être compliqué voire impossible d'avoir un select direct pour avoir mon résultat.
Encore merci à toi.