SELECT tq.enquiry_id as 'TQ ID',
tq.affiliate_id as 'Affiliate ID',
(case status_id
when 0 then 'pending'
when 1 then 'approved'
when 2 then 'declined' end) as 'TQ Status',
(case type_id
when 1 then 'declined'
when 2 then 'incorrect'
when 3 then 'untracked' end) as 'Enquiry Type',
tq.amount, tq.commission,
CONVERT_TZ(tq.date_sale, 'GMT', 'Europe/London') AS 'TQ Date',
 CONVERT_TZ(tq.date_sale, 'GMT', 'Europe/London') AS 'Transaction Date',
tq.order_ref as 'Order Ref',
(SELECT REPLACE(REPLACE(tq.description,CHAR(13), ' '), CHAR(10), ' ')) AS 'Description',
tq.duplicate_transaction_id,
at.extra as 'Original Order Ref',
at.affiliate_id as 'Original Affiliate',
 CONVERT_TZ(at.date, 'GMT', 'Europe/London') AS 'Original Tansaction Date',
 (CASE at.commission_status
        WHEN - 1 THEN 'Deleted'
        WHEN 1 THEN 'Confirmed'
        WHEN 2 THEN 'Declined'
        ELSE 'Pending'
    END) AS 'Original Status',
at.sale_amount as 'Original Sale Amount',
at.commission as 'Original Commission',
IFNULL(GROUP_CONCAT(CONCAT(cg.code, ':', tp.sale_amount) SEPARATOR '|'), 'N/A') AS 'parts',
at.com_date as 'Valiation Date'

 FROM transaction_query.transaction tq
left join  affiliatewindow.transaction  at on at.id=duplicate_transaction_id and at.merchant_id=4329
LEFT JOIN affiliatewindow.transaction_part tp ON tp.transaction_id = at.id
 LEFT JOIN affiliatewindow.commission_group cg ON cg.id = tp.cg_id
where tq.merchant_id = 4329
and tq.status_id=0
AND tq.date_sale BETWEEN CONVERT_TZ(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'), 'Europe/London', 'GMT') AND CONVERT_TZ(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59'), 'Europe/London', 'GMT')
group by tq.enquiry_id