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
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.
Things are not over yet. If you want to display the output based on each user_id group, use the following code: