List:General Discussion« Previous MessageNext Message »
From:Cory Robin Date:May 29 2006 1:45pm
Subject:Re: ugly SQL for a report...
View as plain text  
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, 
pnr.created_by,
        GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last 
DESC SEPARATOR '<br>') names,

        (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp 
WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) 
base_fare,

        (SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE 
pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax ,

        (SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM 
pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf 
INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE 
psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF ,

        (SELECT SUM(amount_value) service_fees FROM pnr_service_fees 
WHERE ID_pnr=pnr.ID) services ,

        (SELECT SUM(amount) FROM pnr_payments_credits WHERE 
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments,

        (SELECT SUM(amount) FROM pnr_payments_credits WHERE 
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits

    FROM pnr INNER JOIN pnr_passengers pp
        ON pnr.ID=pp.ID_pnr

    WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
    AND pnr.reservationdatetime<='2006-05-29 23:59:59'

GROUP BY pnr.ID

ORDER BY reservationdatetime;

Thread
ugly SQL for a report...Cory29 May
  • Re: ugly SQL for a report...Jochem van Dieten29 May
  • Re: ugly SQL for a report...Cory Robin29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
      • Re: ugly SQL for a report...Cory Robin30 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Peter Brawley30 May