如何创建一个计算字段来返回到FISCAL_QUARTER结束前的剩余天数？

Question

Current output with no DAYS_LEFT_IN_QUARTER我刚开始使用雪花，我的任务是创建一个日历维度表，以帮助报告每周/每月的/quarterly报告。我对如何返回FISCAL_QUARTER中剩余的天数感到困惑。Q1跨度从2月到4月。

下面附上的是我一直在写的代码，用来生成预测未来14年的日期。

--Set the start date and number of years to produce
SET START_DATE = '2012-01-01';
SET NUMBER_DAYS = (SELECT TRUNC(14 * 365));

--Set parameters to force ISO
ALTER SESSION SET WEEK_START = 1, WEEK_OF_YEAR_POLICY = 1;

  WITH CTE_MY_DATE AS (
    SELECT DATEADD(DAY, SEQ4(), $START_DATE) AS MY_DATE
    
      FROM TABLE(GENERATOR(ROWCOUNT=>$NUMBER_DAYS))  -- Number of days after reference date in previous line
  )
  SELECT 
        MY_DATE::date
        ,YEAR(MY_DATE) AS YEAR
        ,MONTH(MY_DATE) AS MONTH
        ,MONTHNAME(MY_DATE) AS MONTH_ABBREVIATION
        ,DAY(MY_DATE)
        ,DAYOFWEEK(MY_DATE)
        ,WEEKOFYEAR(MY_DATE)
        ,DAYOFYEAR(MY_DATE)
        ,YEAR(ADD_MONTHS(DATE_TRUNC('month', MY_DATE),11)) AS FISCAL_YEAR
        ,CONCAT('Q', QUARTER(ADD_MONTHS(DATE_TRUNC('month', MY_DATE),11))) AS FISCAL_QUARTER
        ,MONTH(ADD_MONTHS(DATE_TRUNC('month', MY_DATE),11)) AS FISCAL_MONTH
        
    
  FROM CTE_MY_DATE
;

Answer 1

首先，你的生成器会有间隙，因为SEQx()函数允许有间隙，所以你需要使用SEQx()作为ROW_NUMBER的替代，如下所示：

WITH cte_my_date AS (
    SELECT DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY SEQ4()), $START_DATE) AS my_date
    FROM TABLE(GENERATOR(ROWCOUNT=>$NUMBER_DAYS))  -- Number of days after reference date in previous line
)

和在季度中剩下的天数，是截断为季度的日期，+1季度，日期-天与天的差异：

,DATEDIFF('days', my_date, DATEADD('quarter', 1, DATE_TRUNC('quarter', my_date))) AS days_left_in_quarter

Answer 2

这个怎么样？您可以直接将代码复制/粘贴到snowflake中进行测试。

使用last_day()会让它看起来更整洁一些:-)

WITH CTE_MY_DATE AS (
SELECT DATEADD(DAY, SEQ4(), current_date()) AS MY_DATE
  FROM TABLE(GENERATOR(ROWCOUNT=>300)))
SELECT 
    MY_DATE::date
    ,YEAR(last_day(my_date,year)) AS FISCAL_YEAR
    ,concat('Q',quarter(my_date)) AS FISCAL_QUARTER
    ,datediff(d, my_date,  last_day(my_date,quarter))  AS 
DAYS_LEFT_IN_QUARTER
FROM CTE_MY_DATE

​

​