Have a fiscal quarter column, and I want to add a column that displays the previous quarter in the same row. I've done this successfully in tableau with logic like below.
IF RIGHT([current_qtr], 1) = "1"
THEN LEFT([current_qtr],2) STR(INT(MID([current_qtr], 3,2)) -1) "Q4"
ELSE LEFT([current_qtr],5) STR(INT(RIGHT([current_qtr],1)) -1)
END
Example of expected output in snowflake
current_qtr | previous_qtr | other columns |
---|---|---|
FY21Q1 | FY20Q4 | etc |
FY21Q2 | FY21Q1 | etc |
When I try to do this in snowflake though I keep getting this "Numeric value '' is not recognized error instead. Any ideas how to fix? Below is what I attempted.
SELECT *,
CASE
WHEN SUBSTR(p.current_qtr,5,1) = 1
THEN SUBSTR(p.current_qtr,1,2) || TRY_CAST(SUBSTR(p.current_qtr,3,2) as INTEGER)-1 || 'Q4'
ELSE SUBSTR(p.current_qtr,5,1) || TRY_CAST(SUBSTR(p.current_qtr,3,2) as INTEGER)-1
End As prev_qtr
FROM table as p
CodePudding user response:
This logic might look way better as a SQL UDF than as a big pile of strange SQL inside a query:
create or replace function prev_quarter_formatted(x string)
returns string
as
$$
(
select 'FY' || to_varchar(prev_q, '%y') || 'Q' || quarter(prev_q)
from (
select substr(x, 3,2) year
, (1 3*(substr(x, 6,1)::int-1)) q_to_month
, to_date(year||'-'||q_to_month||'-01', 'yy-mm-dd') as_date
, dateadd(month, -3, as_date) prev_q
)
)
$$
;
select prev_quarter_formatted('FY21Q1') a
;
Notice that for the logic I transformed the quarter into a date, subtracted 3 months, and formatted again.
CodePudding user response:
There are some subtle differences in the syntax for casting. You can use your current CASE statement with some minor adjustments:
SELECT *,
CASE
WHEN SUBSTR(p.current_qtr,6,1) = '1'
THEN SUBSTR(p.current_qtr,1,2) || ((SUBSTR(p.current_qtr,3,2))::int - 1) || 'Q4'
ELSE SUBSTR(p.current_qtr,1,2) || SUBSTR(p.current_qtr,3,2) || (SUBSTR(p.current_qtr,3,2)::int-1)
END AS prev_qtr
FROM table1 as p