英文:
How to Group Dates based on another variable?
问题
I have a database for cars. Each car has one to many assignments. Each assignment has a status. The status needs to be renewed every fiscal year, so a status can be the same through many fiscal years.
I'd like to compile all the statuses with the years they were active for each car. Like this:
ID | Status and Years
----+-----------------------------------------------
0 | A (2020-2021), B (2021-2022)
1 | Z (2022-2023)
2 | A (2012-2013), Z (2013-2015)
What I have right now is this:
SELECT Id, Status, Effective_dt, Expiration_dt
FROM CAR_ASGNMT
Which produces this:
Id | Status | Effective_dt | Expiration_dt
---+--------+--------------+---------------
0 | A | 28-SEP-2020 | 06-DEC-2020
0 | A | 07-DEC-2020 | 28-MAR-2021
0 | A | 28-MAR-2021 | 26-SEP-2021
0 | A | 27-SEP-2021 | 05-DEC-2021
0 | B | 06-DEC-2021 | 26-MAR-2022
How can I compile these statuses so they're readable like the first table?
英文:
I have a database for cars. Each car has one to many assignments. Each assignment has a status. The status needs to be renewed every fiscal year, so a status can be the same through many fiscal years.
I'd like to compile all the statuses with the years they were active for each car. Like this:
ID | Status and Years
----+-----------------------------------------------
0 | A (2020-2021), B (2021-2022)
1 | Z (2022-2023)
2 | A (2012-2013), Z (2013-2015)
What I have right now is this:
SELECT Id, Status, Effective_dt, Expiration_dt
FROM CAR_ASGNMT
Which produces this:
Id | Status | Effective_dt | Expiration_dt
---+--------+--------------+---------------
0 | A | 28-SEP-2020 | 06-DEC-2020
0 | A | 07-DEC-2020 | 28-MAR-2021
0 | A | 28-MAR-2021 | 26-SEP-2021
0 | A | 27-SEP-2021 | 05-DEC-2021
0 | B | 06-DEC-2021 | 26-MAR-2022
How can I compile these statuses so they're readable like the first table?
答案1
得分: 1
CREATE TABLE CAR_ASGNMT
(ID int, STATUS varchar(1), EFFECTIVE_DT DATE, EXPIRATION_DT DATE)
;
BEGIN
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2020-09-28', DATE '2020-12-06');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2020-12-07', DATE '2021-03-28');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2021-03-28', DATE '2021-09-26');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2021-09-27', DATE '2021-12-05');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'B', DATE '2021-12-06', DATE '2022-03-26');
END;
/
1 行受影响
SELECT
ID
, STATUS
|| ' ('
|| TO_CHAR(MIN(EFFECTIVE_DT),'YYYY')
|| ' - '
|| TO_CHAR(MAX(EXPIRATION_DT),'YYYY')
|| ')'
AS STATUS_RANGE
, MIN(EFFECTIVE_DT) MIN_DT
FROM CAR_ASGNMT
GROUP BY
ID
, STATUS
ID | STATUS_RANGE | MIN_DT |
---|---|---|
0 | A (2020 - 2021) | 28-SEP-20 |
0 | B (2021 - 2022) | 06-DEC-21 |
```sql
SELECT
ID
, LISTAGG(STATUS_RANGE, ', ') WITHIN GROUP (ORDER BY MIN_DT) AS STATUS_YEARS
FROM (
SELECT
ID
, STATUS
|| ' ('
|| TO_CHAR(MIN(EFFECTIVE_DT), 'YYYY')
|| ' - '
|| TO_CHAR(MAX(EXPIRATION_DT), 'YYYY')
|| ')'
AS STATUS_RANGE
, MIN(EFFECTIVE_DT) MIN_DT
FROM CAR_ASGNMT
GROUP BY
ID
, STATUS
) D
GROUP BY ID;
ID | STATUS_YEARS |
---|---|
0 | A (2020 - 2021), B (2021 - 2022) |
[fiddle](https://dbfiddle.uk/WHhP8e5i)
英文:
CREATE TABLE CAR_ASGNMT
(ID int, STATUS varchar(1), EFFECTIVE_DT DATE, EXPIRATION_DT DATE)
;
BEGIN
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2020-09-28',DATE '2020-12-06');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2020-12-07', DATE '2021-03-28');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2021-03-28', DATE '2021-09-26');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'A', DATE '2021-09-27', DATE '2021-12-05');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES
(0, 'B', DATE '2021-12-06', DATE '2022-03-26');
END;
/
1 rows affected
SELECT
ID
, STATUS
|| ' ('
|| TO_CHAR(MIN(EFFECTIVE_DT),'YYYY')
|| ' - '
|| TO_CHAR(MAX(EXPIRATION_DT),'YYYY')
|| ')'
AS STATUS_RANGE
, MIN(EFFECTIVE_DT) MIN_DT
FROM CAR_ASGNMT
GROUP BY
ID
, STATUS
ID | STATUS_RANGE | MIN_DT |
---|---|---|
0 | A (2020 - 2021) | 28-SEP-20 |
0 | B (2021 - 2022) | 06-DEC-21 |
SELECT
ID
, LISTAGG(STATUS_RANGE,', ') WITHIN GROUP (ORDER BY MIN_DT) AS STATUS_YEARS
FROM (
SELECT
ID
, STATUS
|| ' ('
|| TO_CHAR(MIN(EFFECTIVE_DT),'YYYY')
|| ' - '
|| TO_CHAR(MAX(EXPIRATION_DT),'YYYY')
|| ')'
AS STATUS_RANGE
, MIN(EFFECTIVE_DT) MIN_DT
FROM CAR_ASGNMT
GROUP BY
ID
, STATUS
) D
GROUP BY ID;
ID | STATUS_YEARS |
---|---|
0 | A (2020 - 2021), B (2021 - 2022) |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论