I am encountering an error like unexpected error while executing the below query in snowflake UI:
SELECT ((abc.*::date) +interval '1day' - interval '1sec')::date as week_ending_date
Thanks for your valuable suggestions.
Regards
Ratna Rao Yamani
CREATE OR REPLACE VIEW ADVANCE_DATA_V as (
with adv_data as (select (date_trunc('week',try_to_date(split_fees.paid_on::text))) + 5 as week_ending,
trim(providers.group_name) as group_name,
sum(advanced_amount)/100. as "Total Advanced Amt (All)",
count(distinct payment_id) as "Advance Count (All)",
count(distinct split_fees_cached.user_id) as "Distinct Users (All)",
(sum(advanced_amount) filter(where split_fees=0)/100.) as "Total Advanced Amt (No fee)",
count(distinct payment_id) filter(where split_fees=0) as "Advance Count (No fee)",
count(distinct split_fees_cached.user_id) filter(where split_fees=0) as "Distinct Users (No fee)",
(sum(advanced_amount) filter(where split_fees>0)/100.) as "Total Advanced Amt (fee)",
count(distinct payment_id) filter(where split_fees>0) as "Advance Count (fee)",
count(distinct split_fees_cached.user_id) filter(where split_fees>0) as "Distinct Users (fee)"
from split_fees_cached
join providers on providers.id = split_fees_cached.provider_id
where paid_on is not null and advanced_amount > 0 and split_fees_cached.status = 'paid' and advance_type in ('auto','pull')
and (providers.demo is null or providers.demo = false) --remove test providers
and providers.business_type = 'enterprise' and split_fees_cached.paid_on::date > '2018-01-01'
and (date_trunc('week', try_to_date(split_fees_cached.paid_on::text)))+ 5 < (date_trunc('week',try_to_date (current_date::text)))+5
group by week_ending,group_name),
min_dates as (select
trim(providers.group_name) as group_name,
min(date_trunc('week', try_to_date (providers.created_at at time zone 'utc' at time zone 'America/New_York':;text)))+5 as min_week_ending
from providers
where (providers.demo is null or providers.demo = false) --remove test providers
and providers.business_type = 'enterprise'
and (date_trunc('week', try_to_date (providers.created_at at time zone 'utc' at time zone 'America/New_York'::text))) + 5 < (date_trunc('week', try_to_date (current_date::text)))+5 group by group_name
),
**dates as (
SELECT (abc.*::date +interval '1day' - interval '1sec')::date as week_ending_date,
--to_char(abc.*,'IYYY-IW') as "year-week",**
to_char(abc.*,'IYYY') as "year",
concat('Year ',to_char(abc.*,'IYYY')) as "Year to join",
concat('Week ',to_char(abc.*,'IW')) as week,
to_char(abc.*,'IW') as week_num,
case when to_char(abc.*,'IW') in ('01','02','03','04') then 'January'
when to_char(abc.*,'IW') in ('05','06','07','08') then 'February'
when to_char(abc.*,'IW') in ('09','10','11','12','13') then 'March'
when to_char(abc.*,'IW') in ('14','15','16','17') then 'April'
when to_char(abc.*,'IW') in ('18','19','20','21') then 'May'
when to_char(abc.*,'IW') in ('22','23','24','25','26') then 'June'
when to_char(abc.*,'IW') in ('27','28','29','30') then 'July'
when to_char(abc.*,'IW') in ('31','32','33','34') then 'August'
when to_char(abc.*,'IW') in ('35','36','37','38','39') then 'September'
when to_char(abc.*,'IW') in ('40','41','42','43') then 'October'
when to_char(abc.*,'IW') in ('44','45','46','47') then 'November'
when to_char(abc.*,'IW') in ('48','49','50','51','52','53') then 'December'
end as "Month",
--------Justin Hagstrom addition for easier Tableau manipulation---------------
case when to_char(abc.*,'IW') in ('01','02','03','04') then 1
when to_char(abc.*,'IW') in ('05','06','07','08') then 2
when to_char(abc.*,'IW') in ('09','10','11','12','13') then 3
when to_char(abc.*,'IW') in ('14','15','16','17') then 4
when to_char(abc.*,'IW') in ('18','19','20','21') then 5
when to_char(abc.*,'IW') in ('22','23','24','25','26') then 6
when to_char(abc.*,'IW') in ('27','28','29','30') then 7
when to_char(abc.*,'IW') in ('31','32','33','34') then 8
when to_char(abc.*,'IW') in ('35','36','37','38','39') then 9
when to_char(abc.*,'IW') in ('40','41','42','43') then 10
when to_char(abc.*,'IW') in ('44','45','46','47') then 11
when to_char(abc.*,'IW') in ('48','49','50','51','52','53') then 12
end as month_digit,
------------------------------------------------------------------------------
case when to_char(abc.*,'IW') in ('01','02','03','04','05','06','07','08','09','10','11','12','13') then '1'
when to_char(abc.*,'IW') in ('14','15','16','17','18','19','20','21','22','23','24','25','26') then '2'
when to_char(abc.*,'IW') in ('27','28','29','30','31','32','33','34','35','36','37','38','39') then '3'
when to_char(abc.*,'IW') in ('40','41','42','43','44','45','46','47','48','49','50','51','52','53') then '4'
end as "Quarter"
select dateadd ('2017-04-29'::date, date_trunc ('week',try_to_date (select current_date::text))+5, interval '7day') abc
FROM dateadd (date, '2017-04-29', date_trunc (‘week’,try_to_date(select current_date::text))+5, interval ‘7day’) abc
--FROM generate_series(date '2017-04-29'
, date_trunc('week',try_to_date
(select current_date::text)+5
, interval '7 day') abc--
),
partner_dates as (
select m.*, d.*
from min_dates m
cross join dates d
where d.week_ending_date >= m.min_week_ending)
select d.week_ending_date as week_ending,
d.group_name,
a."Total Advanced Amt (All)",
a."Advance Count (All)",
a."Distinct Users (All)",
a."Total Advanced Amt (No fee)",
a."Advance Count (No fee)",
a."Distinct Users (No fee)",
a."Total Advanced Amt (fee)",
a."Advance Count (fee)",
a."Distinct Users (fee)",
d.year, d.week_num, z.max_week_ending, current_year, current_week
from partner_dates d
full outer join adv_data a on d.week_ending_date = a.week_ending and a.group_name = d.group_name
cross join (select max(a.week_ending::date) as max_week_ending from adv_data a) z
cross join (select max(Year) as current_year from dates where week_ending_date < date_trunc('week', timezone('America/New_York', current_timestamp)::date + interval '1day')::date + interval '5day') as cy --need to filter based on the current year and week_num
cross join (select
max(week_num) as current_week
from dates
where dates.year = (select max(Year) as current_year from dates where week_ending_date::date < (date_trunc('week', timezone('America/New_York', current_timestamp)::date + interval '1day')::date + interval '5day')::date)
and week_ending_date::date < (date_trunc('week', timezone('America/New_York', current_timestamp)::date + interval '1day')::date + interval '5day')::date
group by dates.year) as cw
