CODEWITHSUNDEEP
sqlsql-server-2008
Azeem
Azeem

Reputation: 2924

Time sum of similar employees in a SQL Server query

I have three tables in my database.

First table is Employee:

enter image description here

Second table is EmployeeTimeIn : (HoursConsumed have time in seconds!)

enter image description here

Third table is EmployeeTimeOut:

enter image description here

I wrote the following SQL query which lists all the employees grouped by their IDs and working time of each time in session:

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;

It produces the following output:

enter image description here

I need to modify this query so that for each employee it shows the grand total working time in workingtime column. In this scenario It will display workingtime of 0:41:53 for EID 4 and 0:0:39 for EID 200.

Please advise.

(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!)

Upvotes: 0

Views: 859

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21776

I assume that you always can convert WorkingTimeInSeconds to hours:

;WITH Totals(WorkingTimeInSeconds, EmployeeId, EmployeeName, DateRef) AS
(
   SELECT 
      e.EmployeeId,
      e.EmployeeName,
      eti.Date_Refб
      SUM(HoursConsumed) WorkingTimeInSeconds,
    FROM Employee e
    JOIN EmployeeTimeIn eti
      ON e.EmployeeId = eti.EId
    GROUP BY 
      e.EmployeeId,
      e.EmployeeName,
      eti.Date_Ref
)

SELECT
  Totals.EmployeeId,
  Totals.EmployeeName,
  eti.Time_In,
  eto.Time_Out,
  eti.Ref_Date,
  eti.WorkingTimeInSeconds
FROM Totals
JOIN EmployeeTimeIn eti
  ON Totals.DateRef = eti.Date_Ref AND eti.EID=Totals.EmployeeId
JOIN EmployeeTimeOut eto
  ON Totals.DateRef = eto.Date_Ref AND eto.EID=Totals.EmployeeId AND eti.RefId = eto.RefId

Upvotes: 2

What Would Be Cool
What Would Be Cool

Reputation: 6778

You could treat your current query results as a fourth table, and then do a select sum from that. Of course you would need to keep the values of your original query in numeric values, but I'm not sure what hours consumed represents. 

select EmployeeName, sum(workingtime), date_ref
from (
-- original query but modified to not convert strings
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   ...
) group by EmployeeName, date_ref

You could also pull the time calculation out into a function to get hours by employee and date, but not sure about your data. Hope this help.

Upvotes: 0

Related Questions