title: How to do a new column of temporarily values in SQL?
body: <p>I've got this query:</p>
  <pre><code>SELECT 
      Admission_No, Entry_Date, status, Answer_Date
  FROM 
      TABLE
  GROUP BY 
      Case_nu, Entry_Date, status, Answer_Date 
  </code></pre>
  <p>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)</p>
  <pre><code> 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
  </code></pre>
  <p>Etc….</p>
  <p>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)</p>
  <p>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)</p>
  <p>What I want:</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <p>Maybe it's something with 'WITH' function?? please help....</p>
  <p>this is the full query I've tried:</p>
  <pre><code>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 &gt;= @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
  </code></pre>
  <p>and it doesnt work...</p>
  <p>it says Msg 4104, Level 16, State 1, Line 62 The multi-part identifier &quot;em.Admission_No&quot; could not be bound. Msg 4104, Level 16, State 1, Line 63 The multi-part identifier &quot;con.Entry_Date&quot; could not be bound. Msg 4104, Level 16, State 1, Line 64 The multi-part identifier &quot;con.Final_Answer&quot; could not be bound. Msg 4104, Level 16, State 1, Line 65 The multi-part identifier &quot;con.Final_Answer&quot; could not be bound. Msg 4104, Level 16, State 1, Line 65 The multi-part identifier &quot;con.Answer_Date&quot; could not be bound. Msg 4104, Level 16, State 1, Line 66 The multi-part identifier &quot;con.Final_Answer&quot; could not be bound. Msg 4104, Level 16, State 1, Line 66 The multi-part identifier &quot;con.Answer_Date&quot; could not be bound.</p>
  <p>how to do it right?</p>