Bonjour à tous,
Voici une requête
[CODE=SQL]que je voudrais transformer en quelque chose de similaire à ça i.e en supprimant la jointure left join. Je pré-sens que c'est possible mais ça bug dans ma tête :
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 SELECT b.pays,b.marque, b.hotel, b.date,b.season,b.day, b.TIME, b.room, NBresatot , NBresatotOther INTO TauxOccupation FROM ( select pays, marque,hotel,date,season,day,TIME,room, cast(( select count(*) from reservation where TIME>=a.TIME and hotel=a.hotel and date=a.date ) as decimal(18,6)) as NBresatot from reservation a group by pays, marque,hotel,date,season,day,TIME,room) b left join (select pays, marque,hotel,date,season,day,TIME,room, cast(( select count(*) from reservation where TIME>=a.TIME and hotel=a.hotel and date=a.date and channel_group = 'Other' ) as decimal(18,6)) as NBresatotOther from reservation a where channel_group = 'Other ' group by pays, marque,hotel,date,season,day,TIME,room) c on b.hotel = c.hotel and b.date = c.date and b.time = c.time
[CODE=SQL]Merci pour votre aide
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 SELECT a.pays, a.marque, a.hotel, a.date, a.season, a.day,a.TIME,a.room, (select count(*) from reservation where TIME>=a.TIME and hotel=a.hotel and date=a.date ) NbrResaTotal, (SELECT count(*) as NBresatotOlta , c.pays, c.marque, c.hotel, c.date, c.season, c.day, c.TIME, c.room FROM (SELECT b.pays, b.marque, b.hotel, b.date, b.season, b.day, b.TIME, b.room from reservation b where channel_group = 'Other' group by b.pays, b.marque, b.hotel, b.date, b.season, b.day, b.TIME, b.room ) c where c.hotel= a.hotel and c.date= a.date and c.TIME>= a.TIME group by c.pays, c.marque, c.hotel, c.date, c.season, c.day, c.TIME, c.room ) as NBresatotOther into Ratio FROM reservation a group by a.pays, a.marque, a.hotel, a.date, a.season, a.day,a.TIME, a.room
Partager