BigQuery_Month to Date period-CodePudding

I have a problem to query data like this. I want to compare automatically period month to date; for example, I want to compare time range between 1/10 - current_date (28/10) & 1/9 - 28/9 & 1/8 - 28/8 and all month before. I have code:

WITH
cte_time AS (
SELECT Day
FROM UNNEST(GENERATE_DATE_ARRAY(DATE("2022-08-01"),CURRENT_DATE(" 7"), INTERVAL 1 DAY)) Day
)
,cte_timerange AS(
SELECT
"Monthly" TimeRange,
EXTRACT(Month FROM DAY) Period,
DATE_TRUNC(Day,month) MinDate,
DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 day), (day)), INTERVAL DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),(day)), DAY) DAY) as prior_period_end
group by 1,2,3,period
)
select * from cte_timerange

But the output is just 1 result (28/10) for each month. I am so stuck for this. Can anyone solve this similar problem before?

Thanks a lot for supporting me.

Here is the result that I want.

Thank you a lot.

CodePudding user response：

You might consider below.

SELECT 'Monthly' TimeRange,
       EXTRACT(MONTH FROM Month) Period,
       Month AS MinDate,
       DATE_ADD(Month, INTERVAL EXTRACT(DAY FROM CURRENT_DATE(' 7')) - 2 DAY) AS prior_period_end
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2022-08-01', CURRENT_DATE(' 7'), INTERVAL 1 MONTH)) Month;

Note that prior_period_end doesn't include today (29/10). If you want to include it, you can change - 2 DAY to - 1 DAY.

Query results