Anonymous Anonymous - 1 month ago
1062 0

SQL for Disasterkid

SQL

Disasterkid

SELECT DISTINCT 
	a.PassengerId
	,a.BookingKey
	,a.FlightId
	,a.Email
	,a.FirstDepartureDateTime
	,a.Origin
	,a.Destination
	,a.FirstName
	,a.LastName
	,a.Gender
	,a.FrequentFlyerId
	,a.HasAncillaries
	,a.HasAncillaryBioFuel
FROM (
	SELECT DISTINCT 
		t8.BookingKey
		,t8.Email
		,t8.PassengerId
		,t8.FirstDepartureDateTime
		,t8.FirstName
		,t8.LastName
		,t8.FrequentFlyerId
		,t9.refFirst_FlownTransactionID AS FlightId
		,Gender
		,HasAncillaries
		,HasAncillaryBioFuel
		,Destination
		,Origin
	FROM (
		SELECT DISTINCT 
			t1.BookingKey
			,t3.Email
			,t3.PassengerID
			,t2.FirstDepartureDateTime
			,t3.FirstName
			,t3.LastName
			,t3.FrequentFlyerId
			,t3.Gender
			,t2.HasAncillaries
			,t2.HasAncillaryBioFuel
			,t2.Destination
			,t2.FirstDepartureStation AS Origin
		FROM [Flights] AS t1
		INNER JOIN [Bookings] AS t2 ON t1.BookingKey = t2.BookingKey
		INNER JOIN [Customers] AS t3 ON t1.PassengerID = t3.PassengerID
		WHERE convert(date,t2.BookingDateTime) = convert(date, dateadd(DAY, -2, getdate()))
        AND IsBooked = 1
        AND IsPassenger = 1
        AND t1.IsCancelled = 0
        AND t1.IsCrew = 0
        AND t1.RBD NOT IN ('I','Z','R')
        AND t1.PassengerType NOT IN ('CHD')
        AND t2.SalesChannel <> 'Travel Agency'
        AND t3.IsBRAMember = 1
        AND t3.CommunicationOptIn = 1
        AND t3.FrequentFlyerId = t1.FrequentFlyerId
        AND DATEDIFF(DAY, t2.BookingDateTime, t2.FirstDepartureDateTime) >= 3
        AND t3.FirstBookingEverKey = t1.BookingKey
	) AS t8
	INNER JOIN (
		SELECT DISTINCT 
			BookingKey
			,PassengerID
			,FIRST_VALUE(FlownTransactionID) OVER (PARTITION BY BookingKey,PassengerID ORDER BY BookingKey,PassengerID,ScheduledDepartureDateTime) AS refFirst_FlownTransactionID
		FROM [Flights] AS z
	) AS t9 ON t8.BookingKey = t9.BookingKey AND t8.PassengerID = t9.PassengerID

	UNION 
	
	SELECT DISTINCT 
		t8.BookingKey
		,t8.Email
		,t8.PassengerId
		,t8.FirstDepartureDateTime
		,NULL AS FirstName
		,NULL AS LastName
		,NULL AS FrequentFlyerId
		,refFirst_FlownTransactionID AS FlightId
		,Gender
		,HasAncillaries
		,HasAncillaryBioFuel
		,Destination
		,Origin
	FROM (
		SELECT DISTINCT
			BookingKey
			,Email
			,FIRST_VALUE(PassengerID) OVER (PARTITION BY BookingKey ORDER BY BookingKey,first3CharMatch DESC, CountTrips12M DESC, PassengerID DESC) AS PassengerId
			,FirstDepartureDateTime
			,NULL AS FirstName
			,NULL AS LastName
			,NULL AS FrequentFlyerId
			,Gender
			,HasAncillaries
			,HasAncillaryBioFuel
			,Destination
			,Origin
		FROM (
			SELECT DISTINCT
				t1.BookingKey
				,t2.BookingDateTime
				,t2.email_booker
				,t3.Email
				,t2.FirstDepartureDateTime
				,CASE
					WHEN LEFT(replace(replace(replace(replace(lower(t1.PassengerFirstName), 'ø', 'o'), 'å', 'a'), 'ä', 'a'), 'ö', 'o'), 3) = LEFT(t2.email_booker, 3) THEN 1
					ELSE 0
					END AS first3CharMatch
				,t3.CountTrips12M
				,t3.IsBRAMember
				,t1.PassengerID
				,t1.PassengerFirstName
				,t3.Gender
				,t2.HasAncillaries
				,t2.HasAncillaryBioFuel
				,t2.Destination
				,t2.FirstDepartureStation AS Origin
			FROM [Flights] AS t1
			INNER JOIN [Bookings] AS t2 ON t1.BookingKey = t2.BookingKey
			INNER JOIN [Customers] AS t3 ON t1.PassengerID = t3.PassengerID
			WHERE convert(date, t2.BookingDateTime) = convert(date, dateadd(DAY, -2, getdate()))
			AND t1.IsBooked = 1
			AND t1.IsPassenger = 1
			AND t1.IsCancelled = 0
			AND t1.IsCrew = 0
			AND t1.RBD NOT IN ('I','Z','R')
			AND t1.PassengerType NOT IN ('CHD')
			AND t2.SalesChannel <> 'Travel Agency'
			AND t3.IsBRAMember = 0
			AND t3.Email IS NOT NULL
			AND t3.Email = t2.email_booker
			AND DATEDIFF(DAY, t2.BookingDateTime, t2.FirstDepartureDateTime) >= 3
			AND t3.FirstBookingEverKey = t1.BookingKey
		) AS t1
	) AS t8
	INNER JOIN (
		SELECT DISTINCT 
			BookingKey
			,PassengerID
			,FIRST_VALUE(FlownTransactionID) OVER (PARTITION BY BookingKey,PassengerID ORDER BY BookingKey,PassengerID,ScheduledDepartureDateTime) AS refFirst_FlownTransactionID
		FROM [Flights]) AS t9 ON t8.BookingKey = t9.BookingKey AND t8.PassengerID = t9.PassengerID
) a
INNER JOIN [Flights] b ON a.FlightId = b.FlownTransactionID and a.PassengerId = b.PassengerID
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download