title: Achieving additional column that states in and out at time of the procedure in SQL
body: <p>I have the below query which give me the below results for which I need to add another column which denotes In and Out during procedure</p>
  <pre><code>DECLARE @start datetime, @end datetime
  
  SELECT @start = '10/01/2021'
  SELECT @end = '10/4/2021';
  
  WITH list AS
  ( 
      SELECT
          c.lastname + ', ' + c.FirstName Doctor,
          COUNT(ID) count
      FROM
          Carecenter c
      WHERE
          c.LastName IS NOT NULL 
          AND (c.HIDDEN IS NULL OR c.HIDDEN &lt;&gt; 1) 
          AND c.Procedure_Date BETWEEN @start AND @end
      GROUP BY
          LastName, FirstName
  )
  SELECT
      c.lastname + ', ' + c.FirstName Doctor,
      procedure_name,
      COUNT(procedure_name) [Procedure count], 
      l.count [patient count]
  FROM
      Carecenter c
  LEFT JOIN
      list l ON l.Doctor = c.lastname + ', ' + c.FirstName + ' ' + Title
  WHERE
      Procedure_Date BETWEEN @start AND @end
  GROUP BY
      c.lastname, FirstName, l.count, Procedure_Name
  </code></pre>
  <p>And the result is:</p>
  <pre><code>Doctor  procedure_name  Procedure count patient count
  -----------------------------------------------------
  Dia MD      D2              1               2
  Siri MD     C1              2               2
  Google MD   A1              2               2
  Alexa MD    I2              1               1
  </code></pre>
  <p>I tried to include the <code>Patient_Class 'Inpatient\Outpatient'</code> so to differentiate the classes on the count.</p>
  <p>Expected results:</p>
  <pre><code>Doctor  procedure_name  Procedure count patient count   patient_class
  ----------------------------------------------------------------------
  Dia MD      D2              1               2           Outpatient
  Dia MD      D2              1               2           Outpatient
  Siri MD     C1              2               2           Outpatient
  Siri MD     C1              2               2           Inpatient
  Google MD   A1              2               2           Inpatient
  Google MD   A1              2               2           Outpatient
  Alexa MD    I2              1               1           Inpatient
  </code></pre>
  <p>How can I achieve the above results by adding the column <code>patient_class</code> in the above query?</p>