83 lines
2.7 KiB
SQL
83 lines
2.7 KiB
SQL
{{ config(materialized="table") }}
|
|
|
|
{% set guesty_id_deal = "17814677813" %}
|
|
with
|
|
int_edeposit__verification_fees as (
|
|
select * from {{ ref("int_edeposit__verification_fees") }}
|
|
),
|
|
int_athena__verifications_with_fees as (
|
|
select * from {{ ref("int_athena__verifications_with_fees") }}
|
|
),
|
|
int_check_in_hero__checkins as (
|
|
select * from {{ ref("int_check_in_hero__checkins") }}
|
|
),
|
|
int_screen_and_protect__verification_fees as (
|
|
select * from {{ ref("int_screen_and_protect__verification_fees") }}
|
|
)
|
|
select
|
|
'E-DEPOSIT' as api_source,
|
|
id_verification,
|
|
id_booking,
|
|
id_deal,
|
|
verification_status,
|
|
checkin_date_utc as check_in_date_utc,
|
|
checkout_date_utc as check_out_date_utc,
|
|
created_date_utc,
|
|
checkout_date_utc as billable_date_utc,
|
|
is_cancelled,
|
|
case
|
|
when count(1) over (partition by id_booking) > 1 then true else false
|
|
end as is_duplicate_booking,
|
|
count(1) over (partition by id_booking) as booking_is_duplicated_n_times
|
|
from int_edeposit__verification_fees
|
|
union all
|
|
select
|
|
'ATHENA' as api_source,
|
|
id_verification,
|
|
id_booking,
|
|
'{{guesty_id_deal}}' as id_deal,
|
|
verification_status,
|
|
checkin_date_utc as check_in_date_utc,
|
|
checkout_date_utc as check_out_date_utc,
|
|
created_date_utc,
|
|
checkout_date_utc as billable_date_utc,
|
|
is_cancelled,
|
|
case
|
|
when count(1) over (partition by id_booking) > 1 then true else false
|
|
end as is_duplicate_booking,
|
|
count(1) over (partition by id_booking) as booking_is_duplicated_n_times
|
|
from int_athena__verifications_with_fees
|
|
union all
|
|
select
|
|
'CHECK_IN_HERO' as api_source,
|
|
id_record as id_verification,
|
|
id_reservation as id_booking,
|
|
id_deal,
|
|
null as verification_status,
|
|
checkin_date_utc as check_in_date_utc,
|
|
checkout_date_utc as check_out_date_utc,
|
|
created_date_utc,
|
|
created_date_utc as billable_date_utc,
|
|
null as is_cancelled,
|
|
case
|
|
when count(1) over (partition by id_reservation) > 1 then true else false
|
|
end as is_duplicate_booking,
|
|
count(1) over (partition by id_reservation) as booking_is_duplicated_n_times
|
|
from int_check_in_hero__checkins
|
|
union all
|
|
select
|
|
'SCREEN_AND_PROTECT' as api_source,
|
|
id_verification,
|
|
id_booking,
|
|
id_deal,
|
|
verification_status,
|
|
checkin_date_utc as check_in_date_utc,
|
|
checkout_date_utc as check_out_date_utc,
|
|
creation_date_utc as created_date_utc,
|
|
invoice_date_utc as billable_date_utc,
|
|
is_cancelled,
|
|
case
|
|
when count(1) over (partition by id_booking) > 1 then true else false
|
|
end as is_duplicate_booking,
|
|
count(1) over (partition by id_booking) as booking_is_duplicated_n_times
|
|
from int_screen_and_protect__verification_fees
|