I've got this query:

SELECT 
    Admission_No, Entry_Date, status, Answer_Date
FROM 
    TABLE
GROUP BY 
    Case_nu, Entry_Date, status, Answer_Date 

And I get this: the entry date is when something started… then there is a status 2 (temporarily) and then status 1 (which is the final status)

 Case Number               entry_date                           status                         answer_date

 5158                2022-05-01 17:12:00                            1                      2022-05-01 17:15:00
 5158                2022-05-01 17:12:00                            2                      2022-05-01 17:13:00
 2765                2022-05-01 18:17:00                            1                      2022-05-01 18:35:00
 2765                2022-05-01 17:12:00                            2                      2022-05-01 18:28:00
 6540                2022-05-01 23:04:00                            2                      2022-05-01 23:18:01

Etc….

you see that for each admission number I get 2 rows: when its status 2 and then a new row when its status 1 (and the timestamp of when the status occurred)

What I want is this: 1 line for each case number. When the status in final (=1) so I want to see a column of the timestamp when the status changed to 1 and a column when the status changed to 2 (when it was temporarily)

What I want:

Case Number          entry_date          status               final answer_date        temp answer date

5158            2022-05-01 17:12:00           1            2022-05-01 17:15:00        2022-05-01 17:13:00
2765            2022-05-01 18:17:00           1            2022-05-01 18:35:00        2022-05-01 18:28:00
6540           2022-05-01 23:04:00            2           (same as temp or NULL)      2022-05-01 23:18:01

I've tried setting [WHERE (status = 1 or status = 2)] and that’s good for eliminating the double rows by choosing/showing when the status is 1 and if not, so 2. But how do I show another column of the temp answer date – when it changed first to 2.

Maybe it's something with 'WITH' function?? please help....

this is the full query I've tried:

WITH T as (
select 
em.Admission_No as CaseNumber,
con.Entry_Date as CreatedDate,
con.final_answer as ConsultationStatus,
con.Answer_Date as AnswerDate

from em

inner join con
on em.P = con.P

where em.Unit = @UNIT
and con.entry_date >= @DATE

group by        
em.Admission_No,
con.Answer_Date,
con.final_answer,
con.Entry_Date,

 )

select  em.Admission_No
, MIN(con.Entry_Date) AS Entry_Date
, MIN(con.Final_Answer) AS status
, MAX(CASE WHEN con.Final_Answer = 1 THEN con.Answer_Date END) AS 
 final_answer_date
, MAX(CASE WHEN con.Final_Answer = 2 THEN con.Answer_Date END) AS 
 temp_answer_date


 from T
 Group by em.admission_no

and it doesnt work...

it says Msg 4104, Level 16, State 1, Line 62 The multi-part identifier "em.Admission_No" could not be bound. Msg 4104, Level 16, State 1, Line 63 The multi-part identifier "con.Entry_Date" could not be bound. Msg 4104, Level 16, State 1, Line 64 The multi-part identifier "con.Final_Answer" could not be bound. Msg 4104, Level 16, State 1, Line 65 The multi-part identifier "con.Final_Answer" could not be bound. Msg 4104, Level 16, State 1, Line 65 The multi-part identifier "con.Answer_Date" could not be bound. Msg 4104, Level 16, State 1, Line 66 The multi-part identifier "con.Final_Answer" could not be bound. Msg 4104, Level 16, State 1, Line 66 The multi-part identifier "con.Answer_Date" could not be bound.

how to do it right?

CodePudding user response：

I think this should be valid for most RDBMS:

WITH t AS (
    SELECT 
        Admission_No, Entry_Date, status, Answer_Date
    FROM 
        TABLE
    GROUP BY 
        Case_nu, Entry_Date, status, Answer_Date 
)
SELECT Admission_No
     , MIN(Entry_Date) AS Entry_Date
     , MIN(status) AS status
     , MAX(CASE WHEN status = 1 THEN Answer_Date END) AS final_answer_date
     , MAX(CASE WHEN status = 2 THEN Answer_Date END) AS temp_answer_date
FROM t
GROUP BY Admission_No

-- OR

SELECT Admission_No
     , MIN(Entry_Date) AS Entry_Date
     , MIN(status) AS status
     , MAX(CASE WHEN status = 1 THEN Answer_Date END) AS final_answer_date
     , MAX(CASE WHEN status = 2 THEN Answer_Date END) AS temp_answer_date
FROM (
    SELECT 
        Admission_No, Entry_Date, status, Answer_Date
    FROM 
        TABLE
    GROUP BY 
        Case_nu, Entry_Date, status, Answer_Date 
) AS t
GROUP BY Admission_No

Schema (MySQL v5.7)

CREATE TABLE t (
  `Admission_No` INTEGER,
  `entry_date` DATETIME,
  `status` INTEGER,
  `answer_date` DATETIME
);

INSERT INTO t
  (`Admission_No`, `entry_date`, `status`, `answer_date`)
VALUES
  ('5158', '2022-05-01 17:12:00', '1', '2022-05-01 17:15:00'),
  ('5158', '2022-05-01 17:12:00', '2', '2022-05-01 17:13:00'),
  ('2765', '2022-05-01 18:17:00', '1', '2022-05-01 18:35:00'),
  ('2765', '2022-05-01 17:12:00', '2', '2022-05-01 18:28:00'),
  ('6540', '2022-05-01 23:04:00', '2', '2022-05-01 23:18:01');

Query

SELECT Admission_No
     , MIN(Entry_Date) AS Entry_Date
     , MIN(status) AS status
     , MAX(CASE WHEN status = 1 THEN Answer_Date END) AS final_answer_date
     , MAX(CASE WHEN status = 2 THEN Answer_Date END) AS temp_answer_date
FROM t
GROUP BY Admission_No;

View on DB Fiddle