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;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
