Get the start date and end date of the creation time-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)

Get the start date and end date of the creation time

P粉265724930 2024-02-21 18:25:26

371

Need help, I need to get start_date and end_date from creation_time based on is_active status. I tried several queries but didn't get the correct results.

Table example

id	user_id	Name	leader_name	is_active	Creation time
6	29	DF	AS	0	2021-10-10
620	29	DF	RB	0	2022-02-09
1088	29	DF	AS	1	2022-06-30

The result should look like this:

id	user_id	Name	leader_name	is_active	start date	End date	Creation time
6	29	DF	AS	0	2021-10-10	2022-02-09	2021-10-10
620	29	DF	RB	0	2022-02-09	2022-06-30	2022-02-09
1088	29	DF	AS	1	2022-06-30	CURRENT_DATE()	2022-06-30

Please help my friends, thank you in advance

P粉265724930

reply all (1)

P粉4959559862024-02-22 17:28:02 1 floor

Based on the information in the question section and the comments section, I believe the row with is_active=1 has the most recent creation time of the group (based on user_id). This is the query written and tested in the workbench.

select id,user_id,name,leader_name,is_active, t1.creation_time as start_date, case is_active when 0 then t2.creation_time else current_date() end as end_date,t1.creation_time from (select id,user_id,name,leader_name,is_active,creation_time,@row_id:=@row_id+1 as row_id from test,(select @row_id:=0)t where user_id=29 order by creation_time )t1 left join (select creation_time,@row_num:=@row_num+1 as row_num from test,(select @row_num:=0)t where user_id=29 order by creation_time )t2 on t1.row_id+1=t2.row_num ; -- result set: # id, user_id, name, leader_name, is_active, start_date, end_date, creation_time 6, 29, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10 620, 29, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09 1088, 29, DF, AS, 1, 2022-06-30, 2022-08-31, 2022-06-30

Things are not over yet. If you want to display the output based on each user_id group, use the following code:

-- first of all insert the following 4 lines on top of the original table data, which has the same user_id 50 61 50 DF AS 0 2021-10-10 630 50 DF RB 0 2022-02-09 1188 50 DF TS 0 2022-06-30 2288 50 DF AS 1 2022-07-30 select id,t1.user_id,name,leader_name,is_active, t1.creation_time as start_date, case is_active when 0 then t2.creation_time else current_date() end as end_date,t1.creation_time from (select id,user_id,name,leader_name,is_active,creation_time,@row_id:=@row_id+1 as row_id from test,(select @row_id:=0)t order by user_id,creation_time )t1 left join (select user_id,creation_time,@row_num:=@row_num+1 as row_num from test,(select @row_num:=0)t order by user_id,creation_time )t2 on t1.user_id=t2.user_id and t1.row_id+1=t2.row_num ; -- result set: # id, user_id, name, leader_name, is_active, start_date, end_date, creation_time 6, 29, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10 620, 29, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09 1088, 29, DF, AS, 1, 2022-06-30, 2022-08-31, 2022-06-30 61, 50, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10 630, 50, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09 1188, 50, DF, TS, 0, 2022-06-30, 2022-07-30, 2022-06-30 2288, 50, DF, AS, 1, 2022-07-30, 2022-08-31, 2022-07-30