Generate dynamic table of all months between two dates-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Generate dynamic table of all months between two dates

P粉663883862 2023-08-28 12:44:46

491

I have a table that looks like this:

Name	INT_VALUE	Start	END
a	100	2013-11-16	2014-11-16

The goal is to output the following:

Name	INT_VALUE	Start	END	interval
a	100	2013-11-16	2014-11-16	11-2013
a	100	2013-11-16	2014-11-16	December 2013
a	100	2013-11-16	2014-11-16	1-2014
a	100	2013-11-16	2014-11-16	February 2014

I've started using this. But I'm not sure how to use the START and END dates in the table to determine the start and end of the list of months between calculations.

SELECT START, calendar, COUNT(1) FROM table1 AS t1 RIGHT JOIN (SELECT row_number() OVER (ORDER BY SEQ4()) AS MONTHS , TO_DATE(DATEADD(MONTH, MONTHS, '2019-05-01')) AS calendar FROM TABLE(GENERATOR(rowcount=>80))) ON t1.START = calendar GROUP BY 1, 2 ORDER BY 2, 1 ;

The goal here is to associate an int value with each month between the start and end of the range.

P粉663883862

reply all (2)

P粉9249157872023-08-29 11:21:21 2 floor

Moved thenumber rangeinto its own CTE to separate things, we now have a large list of 80 numbers (probably larger).

Then we find the number of months between start/end and concatenate the same number of rows. Then do the math to convert the range into a selection:

WITH range_of_numbers AS ( SELECT row_number() OVER (ORDER BY SEQ4())-1 AS rn FROM TABLE(GENERATOR(rowcount=>80)) ) SELECT t1.name, t1.int_value, t1.start, t1.end, DATEADD(MONTH, r.rn, t1.start) as interval FROM table1 AS t1 JOIN range_of_numbers as r ON date_diff('month', t1.START, t1.end) <= between r.rn ORDER BY 2,1,3;

Another option is to build a long range date table

CREATE TABLE dates AS SELECT DATEADD(MONTH, row_number() OVER (ORDER BY SEQ4())-1, '1980-01-01') as month_date FROM TABLE(GENERATOR(rowcount=>8000))

Then we useBETWEENto get the included values in the (start, end) range, which becomes:

FROM table1 AS t1 JOIN dates as d ON d.month_date BETWEEN t1.START AND t1.end

Like+0

Add Reply

P粉5671123912023-08-29 00:50:09 1 floor

WITH RECURSIVE cte AS ( SELECT name, int_value, start, `end`, 1 rownum, DATE_FORMAT(start, '%m-%Y') `interval` FROM source_table UNION ALL SELECT name, int_value, start, `end`, 1 + rownum, DATE_FORMAT(start + INTERVAL rownum MONTH, '%m-%Y') FROM cte WHERE start + INTERVAL rownum - 1 MONTH < `end` ) SELECT name, int_value, start, `end`, `interval` FROM cte ORDER BY rownum;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bdd028a7755fdcb8296df2301baeb295

If you don't want the month to have leading zeros, use the'%c-%Y'pattern.

Like+0

Add Reply