with DATE_BUCKET as (
select cal_date
FROM UNNEST(GENERATE_DATE_ARRAY(CAST(parse_date('%Y%m%d',@DS_START_DATE) as date), CAST(parse_date('%Y%m%d',@DS_END_DATE) as date), INTERVAL 1 DAY)) as cal_date
)
, RESERVATION_CHANGES as (
select *
from (
SELECT datetime(change_timestamp, ('PST8PDT')) change_timestamp_org,
date(date_trunc(datetime(change_timestamp, ('PST8PDT')), DAY)) as change_date,
datetime(IFNULL(LAG(a.change_timestamp) OVER (ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT')) AS end_change_timestamp_org,
date(datetime(IFNULL(LAG(a.change_timestamp) OVER (ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT'))) AS end_change_date,
project_id,
reservation_name,
ignore_idle_slots,
action,
slot_capacity,
autoscale.current_slots,
autoscale.max_slots,
user_email,
edition
FROM `ns-bill-data.region-asia-northeast3`.INFORMATION_SCHEMA.RESERVATION_CHANGES a
-- WHERE change_timestamp >= CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_START_DATE || ' 00:00:00', 'PST8PDT') AS TIMESTAMP)
)
where 1=1
and action NOT IN ('DELETE')
-- AND change_timestamp_org BETWEEN CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_START_DATE || ' 00:00:00', 'PST8PDT') AS datetime) AND CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_END_DATE || ' 23:59:59', 'PST8PDT') AS datetime)
)
, base2 as (
select cal_date
, case when cal_date > change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', cal_date || ' 00:00:00') AS datetime)
else change_timestamp_org
end as change_timestamp_new
, case when cal_date < end_change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', date_add(cal_date, interval 1 day) || ' 00:00:00') AS datetime)
else end_change_timestamp_org
end as end_change_timestamp_new
, base.project_id
, base.reservation_name
, base.ignore_idle_slots
, base.action
, base.slot_capacity
, base.current_slots
, base.max_slots
, base.user_email
, base.edition
from DATE_BUCKET
join RESERVATION_CHANGES
on base.change_date <> base.end_change_date
and cal_date >= base.change_date
and cal_date <= base.end_change_date
union all
select cal_date
, change_timestamp_org as change_timestamp_new
, end_change_timestamp_org as end_change_timestamp_new
, base.project_id
, base.reservation_name
, base.ignore_idle_slots
, base.action
, base.slot_capacity
, base.current_slots
, base.max_slots
, base.user_email
, base.edition
from DATE_BUCKET
join RESERVATION_CHANGES
on base.change_date = base.end_change_date
and cal_date = base.change_date
-- order by cal_date desc, change_timestamp_new desc
)
select change_timestamp_new
, end_change_timestamp_new
, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
, project_id
, reservation_name
, ignore_idle_slots
, action
, slot_capacity
, current_slots
, max_slots
, user_email
, edition
from base2
where 1=1
order by cal_date desc, change_timestamp_new desc
