根据另一个变量如何对日期进行分组?

huangapple go评论65阅读模式
英文:

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)

fiddle

huangapple
  • 本文由 发表于 2023年3月4日 05:30:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75632047.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定