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
| SELECT distinct BOOK.totoNO,
PA.[OCODPASSENGERID],
titiE_REDUIT.CABINNO ,titiE_REDUIT.titiEID, titiE_REDUIT.totoSTATUSCODE,titiE_REDUIT.CATEGORYCODE
FROM DWH_toto.[dbo].[ODS_BKIN_toto_INFO] BOOK
INNER JOIN DWH_toto.[dbo].[ODS_CTRL_BK_CalculVersiontoto_Libelle] VERS_BOOK
ON (BOOK.ID_SOURCE = VERS_BOOK.ID_SOURCE and VERS_BOOK.FlagVersiontoto = 1)
INNER JOIN
(
SELECT titi.ID_BKIN ,titi.titiEID ,titi.CABINNO, titi.ID_BKCS ,
titi.totoSTATUSCODE,titi.CATEGORYCODE
FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titi
INNER JOIN
(
SELECT titiG.ID_BKIN ,titiG.titiEID ,titiG.CABINNO, MAX(titiG.ID_BKCS) as MAX_ID_BKCS
FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG
INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP ON CRUIP.ID_BKCS = titiG.ID_BKCS
INNER JOIN DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA ON PA.PASSENGERNO = CRUIP.PASSENGERNO
WHERE PA.OCODPASSENGERID IS NOT NULL AND not exists(select 1
FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG1
WHERE titiG1.ID_BKIN = titiG.ID_BKIN AND titiG.titiEID=titiG1.titiEID AND titiG.CABINNO=titiG1.CABINNO
AND titiG1.totoSTATUSCODE <> titiG.totoSTATUSCODE) GROUP BY titiG.ID_BKIN ,titiG.titiEID,titiG.CABINNO
UNION
SELECT titiG.ID_BKIN ,titiG.titiEID ,titiG.CABINNO, MAX(titiG.ID_BKCS) as MAX_ID_BKCS
FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG
INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP ON CRUIP.ID_BKCS = titiG.ID_BKCS
INNER JOIN DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA ON PA.PASSENGERNO = CRUIP.PASSENGERNO
WHERE PA.OCODPASSENGERID IS NOT NULL AND titiG.totoSTATUSCODE = 'BKD' and exists(select 1
FROM DWH_toto.[dbo].[ODS_BKCS_titiE_SAILING] titiG1
WHERE titiG1.ID_BKIN = titiG.ID_BKIN AND titiG.titiEID=titiG1.titiEID AND titiG.CABINNO=titiG1.CABINNO
AND titiG1.totoSTATUSCODE = 'CXL' ) GROUP BY titiG.ID_BKIN ,titiG.titiEID,titiG.CABINNO
) titiGG
ON titi.ID_BKIN = titiGG.ID_BKIN
AND titi.ID_BKCS = titiGG.MAX_ID_BKCS
) titiE_REDUIT
ON BOOK.ID_BKIN = titiE_REDUIT.ID_BKIN
INNER JOIN DWH_toto.[dbo].[ODS_BKCP_titiE_PARTICIPANT] CRUIP
ON titiE_REDUIT.ID_BKCS = CRUIP.[ID_BKCS]
INNER JOIN DWH_toto.[dbo].[ODS_BKPA_PARTICIPANT] PA
ON (PA.ID_BKIN = BOOK.ID_BKIN
AND CRUIP.PASSENGERNO = PA.PASSENGERNO and PA.[OCODPASSENGERID] is not NULL) |
