title: Time sum of similar employees in a SQL Server query
body: <p>I have three tables in my database.</p>
  
  <p>First table is <code>Employee</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/zUzlS.png" alt="enter image description here"></p>
  
  <p>Second table is <code>EmployeeTimeIn</code> : (HoursConsumed have time in seconds!)</p>
  
  <p><img src="https://i.stack.imgur.com/8MTuG.png" alt="enter image description here"></p>
  
  <p>Third table is <code>EmployeeTimeOut</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/U4j85.png" alt="enter image description here"></p>
  
  <p>I wrote the following SQL query which lists all the employees grouped by their IDs and working time of each time in session:</p>
  
  <pre><code>select 
      v.EID, e.EmployeeName, v.Time_In, v.Time_Out,
      convert(varchar(5), SUM(v.HoursConsumed) / 3600)  + ':' + 
      convert(varchar(5), SUM(v.HoursConsumed) % 3600 / 60) + ':' + 
      convert(varchar(5), (SUM(v.HoursConsumed) % 60)) as workingtime,
      v.Date_Ref
  from
      (select 
           e1.EID, e1.Time_In, e2.Time_Out, e1.HoursConsumed,    
           CONVERT(VARCHAR(10), e1.Date_Ref, 111) as Date_Ref
       from EmployeeTimeIn as e1, EmployeeTimeOut as e2
       where e1.Refid = e2.Refid) as v, Employee as e
       where v.EID = e.EmployeeID
       group by 
            v.EID, e.EmployeeName, v.Time_In, v.Time_Out, v.HoursConsumed, v.Date_Ref;
  </code></pre>
  
  <p>It produces the following output:</p>
  
  <p><img src="https://i.stack.imgur.com/wbTuq.png" alt="enter image description here"></p>
  
  <p>I need to modify this query so that for each employee it shows the grand total working time in <code>workingtime</code> column. In this scenario It will display <code>workingtime</code> of 0:41:53 for EID 4 and 0:0:39 for EID 200.</p>
  
  <p>Please advise.</p>
  
  <p><em>(NOTE: final output should have three rows for this example data because i need to show Time In and Time Out fields in final table too!)</em></p>