Mam trzy stoły w mojej bazie danych.

Pierwszy stół to Employee:

enter image description here

Drugi stół to EmployeeTimeIn: (Zużyte godziny mają czas w sekundach!)

enter image description here

Trzeci stół to EmployeeTimeOut:

enter image description here

Napisałem następujące zapytanie SQL, które wylicza wszystkich pracowników pogrupowanych według ich identyfikatorów i czasu pracy w każdej sesji:

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;

Wytwarza następujące wyjście:

enter image description here

Muszę zmodyfikować to zapytanie, aby dla każdego pracownika wyświetlało całkowity czas pracy w kolumnie workingtime. W tym scenariuszu wyświetli workingtime 0:41:53 dla EID 4 i 0:0:39 dla EID 200.

Proszę doradź.

(UWAGA: końcowe dane wyjściowe powinny zawierać trzy wiersze dla przykładowych danych, ponieważ muszę pokazać pola Time In i Time Out również w tabeli końcowej!)

0
Azeem 18 luty 2012, 21:08

2 odpowiedzi

Najlepsza odpowiedź

Zakładam, że zawsze możesz przeliczyć WorkingTimeInSeconds na godziny:

;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
2
Oleg Dok 19 luty 2012, 20:19

Możesz potraktować bieżące wyniki zapytania jako czwartą tabelę, a następnie wybrać z niej sumę. Oczywiście musiałbyś zachować wartości oryginalnego zapytania w wartościach liczbowych, ale nie jestem pewien, co oznaczają zużyte godziny.

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

Możesz również przeciągnąć obliczenia czasu do funkcji, aby uzyskać godziny według pracownika i daty, ale nie masz pewności co do swoich danych. Mam nadzieję, że to pomoże.

0
What Would Be Cool 18 luty 2012, 21:40