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
| WITH
T AS
(
SELECT COUNT(DISTINCT ID_BKCS )
FROM dbo.ODS_BKCS_CRUISE_SAILING AS CRUIS
GROUP BY ID_BKIN
HAVING COUNT(CustomerID) > 1
)
SELECT CRUIS.ID_BKIN ,CRUIS.TITI ,CRUIS.TATA, CRUIS.ID_BKCS , CRUIS.BOOKINGSTATUSCODE, CRUIS.CATEGORYCODE
FROM DWH_BOOKING.dbo.ODS_BKCS_TUTU_SAILING AS CRUIS
INNER JOIN (SELECT CRUISG.ID_BKIN ,CRUISG.TITI ,CRUISG.TATA, MAX(CRUISG.ID_BKCS) as MAX_ID_BKCS
FROM DWH_BOOKING.dbo.ODS_BKCS_TUTU_SAILING AS CRUISG
INNER JOIN DWH_BOOKING.dbo.ODS_BKCP_TUTU_PARTICIPANT AS CRUIP
ON CRUISG.ID_SOURCE = CRUIP.ID_SOURCE and CRUISG.ID_BKCS = CRUIP.ID_BKCS
INNER JOIN DWH_BOOKING.dbo.ODS_BKPA_PARTICIPANT AS PA
ON CRUIP.ID_SOURCE = PA.ID_SOURCE and PA.PASSENGERNO = CRUIP.PASSENGERNO
WHERE PA.MARKETINGID is not null
AND CRUISG.BOOKINGSTATUSCODE <> 'CXL'
GROUP BY CRUISG.ID_BKIN ,CRUISG.TITI, CRUISG.TATA) AS TOTOD
ON CRUIS.ID_BKIN = TOTOD.ID_BKIN AND CRUIS.ID_BKCS = TOTOD.MAX_ID_BKCS
AND EXISTS(SELECT * FROM T)
UNION ALL
SELECT CRUIS.ID_BKIN ,CRUIS.TITI ,CRUIS.TATA, CRUIS.ID_BKCS , CRUIS.BOOKINGSTATUSCODE,CRUIS.CATEGORYCODE
FROM dbo.ODS_BKCS_CRUISE_SAILING AS CRUIS
INNER JOIN (SELECT TOTO.ID_BKIN ,TOTO.TITI ,TOTO.TATA, MAX(TOTO.ID_BKCS) as MAX_ID_BKCS
FROM dbo.ODS_BKCS_CRUISE_SAILING AS TOTO
GROUP BY TOTO.ID_BKIN ,TOTO.TITI,TOTO.TATA) AS TOTOG
ON CRUIS.ID_BKIN = TOTOG.ID_BKIN AND CRUIS.ID_BKCS = TOTOG.MAX_ID_BKCS
AND NOT EXISTS(SELECT * FROM T); |
Partager