AEPD - Final user_consent timestamp query

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

select * from (
select
    r.id,
    cu.id as user_id,
    r.created_at as tiempo_utc,
    r.minutes,
    r.distance/1000 as distancia_km,
    r.cost[offset(0)].amount as coste,
    r.path[safe_offset(0)].latitude as latitude_initial,
    r.path[safe_offset(0)].longitude as longitude_initial,
    r.path[safe_offset(array_length(path)-1)].latitude as latitude_final,
    r.path[safe_offset(array_length(path)-1)].longitude as longitude_final,
    r.starting_battery_level,
    r.ending_battery_level,
    co.name as vehicle_company,
    co2.name as app_company
from `reby-cloud.analytics_reby_v1_eu.pg_rides_json` r
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on r.vehicle_company_id = co.id
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2 on r.company_id = co2.id
left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on r.company_id = cu.company_id and r.user_id = cu.user_id
join `reby-cloud.temp_eu.200_users_aepd_avant` aepd on aepd.from_co_cu_id = cu.id
--order by r.created_at asc
) where vehicle_company = 'Avant'

select
    uc.from_co_cu_id as user_id,
    uc.consent_timestamp,
    uc.consent_from
from `reby-cloud.misc_eu.user_consent` uc
join `reby-cloud.temp_eu.200_users_aepd_avant` aepd on aepd.from_co_cu_id = uc.from_co_cu_id

SELECT 
    cu.id as user_id,
    if(date_diff(current_date,date(last_location_at),DAY) > 190,null,longitude) longitude,
    if(date_diff(current_date,date(last_location_at),DAY) > 190,null,latitude) latitude,
    last_location_at
FROM `reby-cloud.alvaro_misc.pg_user_last_location` ull
left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on cu.company_id = ull.company_id and cu.user_id = ull.user_id
join `reby-cloud.temp_eu.200_users_aepd_avant` aepd on aepd.from_co_cu_id = cu.id
;

DECLARE fecha_migracion TIMESTAMP;
SET fecha_migracion = '2021-06-16 00:52:53.131944 UTC';

drop table if exists misc_eu.user_consent;
create or replace table `reby-cloud.misc_eu.user_consent` as 
with 
first_user_session as (
    SELECT
    us.*,
    cu.id as cu_id,
    co.name as app_company_name,
    co.id as company_id,
    row_number() over(partition by cu.id order by us.created_at asc) as rank_
FROM `reby-cloud.analytics_reby_v1_eu.pg_user_session` us
LEFT JOIN `reby-cloud.reby_marketing_eu.pg_company_user` cu
    on us.company_id = cu.company_id and us.user_id = cu.user_id
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
    on cu.company_id = co.id
QUALIFY rank_ = 1
),
--t1 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t1 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
--t2 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t2 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
all_users as (select id from `reby-cloud.analytics_reby_v1_eu.pg_users_json`),

company_users as (
  select
    cu.id,
    cu.user_id,
    co.name as from_company,
    cu.created_at,
    cu.company_id,
    cu.is_deleted,
    consent.consent_from_reby
  from `reby-cloud.reby_marketing_eu.pg_company_user` cu
  left join `reby-cloud.analytics_reby_v1_eu.pg_consent` consent
      on cu.id = consent.company_user_id
  left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
      on cu.company_id = co.id
),
cross_joins as (
SELECT t1.franquicias as from_co_id, t2.franquicias as to_co_id, all_users.id as user_id
FROM t1 CROSS JOIN t2 CROSS JOIN all_users group by 1,2,3),

cross_cus as (
select
  from_co_id,
  to_co_id,
  cu1.consent_from_reby,
  cj.user_id,
  cu1.id as from_co_cu_id,
  cu1.created_at as from_co_cu_id_created,
  cu2.id as to_co_cu_id,
  cu2.created_at as to_co_cu_id_created,
from cross_joins cj
left join company_users cu1 on cj.from_co_id = cu1.company_id and cj.user_id = cu1.user_id
left join company_users cu2 on cj.to_co_id = cu2.company_id and cj.user_id = cu2.user_id
),

filtered_users as (
select *
from cross_cus
where to_co_id = 'c_3qteetfhxjshx4j54111'
  and from_co_cu_id is not null
  and from_co_id != 'c_3qteetfhxjshx4j54111'
  and to_co_cu_id is not null
),

join_first_ride_reserve_session as (
select
  fu.*,
  frr.created_at as ts_frr,
  fs1.created_at as first_session_from_co,
--  fs2.created_at as first_session_to_co,
  if(from_co_cu_id_created <= fecha_migracion,'pre-migracion','post-migracion') as pre_post
from filtered_users fu
left join temp_eu.delete_first_ride_reserve frr 
  on frr.vehicle_company_id = fu.from_co_id and frr.app_co_id = fu.to_co_id and fu.user_id = frr.user_id
left join first_user_session fs1 on fs1.cu_id = fu.from_co_cu_id 
--left join first_user_session fs2 on fs2.cu_id = fu.to_co_cu_id
),

consent_ts as (
select
  jfrrs.*,
  case
    when pre_post = 'pre-migracion' then from_co_cu_id_created
    when pre_post = 'post-migracion' and first_session_from_co is null  and consent_from_reby is true then from_co_cu_id_created
    when pre_post = 'post-migracion' and first_session_from_co is not null and ts_frr is not null then ts_frr
    when pre_post = 'post-migracion' and first_session_from_co is not null and consent_from_reby is true and ts_frr is null then timestamp_add(greatest(to_co_cu_id_created,from_co_cu_id_created),INTERVAL 3 HOUR)
    else null
  end as consent_timestamp,
  row_number() over (partition by from_co_id,to_co_id,consent_from_reby,user_id) as rn_
from join_first_ride_reserve_session jfrrs
QUALIFY rn_ = 1
)

select
  *,
  case
    when consent_timestamp is not null and pre_post = 'pre-migracion' and abs(timestamp_diff(from_co_cu_id_created,first_session_from_co,HOUR))<3 then 'consent-from-co-app'
    when consent_timestamp is not null and pre_post = 'pre-migracion' then 'consent-from-reby-app'
    when consent_timestamp is not null and pre_post = 'post-migracion' then 'consent-from-reby-app'
  else 'other'
  end as consent_from
from consent_ts

--GET THE FIRST VEHICLE RESERVATION PER USER/APP/VEHICLE
create or replace table temp_eu.delete_first_ride_reserve as 

with first_reservations as (
    select
        vrh.created_at,
        vrh.user_id,
        vrh.company_vehicle_id as vehicle_company_id,
        vrh.company_id as app_co_id,
        co1.name as vehicle_company_name,
        co2.name as app_company_name,
        row_number() over (partition by vrh.user_id,vrh.company_vehicle_id,vrh.company_id order by vrh.created_at asc) as r_n
    from `reby-cloud.analytics_reby_v1_eu.pg_vehicle_reservation_history` vrh
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co1
    on vrh.company_vehicle_id = co1.id
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2
    on vrh.company_vehicle_id = co2.id
    QUALIFY r_n = 1
),
--FINISH

--GET THE FIRST RIDE PER USER/APP/VEHICLE
rides as (
    select 
        id,
        created_at,
        user_id,
        if(vehicle_company_id is null,'c_3qteetfhxjshx4j54111',vehicle_company_id) as vehicle_company_id,
        if(company_id is null,'c_3qteetfhxjshx4j54111',company_id) as company_id
    from analytics_reby_v1_eu.pg_rides_json
),

first_ride_pre as (
    select
        r.id,
        r.user_id,
        r.created_at as ride_date,
        r.vehicle_company_id,
        r.company_id as app_co_id,
        co.name as vehicle_company_name,
        co2.name as app_company_name,
        cu.id as cu_id_vehicle,
        cu2.id as cu_id_app,
        --row_number() over (partition by r.user_id,r.vehicle_company_id order by r.created_at asc) as rank_
    from rides r
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on r.vehicle_company_id = co.id
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2 on r.company_id = co2.id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on r.user_id = cu.user_id and r.vehicle_company_id = cu.company_id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu2 on r.user_id = cu2.user_id and r.company_id = cu2.company_id
),

first_ride_per_app_vehicle as (
select *, row_number() over (partition by user_id, app_company_name, vehicle_company_name order by ride_date asc) as rank_ from first_ride_pre qualify rank_ = 1
),
--FINISH

--GET FIRST DATE BETWEEN FIRST RIDE AND/OR FIRST RESERVATION
first_date_ride_reserv as (
    select
        user_id,
        vehicle_company_name,
        app_company_name,
        created_at,
        vehicle_company_id,
        app_co_id,
        row_number() over (partition by user_id, app_company_name, vehicle_company_name order by created_at asc) as rank_
        from(
            --UNION BETWEEN RIDES AND RESERVATIONS
            select
                user_id,
                vehicle_company_name,
                app_company_name,
                created_at,
                vehicle_company_id,
                app_co_id
            from first_reservations 
            UNION ALL
            select
                user_id,
                vehicle_company_name,
                app_company_name,
                ride_date as created_at,
                vehicle_company_id,
                app_co_id
            from first_ride_per_app_vehicle
        ) QUALIFY rank_=1
)

select * from first_date_ride_reserv
--FINISH
;

DECLARE fecha_migracion TIMESTAMP;
SET fecha_migracion = '2021-06-16 00:52:53.131944 UTC';

drop table if exists misc_eu.user_consent;
create or replace table `reby-cloud.misc_eu.user_consent` as 
with 
first_user_session as (
    SELECT
    us.*,
    cu.id as cu_id,
    co.name as app_company_name,
    co.id as company_id,
    row_number() over(partition by cu.id order by us.created_at asc) as rank_
FROM `reby-cloud.analytics_reby_v1_eu.pg_user_session` us
LEFT JOIN `reby-cloud.reby_marketing_eu.pg_company_user` cu
    on us.company_id = cu.company_id and us.user_id = cu.user_id
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
    on cu.company_id = co.id
QUALIFY rank_ = 1
),
--t1 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t1 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
--t2 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t2 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
all_users as (select id from `reby-cloud.analytics_reby_v1_eu.pg_users_json`),

company_users as (
  select
    cu.id,
    cu.user_id,
    co.name as from_company,
    cu.created_at,
    cu.company_id,
    cu.is_deleted,
    consent.consent_from_reby
  from `reby-cloud.reby_marketing_eu.pg_company_user` cu
  left join `reby-cloud.analytics_reby_v1_eu.pg_consent` consent
      on cu.id = consent.company_user_id
  left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
      on cu.company_id = co.id
),
cross_joins as (
SELECT t1.franquicias as from_co_id, t2.franquicias as to_co_id, all_users.id as user_id
FROM t1 CROSS JOIN t2 CROSS JOIN all_users group by 1,2,3),

cross_cus as (
select
  from_co_id,
  to_co_id,
  cu1.consent_from_reby,
  cj.user_id,
  cu1.id as from_co_cu_id,
  cu1.created_at as from_co_cu_id_created,
  cu2.id as to_co_cu_id,
  cu2.created_at as to_co_cu_id_created,
from cross_joins cj
left join company_users cu1 on cj.from_co_id = cu1.company_id and cj.user_id = cu1.user_id
left join company_users cu2 on cj.to_co_id = cu2.company_id and cj.user_id = cu2.user_id
),

filtered_users as (
select *
from cross_cus
where to_co_id = 'c_3qteetfhxjshx4j54111'
  and from_co_cu_id is not null
  and from_co_id != 'c_3qteetfhxjshx4j54111'
  and to_co_cu_id is not null
),

join_first_ride_reserve_session as (
select
  fu.*,
  frr.created_at as ts_frr,
  fs1.created_at as first_session_from_co,
--  fs2.created_at as first_session_to_co,
  if(from_co_cu_id_created <= fecha_migracion,'pre-migracion','post-migracion') as pre_post
from filtered_users fu
left join temp_eu.delete_first_ride_reserve frr 
  on frr.vehicle_company_id = fu.from_co_id and frr.app_co_id = fu.to_co_id and fu.user_id = frr.user_id
left join first_user_session fs1 on fs1.cu_id = fu.from_co_cu_id 
--left join first_user_session fs2 on fs2.cu_id = fu.to_co_cu_id
),

consent_ts as (
select
  jfrrs.*,
  case
    when pre_post = 'pre-migracion' then from_co_cu_id_created
    when pre_post = 'post-migracion' and first_session_from_co is null  and consent_from_reby is true then from_co_cu_id_created
    when pre_post = 'post-migracion' and first_session_from_co is not null and ts_frr is not null then ts_frr
    when pre_post = 'post-migracion' and first_session_from_co is not null and consent_from_reby is true and ts_frr is null then timestamp_add(greatest(to_co_cu_id_created,from_co_cu_id_created),INTERVAL 3 HOUR)
    else null
  end as consent_timestamp,
  row_number() over (partition by from_co_id,to_co_id,consent_from_reby,user_id) as rn_
from join_first_ride_reserve_session jfrrs
QUALIFY rn_ = 1
)

select
  *,
  case
    when consent_timestamp is not null and pre_post = 'pre-migracion' and abs(timestamp_diff(from_co_cu_id_created,first_session_from_co,HOUR))<3 then 'consent-from-co-app'
    when consent_timestamp is not null and pre_post = 'pre-migracion' then 'consent-from-reby-app'
    when consent_timestamp is not null and pre_post = 'post-migracion' then 'consent-from-reby-app'
  else 'other'
  end as consent_from
from consent_ts

SELECT Query, UserId, SUM(TotalCost) FROM `reby-cloud.bq_logs.bigquery_usage_datepart` 
WHERE DATE(Date) >= "2022-03-01" and DATE(Date) <= "2022-03-30" 
GROUP BY 1,2
ORDER BY 3 DESC

dim tempo = listbox1.SelectedItems
dim students as string = ""
for each selected in tempo
students = selected.ToString & "," & students
next

Use Sum aggregate function with case statement as below, if you want sum of those t.value based on t.clock then do group by on that column so that you will get clock while sum of values.

select sum(case when  i.hostid='223344'   and t.itemid = '0223344' then t.value end) as FirstValue,sum(case when  i.hostid='112233' and t.itemid = '0112233' then t.value end) as SecondValue
from hosts h, items i,history_uint t
where i.hostid=h.hostid and t.itemid=i.itemid
If it is based on t.clock then

   select t.clock, sum(case when  i.hostid='223344'   and t.itemid = '0223344' then t.value end) as FirstValue,sum(case when  i.hostid='112233' and t.itemid = '0112233' then t.value end) as SecondValue
    from hosts h, items i,history_uint t
    where i.hostid=h.hostid and t.itemid=i.itemid
    group by t.clock
    0

You can try this below logic-

SELECT i.hostid,t.itemid,t.value as OneValues, t.clock as time
FROM hosts h 
INNER JOIN items i 
    ON i.hostid=h.hostid
    AND i.hostid IN ('223344','112233')
    AND i.itemid IN ('0223344','0112233')
INNER JOIN  history_uint t 
    ON t.itemid=i.itemid

ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);

select 
				   a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 day'::interval, 'DD-MM-YYYY')  jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
				   a.plafond_or,
				   a.penjaminan nilai_penjaminan,
				   c.is_konvensional,
				   0 nilai_asuransi_1,
					0 nilai_asuransi_2,
				  (a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
				 CASE
				        when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
				 END umur,
				  '--' note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							0 komisi_asuransi
							--(ijp cogar - feebase cogar) x persen komisi

						

                from 
                  v_tr_transaksi_suretyship a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_obligee f on a.id_obligee = f.id_obligee
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	 
	            where 
				--( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND
				--d.id_reference is null and
				--a.flag_deletion is null and
	              -- d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				  a.flag_deletion is null and 		
					d.flag_delete is null and 
					( 
						( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
							( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
						) OR  
						
						( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
					)  
					
	          
				 union all
				 select 
					a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_bank_cabang as nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
					a.plafond_or,
					a.penjaminan nilai_penjaminan,
					c.is_konvensional, 
					0 nilai_asuransi_1,
					0 nilai_asuransi_2,
					(a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
					case
					    when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
					end    
					 umur,
				    '--' note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							0 komisi_asuransi
                from 
                  v_tr_transaksi_suretyship a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	 
	            where
				--	( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND				
				--d.id_reference is null and
				--	a.flag_deletion is null and
	             --  d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				 a.flag_deletion is null and 		
				d.flag_delete is null and 
				( 
					( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
						( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
					) OR  
					
					( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
				) and 
				c.id_mt_suretyship != 9
	              
	                union all
				 select 
					a.id_transaksi_suretyship,
                   b.jenis_suretyship,
                   c.suretyship,
                   d.nomor_sk,
                   d.tgl_sk,
				   d.tgl_sk_cetak,
                   e.nama_principal,
                   f.nama_bank_cabang as nama_obligee,
				   a.flag_deletion,
				   a.id_reference,
                   a.nilai_proyek,
                   a.nilai_bond,
                   a.nilai_ijp,
				   a.fee_base persen_fee_base,
				   a.fee_base * a.nilai_ijp/100 fee_base,
				   a.komisi_agen persen_komisi_agen,
				   a.komisi_agen*a.nilai_ijp/100 komisi_agen,
				   a.persen_reasuransi ,
				   --a.persen_reasuransi * a.nilai_ijp/100 reasuransi,
				   a.reasuransi,
				   --coalesce(a.fee_base/100,0)*a.persen_reasuransi * a.nilai_ijp/100 reasuransi_fee_base,
				   a.reasuransi_fee_base,
				   a.persen_coguarantee,
				   a.persen_coguarantee * a.nilai_ijp/100 coguarantee,
				   a.potongan_co persen_potongan_co,
				   a.potongan_co * a.nilai_ijp/100 potongan_co,
				   a.potongan_re persen_potongan_re,
				   a.potongan_re * a.nilai_ijp/100 potongan_re,
				   to_char(a.periode_berlaku + (a.jangka_waktu)* '1 MONTH'::interval, 'DD-MM-YYYY') jatuh_tempo,
				   to_char(a.periode_berlaku, 'DD-MM-YYYY') periode_berlaku,
				   coalesce(e.jumlah_karyawan,0) tenaga_kerja,
				   --a.nilai_bond * (100-coalesce(a.persen_reasuransi,0))/100 as plafond_or
					a.plafond_or,
					a.penjaminan nilai_penjaminan,
					c.is_konvensional, 
					nilai_asuransi_1,
					nilai_asuransi_2,
					(a.fee_base*(a.persen_coguarantee*a.nilai_ijp/100)/100)  coguarantee_fee_base,
					case
					    when cast(usia AS varchar) IS NULL then cast(tanggal_usia AS varchar)
					    else cast(usia AS varchar)
					end    
					 umur,
				    a.catatan note,
				  case
				  	when d.tgl_sk >= '2016-12-01' and d.tgl_sk <= '2017-11-30' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*5/100
				  	when d.tgl_sk >= '2017-12-01' and d.tgl_sk <= '2019-08-31' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*7.5/100
				  	when d.tgl_sk > '2019-09-01' then 
				  	(coalesce(a.reasuransi,0)-(coalesce(a.reasuransi_fee_base,0)))*10/100
				  	else 0
				  	end ri_com,
						CASE
							when  d.tgl_sk >= '2019-09-30' and d.tgl_sk <= '2020-04-30' then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*5/100
							when  d.tgl_sk >= '2020-05-01'  then
							((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)-((COALESCE(a.persen_coguarantee,0)* a.nilai_ijp/100)*(COALESCE(a.fee_base,0)/100)))*7.5/100
							ELSE 0
							end komisi_co,
							nilai_asuransi_2 * 5/100 komisi_asuransi
                from 
                  v_tr_transaksi_suretyship_asuransi a
                  inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
                  inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
                  inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
                  inner join mt_principal e on a.id_principal = e.id_principal
                  inner join mt_bank_cabang f on d.id_mt_bank_cabang = f.id_mt_bank_cabang
				  Left join mt_agen g on a.id_mt_agen=g.id_mt_agen
				  left join mt_broker h on d.reas_id_broker = h.id_broker
    	          left join (
                    SELECT id_transaksi_suretyship, 
                            sum(nilai_asuransi_1)  nilai_asuransi_1, 
                            sum(nilai_asuransi_2)  nilai_asuransi_2 FROM (
                                SELECT
                                    id_transaksi_suretyship,
                                        CASE 
                                                    WHEN  
                                                        id_asuransi = 1
                                                    THEN
                                                        nilai_asuransi
                                                    ELSE
                                                        0
                                                END AS nilai_asuransi_1,
                                        CASE 
                                                    WHEN  
                                                        id_asuransi = 2
                                                    THEN
                                                        nilai_asuransi
                                                    ELSE
                                                        0
                                                END AS nilai_asuransi_2 
                                FROM
                                tr_transaksi_suretyship_ext	 
                            ) ss GROUP BY id_transaksi_suretyship
                ) i ON a.id_transaksi_suretyship = i.id_transaksi_suretyship
	            where
				--	( a.flag_deletion is null OR (a.flag_deletion is not null and a.change_date > '2020-07-31') ) AND				
				--d.id_reference is null and
				--	a.flag_deletion is null and
	             --  d.tgl_sk >='2015-01-01'  and d.tgl_sk<='2020-07-31'
				 a.flag_deletion is null and 		
				d.flag_delete is null and 
				( 
					( tgl_sk >='2015-01-01' and  tgl_sk <='2020-07-31'  and 
						( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
					) OR  
					
					( tgl_sk_cetak >='2015-01-01' and  tgl_sk_cetak <='2020-07-31' and d.id_reference is not null)
				) and 
				c.id_mt_suretyship = 9