select a.week_date,a.aff_id,a.aff_name, a.booker_cc1, a.tns, a.gross_rn, round(a.gross_ttv,0) as gross_ttv, a.is_global from (
select
subdate(date_format(r.created, "%Y-%m-%d"), weekday(r.created)) as week_date,
r.affiliate_id as aff_id, a.name as aff_name, r.booker_cc1 as booker_cc1,
count(distinct r.id) as tns,
sum(roomnights) as gross_rn, sum(r.price_euro) as gross_ttv,
if(count(distinct r.id) > 0,'gb','') as is_global
 from Reservation r
 left join B_Affiliate a on a.id=r.affiliate_id
where r.affiliate_id in (324340,
323128,
323127,
323126,
323125,
323124,
323592,
323593,
375450,
846004,
394788,
394271,
394789,
394787,
396670,
396671,
1373045,
396674,
399948,
399949,
801059,
801382,
801386,
801387,
801391,
1137797,
801392,
801393,
801394,
801395,
801396,
1559275,
801399,
801404,
801406,
808432,
808433,
808434,
808435,
1631702,
808437,
811130,
1139656,
1128032,
1139660,
1139661,
1139664,
1139666,
1139669,
1139672,
1139675,
1139676,
1139678,
1139682,
1139686,
1139689,
1139696,
1139697,
1139698,
1139699,
1139701,
1139703,
1139706,
1139714,
1139716,
1194862,
1346052,
1194867,
1224988,
1224987,
1231116,
1231130,
1267312,
1267313,
1267315,
1267316,
1267317,
1267318,
1267319,
1267320,
1267321,
1267323,
1267326,
1267330,
1267333,
1291764,
1369234
)
and r.created BETWEEN '2019-01-01' and '2019-12-16'
group by 1,2,4 ) a
union select b.week_date,b.aff_id,b.aff_name, b.booker_cc1, b.tns, b.gross_rn, b.gross_ttv, '' as is_global from (select
subdate(date_format(r.created, "%Y-%m-%d"), weekday(r.created)) as week_date,
r.affiliate_id as aff_id, a.name as aff_name, r.booker_cc1 as booker_cc1,
count(distinct r.id) * -1 as tns,
sum(roomnights) * -1 as gross_rn, sum(r.price_euro) * -1 as gross_ttv
 from Reservation r
 left join B_Affiliate a on a.id=r.affiliate_id
where r.affiliate_id in (324340,
323128,
323127,
323126,
323125,
323124,
323592,
323593,
375450,
846004,
394788,
394271,
394789,
394787,
396670,
396671,
1373045,
396674,
399948,
399949,
801059,
801382,
801386,
801387,
801391,
1137797,
801392,
801393,
801394,
801395,
801396,
1559275,
801399,
801404,
801406,
808432,
808433,
808434,
808435,
1631702,
808437,
811130,
1139656,
1128032,
1139660,
1139661,
1139664,
1139666,
1139669,
1139672,
1139675,
1139676,
1139678,
1139682,
1139686,
1139689,
1139696,
1139697,
1139698,
1139699,
1139701,
1139703,
1139706,
1139714,
1139716,
1194862,
1346052,
1194867,
1224988,
1224987,
1231116,
1231130,
1267312,
1267313,
1267315,
1267316,
1267317,
1267318,
1267319,
1267320,
1267321,
1267323,
1267326,
1267330,
1267333,
1291764,
1369234
)
and r.status in ('cancelled_by_guest', 'cancelled_by_hotel')
and r.created BETWEEN '2019-01-01' and '2019-12-16'
group by 1,2,4 ) b