Morning People,
I'm having a little issue with COALESCE causing a stored procedure which is writing data to a table to run excessively. (without the COALESCE it takes 4 minutes and with it it takes 1 hour 30 minutes ish).
I need this staging table's f_ columns to be clear of NULLs as these will be used for calculus later on in the warehouse which is then being displayed to Cognos reporting studio.
There are 161 columns that require this and I have also tried to do a "CASE WHEN X IS NULL THEN 0 ELSE X" which apparently has a lower impact but still takes an Hour and a half.
Also have added "DEFAULT 0" to the tables build with no joys.
Stored Procedure follows (Apologies in advance for the sheer size of it)
TRUNCATE TABLE
ST_PIT_BOOKINGCOSTS
INSERT INTO
ST_PIT_BOOKINGCOSTS
SELECT DISTINCT
ST_PIT_BOOKINGHEADERS.i_SK_Bkg,
ST_PIT_BOOKINGHEADERS.i_SK_Version,
0 AS f_BkgTransportPax,
0 AS f_BkgTransportQuantity,
0 AS f_BkgTransportAdults,
0 AS f_BkgTransportChildren,
0 AS f_BkgTransportInfants,
0 AS f_BkgTransportRevenue,
0 AS f_BkgTransportCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Pax,0) AS f_BkgFltPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Quantity,0) AS f_BkgFltQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Adults,0) AS f_BkgFltAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Children,0) AS f_BkgFltChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Infants,0) AS f_BkgFltInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Revenue,0) AS f_BkgFltRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Cost,0) AS f_BkgFltCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Pax,0) AS f_BkgFerryPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Quantity,0) AS f_BkgFerryQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Adults,0) AS f_BkgFerryAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Children,0) AS f_BkgFerryChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Infants,0) AS f_BkgFerryInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Revenue,0) AS f_BkgFerryRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Cost,0) AS f_BkgFerryCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Pax,0) AS f_BkgTrainPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Quantity,0) AS f_BkgTrainQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Adults,0) AS f_BkgTrainAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Children,0) AS f_BkgTrainChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Infants,0) AS f_BkgTrainInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Revenue,0) AS f_BkgTrainRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Cost,0) AS f_BkgTrainCost,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Pax,0) AS f_BkgAccomPax,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Quantity,0) AS f_BkgAccomQuantity,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Adults,0) AS f_BkgAccomAdults,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Children,0) AS f_BkgAccomChildren,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Infants,0) AS f_BkgAccomInfants,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Revenue,0) AS f_BkgAccomRevenue,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Cost,0) AS f_BkgAccomCost,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Pax,0) AS f_BkgExtraACTIPax,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Quantity,0) AS f_BkgExtraACTIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Adults,0) AS f_BkgExtraACTIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Children,0) AS f_BkgExtraACTIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Infants,0) AS f_BkgExtraACTIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Revenue,0) AS f_BkgExtraACTIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Cost,0) AS f_BkgExtraACTICost,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Pax,0) AS f_BkgExtraCARHPax,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Quantity,0) AS f_BkgExtraCARHQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Adults,0) AS f_BkgExtraCARHAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Children,0) AS f_BkgExtraCARHChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Infants,0) AS f_BkgExtraCARHInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Revenue,0) AS f_BkgExtraCARHRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Cost,0) AS f_BkgExtraCARHCost,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Pax,0) AS f_BkgExtraCARIPax,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Quantity,0) AS f_BkgExtraCARIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Adults,0) AS f_BkgExtraCARIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Children,0) AS f_BkgExtraCARIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Infants,0) AS f_BkgExtraCARIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Revenue,0) AS f_BkgExtraCARIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Cost,0) AS f_BkgExtraCARICost,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Pax,0) AS f_BkgExtraCHLDPax,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Quantity,0) AS f_BkgExtraCHLDQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Adults,0) AS f_BkgExtraCHLDAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Children,0) AS f_BkgExtraCHLDChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Infants,0) AS f_BkgExtraCHLDInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Revenue,0) AS f_BkgExtraCHLDRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Cost,0) AS f_BkgExtraCHLDCost,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Pax,0) AS f_BkgExtraCLUBPax,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Quantity,0) AS f_BkgExtraCLUBQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Adults,0) AS f_BkgExtraCLUBAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Children,0) AS f_BkgExtraCLUBChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Infants,0) AS f_BkgExtraCLUBInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Revenue,0) AS f_BkgExtraCLUBRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Cost,0) AS f_BkgExtraCLUBCost,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Pax,0) AS f_BkgExtraCRECPax,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Quantity,0) AS f_BkgExtraCRECQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Adults,0) AS f_BkgExtraCRECAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Children,0) AS f_BkgExtraCRECChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Infants,0) AS f_BkgExtraCRECInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Revenue,0) AS f_BkgExtraCRECRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Cost,0) AS f_BkgExtraCRECCost,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Pax,0) AS f_BkgExtraEQUIPax,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Quantity,0) AS f_BkgExtraEQUIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Adults,0) AS f_BkgExtraEQUIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Children,0) AS f_BkgExtraEQUIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Infants,0) AS f_BkgExtraEQUIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Revenue,0) AS f_BkgExtraEQUIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Cost,0) AS f_BkgExtraEQUICost,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Pax,0) AS f_BkgExtraEXCUPax,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Quantity,0) AS f_BkgExtraEXCUQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Adults,0) AS f_BkgExtraEXCUAdults,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Children,0) AS f_BkgExtraEXCUChildren,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Infants,0) AS f_BkgExtraEXCUInfants,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Revenue,0) AS f_BkgExtraEXCURevenue,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Cost,0) AS f_BkgExtraEXCUCost,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Pax,0) AS f_BkgExtraFLTRPax,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Quantity,0) AS f_BkgExtraFLTRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Adults,0) AS f_BkgExtraFLTRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Children,0) AS f_BkgExtraFLTRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Infants,0) AS f_BkgExtraFLTRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Revenue,0) AS f_BkgExtraFLTRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Cost,0) AS f_BkgExtraFLTRCost,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Pax,0) AS f_BkgExtraINSRPax,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Quantity,0) AS f_BkgExtraINSRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Adults,0) AS f_BkgExtraINSRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Children,0) AS f_BkgExtraINSRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Infants,0) AS f_BkgExtraINSRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Revenue,0) AS f_BkgExtraINSRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Cost,0) AS f_BkgExtraINSRCost,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Pax,0) AS f_BkgExtraOTHRPax,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Quantity,0) AS f_BkgExtraOTHRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Adults,0) AS f_BkgExtraOTHRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Children,0) AS f_BkgExtraOTHRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Infants,0) AS f_BkgExtraOTHRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Revenue,0) AS f_BkgExtraOTHRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Cost,0) AS f_BkgExtraOTHRCost,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Pax,0) AS f_BkgExtraSKIPPax,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Quantity,0) AS f_BkgExtraSKIPQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Adults,0) AS f_BkgExtraSKIPAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Children,0) AS f_BkgExtraSKIPChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Infants,0) AS f_BkgExtraSKIPInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Revenue,0) AS f_BkgExtraSKIPRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Cost,0) AS f_BkgExtraSKIPCost,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Pax,0) AS f_BkgExtraSKITPax,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Quantity,0) AS f_BkgExtraSKITQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Adults,0) AS f_BkgExtraSKITAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Children,0) AS f_BkgExtraSKITChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Infants,0) AS f_BkgExtraSKITInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Revenue,0) AS f_BkgExtraSKITRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Cost,0) AS f_BkgExtraSKITCost,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Pax,0) AS f_BkgExtraSPIOPax,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Quantity,0) AS f_BkgExtraSPIOQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Adults,0) AS f_BkgExtraSPIOAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Children,0) AS f_BkgExtraSPIOChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Infants,0) AS f_BkgExtraSPIOInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Revenue,0) AS f_BkgExtraSPIORevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Cost,0) AS f_BkgExtraSPIOCost,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Pax,0) AS f_BkgExtraSPORPax,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Quantity,0) AS f_BkgExtraSPORQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Adults,0) AS f_BkgExtraSPORAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Children,0) AS f_BkgExtraSPORChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Infants,0) AS f_BkgExtraSPORInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Revenue,0) AS f_BkgExtraSPORRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Cost,0) AS f_BkgExtraSPORCost,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Pax,0) AS f_BkgExtraSTNDPax,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Quantity,0) AS f_BkgExtraSTNDQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Adults,0) AS f_BkgExtraSTNDAdults,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Children,0) AS f_BkgExtraSTNDChildren,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Infants,0) AS f_BkgExtraSTNDInfants,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Revenue,0) AS f_BkgExtraSTNDRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Cost,0) AS f_BkgExtraSTNDCost,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Pax,0) AS f_BkgExtraTRAFPax,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Quantity,0) AS f_BkgExtraTRAFQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Adults,0) AS f_BkgExtraTRAFAdults,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Children,0) AS f_BkgExtraTRAFChildren,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Infants,0) AS f_BkgExtraTRAFInfants,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Revenue,0) AS f_BkgExtraTRAFRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Cost,0) AS f_BkgExtraTRAFCost,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Pax,0) AS f_BkgExtraXXXXPax,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Quantity,0) AS f_BkgExtraXXXXQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Adults,0) AS f_BkgExtraXXXXAdults,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Children,0) AS f_BkgExtraXXXXChildren,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Infants,0) AS f_BkgExtraXXXXInfants,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Revenue,0) AS f_BkgExtraXXXXRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Cost,0) AS f_BkgExtraXXXXCost,
0 AS f_DiffTransportPax,
0 AS f_DiffTransportQuantity,
0 AS f_DiffTransportAdults,
0 AS f_DiffTransportChildren,
0 AS f_DiffTransportInfants,
0 AS f_DiffTransportRevenue,
0 AS f_DiffTransportCost,
0 AS f_DiffFltPax,
0 AS f_DiffFltQuantity,
0 AS f_DiffFltAdults,
0 AS f_DiffFltChildren,
0 AS f_DiffFltInfants,
0 AS f_DiffFltRevenue,
0 AS f_DiffFltCost,
0 AS f_DiffFerryPax,
0 AS f_DiffFerryQuantity,
0 AS f_DiffFerryAdults,
0 AS f_DiffFerryChildren,
0 AS f_DiffFerryInfants,
0 AS f_DiffFerryRevenue,
0 AS f_DiffFerryCost,
0 AS f_DiffTrainPax,
0 AS f_DiffTrainQuantity,
0 AS f_DiffTrainAdults,
0 AS f_DiffTrainChildren,
0 AS f_DiffTrainInfants,
0 AS f_DiffTrainRevenue,
0 AS f_DiffTrainCost,
0 AS f_DiffAccomPax,
0 AS f_DiffAccomQuantity,
0 AS f_DiffAccomAdults,
0 AS f_DiffAccomChildren,
0 AS f_DiffAccomInfants,
0 AS f_DiffAccomRevenue,
0 AS f_DiffAccomCost,
0 AS f_DiffExtraACTIPax,
0 AS f_DiffExtraACTIQuantity,
0 AS f_DiffExtraACTIAdults,
0 AS f_DiffExtraACTIChildren,
0 AS f_DiffExtraACTIInfants,
0 AS f_DiffExtraACTIRevenue,
0 AS f_DiffExtraACTICost,
0 AS f_DiffExtraCARHPax,
0 AS f_DiffExtraCARHQuantity,
0 AS f_DiffExtraCARHAdults,
0 AS f_DiffExtraCARHChildren,
0 AS f_DiffExtraCARHInfants,
0 AS f_DiffExtraCARHRevenue,
0 AS f_DiffExtraCARHCost,
0 AS f_DiffExtraCARIPax,
0 AS f_DiffExtraCARIQuantity,
0 AS f_DiffExtraCARIAdults,
0 AS f_DiffExtraCARIChildren,
0 AS f_DiffExtraCARIInfants,
0 AS f_DiffExtraCARIRevenue,
0 AS f_DiffExtraCARICost,
0 AS f_DiffExtraCHLDPax,
0 AS f_DiffExtraCHLDQuantity,
0 AS f_DiffExtraCHLDAdults,
0 AS f_DiffExtraCHLDChildren,
0 AS f_DiffExtraCHLDInfants,
0 AS f_DiffExtraCHLDRevenue,
0 AS f_DiffExtraCHLDCost,
0 AS f_DiffExtraCLUBPax,
0 AS f_DiffExtraCLUBQuantity,
0 AS f_DiffExtraCLUBAdults,
0 AS f_DiffExtraCLUBChildren,
0 AS f_DiffExtraCLUBInfants,
0 AS f_DiffExtraCLUBRevenue,
0 AS f_DiffExtraCLUBCost,
0 AS f_DiffExtraCRECPax,
0 AS f_DiffExtraCRECQuantity,
0 AS f_DiffExtraCRECAdults,
0 AS f_DiffExtraCRECChildren,
0 AS f_DiffExtraCRECInfants,
0 AS f_DiffExtraCRECRevenue,
0 AS f_DiffExtraCRECCost,
0 AS f_DiffExtraEQUIPax,
0 AS f_DiffExtraEQUIQuantity,
0 AS f_DiffExtraEQUIAdults,
0 AS f_DiffExtraEQUIChildren,
0 AS f_DiffExtraEQUIInfants,
0 AS f_DiffExtraEQUIRevenue,
0 AS f_DiffExtraEQUICost,
0 AS f_DiffExtraEXCUPax,
0 AS f_DiffExtraEXCUQuantity,
0 AS f_DiffExtraEXCUAdults,
0 AS f_DiffExtraEXCUChildren,
0 AS f_DiffExtraEXCUInfants,
0 AS f_DiffExtraEXCURevenue,
0 AS f_DiffExtraEXCUCost,
0 AS f_DiffExtraFLTRPax,
0 AS f_DiffExtraFLTRQuantity,
0 AS f_DiffExtraFLTRAdults,
0 AS f_DiffExtraFLTRChildren,
0 AS f_DiffExtraFLTRInfants,
0 AS f_DiffExtraFLTRRevenue,
0 AS f_DiffExtraFLTRCost,
0 AS f_DiffExtraINSRPax,
0 AS f_DiffExtraINSRQuantity,
0 AS f_DiffExtraINSRAdults,
0 AS f_DiffExtraINSRChildren,
0 AS f_DiffExtraINSRInfants,
0 AS f_DiffExtraINSRRevenue,
0 AS f_DiffExtraINSRCost,
0 AS f_DiffExtraOTHRPax,
0 AS f_DiffExtraOTHRQuantity,
0 AS f_DiffExtraOTHRAdults,
0 AS f_DiffExtraOTHRChildren,
0 AS f_DiffExtraOTHRInfants,
0 AS f_DiffExtraOTHRRevenue,
0 AS f_DiffExtraOTHRCost,
0 AS f_DiffExtraSKIPPax,
0 AS f_DiffExtraSKIPQuantity,
0 AS f_DiffExtraSKIPAdults,
0 AS f_DiffExtraSKIPChildren,
0 AS f_DiffExtraSKIPInfants,
0 AS f_DiffExtraSKIPRevenue,
0 AS f_DiffExtraSKIPCost,
0 AS f_DiffExtraSKITPax,
0 AS f_DiffExtraSKITQuantity,
0 AS f_DiffExtraSKITAdults,
0 AS f_DiffExtraSKITChildren,
0 AS f_DiffExtraSKITInfants,
0 AS f_DiffExtraSKITRevenue,
0 AS f_DiffExtraSKITCost,
0 AS f_DiffExtraSPIOPax,
0 AS f_DiffExtraSPIOQuantity,
0 AS f_DiffExtraSPIOAdults,
0 AS f_DiffExtraSPIOChildren,
0 AS f_DiffExtraSPIOInfants,
0 AS f_DiffExtraSPIORevenue,
0 AS f_DiffExtraSPIOCost,
0 AS f_DiffExtraSPORPax,
0 AS f_DiffExtraSPORQuantity,
0 AS f_DiffExtraSPORAdults,
0 AS f_DiffExtraSPORChildren,
0 AS f_DiffExtraSPORInfants,
0 AS f_DiffExtraSPORRevenue,
0 AS f_DiffExtraSPORCost,
0 AS f_DiffExtraSTNDPax,
0 AS f_DiffExtraSTNDQuantity,
0 AS f_DiffExtraSTNDAdults,
0 AS f_DiffExtraSTNDChildren,
0 AS f_DiffExtraSTNDInfants,
0 AS f_DiffExtraSTNDRevenue,
0 AS f_DiffExtraSTNDCost,
0 AS f_DiffExtraTRAFPax,
0 AS f_DiffExtraTRAFQuantity,
0 AS f_DiffExtraTRAFAdults,
0 AS f_DiffExtraTRAFChildren,
0 AS f_DiffExtraTRAFInfants,
0 AS f_DiffExtraTRAFRevenue,
0 AS f_DiffExtraTRAFCost,
0 AS f_DiffExtraXXXXPax,
0 AS f_DiffExtraXXXXQuantity,
0 AS f_DiffExtraXXXXAdults,
0 AS f_DiffExtraXXXXChildren,
0 AS f_DiffExtraXXXXInfants,
0 AS f_DiffExtraXXXXRevenue,
0 AS f_DiffExtraXXXXCost
FROM
AMI_Stage.dbo.ST_PIT_BOOKINGHEADERS ST_PIT_BOOKINGHEADERS
LEFT JOIN
AMI_GALAXY.dbo.CONF_DT_BOOKING CONF_DT_BOOKING
ON
CONF_DT_BOOKING.i_SK_Bkg = ST_PIT_BOOKINGHEADERS.I_SK_BKG
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_XXXX
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_XXXX.i_BK_ExtraType = 'XXXX'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_TRAF
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_TRAF.i_BK_ExtraType = 'TRAF'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_STND
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_STND.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_STND.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_STND.i_BK_ExtraType = 'STND'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPOR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SPOR.i_BK_ExtraType = 'SPOR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPIO
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SPIO.i_BK_ExtraType = 'SPIO'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIT
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SKIT.i_BK_ExtraType = 'SKIT'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIP
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SKIP.i_BK_ExtraType = 'SKIP'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_OTHR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_OTHR.i_BK_ExtraType = 'OTHR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_INSR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_INSR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_INSR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_INSR.i_BK_ExtraType = 'INSR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_FLTR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_FLTR.i_BK_ExtraType = 'FLTR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EXCU
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_EXCU.i_BK_ExtraType = 'EXCU'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EQUI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_EQUI.i_BK_ExtraType = 'EQUI'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CREC
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CREC.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CREC.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CREC.i_BK_ExtraType = 'CREC'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CLUB
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CLUB.i_BK_ExtraType = 'CLUB'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CHLD
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CHLD.i_BK_ExtraType = 'CHLD'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CARI.i_BK_ExtraType = 'CARI'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARH
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARH.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARH.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CARH.i_BK_ExtraType = 'CARH'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_ACTI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_ACTI.i_BK_ExtraType = 'ACTI'
LEFT JOIN
AMI_Stage.dbo.ST_V_AC_ACCOMCOSTS ST_V_AC_ACCOMCOSTS
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_AC_ACCOMCOSTS.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_AC_ACCOMCOSTS.i_BK_Version
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS.i_BK_Version
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_AIR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_AIR.i_BK_TransportType = 'AIR'
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_FER
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_FER.i_BK_TransportType = 'FER'
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_TRN
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_TRN.i_BK_TransportType = 'TRN'
