Breaking News: Grepper is joining You.com. Read the official announcement!

sql 28 day increments

Trained Tuna answered on March 7, 2023 Popularity 1/10 Helpfulness 3/10

answer sql 28 day increments

sql 28 day increments

Comment

SQL to pull dates in 28 day increments

/*CTE Pulls yesterday and dates in 28 day increments.*/
WITH PERIOD AS  
( SELECT  CAL.CAL_DATE - 27 AS START_DATE
    , CAL.CAL_DATE AS END_DATE
    , ROW_NUMBER() OVER (ORDER BY CAL.CAL_DATE DESC) AS ROW_NUM
  FROM ( SELECT TRUNC(SYSDATE - 1) - (ROWNUM - 1) * 28 AS CAL_DATE
        FROM DUAL
        CONNECT BY ROWNUM <= 14 /* # of periods to pull */
        ) CAL
)
/* Join like the below if joining to a fact table. 
Join on YT.DATE = PERIOD.END_DATE if joining to a snapshot table */
SELECT PERIOD.ROW_NUM AS PERIOD_NUMBER
	, SUM(YOUR_METRIC) AS METRIC
FROM YOUR_TABLE YT
INNER JOIN PERIOD ON YT.DATE BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE
GROUP BY PERIOD.ROW_NUM
ORDER BY PERIOD.ROW_NUM

Popularity 1/10 Helpfulness 3/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Mar 07 2023

Trained Tuna

0 Answers Avg Quality 2/10

Continue with Google

By continuing, I agree that I have read and agree to Greppers's Terms of Service and Privacy Policy.

sql 28 day increments

Contents

More Related Answers

sql 28 day increments

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.