Multiply the hours worked SQL 2012

Question

Hi, I have a code that it gives me the minutes worked and it converts it into hours worked by every employee, I have the columns "HORA DE ENTRADA","RETARDO","HORA DE SALIDA" and it takes the value of "RETARDO" if the value of "HORA DE ENTRADA" is null, and it also match with the number of the week and only shows me those days, this is the code: 237460-code.txt

Well, what I want to do, is, as it shows me, the registers by every day of a week, for example, today number week is 35, and as I select the days Monday to Saturday, and every employe creates a new register, I want to sum the number of the hours worked every day by the same employe. For example, I work Monday to Saturday, and my hours worked are: M: 9:00:00 T: 8:57:00 W: 7:00:00 T: 9:00:00 F: 8:58:00 S: 8:56:00 the sum of that should give me: 51:51 hours worked by the week, and also I want to show me only one register, and no 6, like a resume of the week I know Im asking to much, but anyone knows how to do it? I have cero experience with SQL and searching for tutorials I didnt find anything:(

This is my table: view

Answer 1

Hi @chavarin chuy
Try this query:

DECLARE @YA AS DATETIME = GETDATE()  
  
;with cte as  
(  
 select *  
 from Datos.dbo.RegistroDeEntradas  
 cross apply (values (datediff(minute, cast( isnull(nullif([HORA DE ENTRADA], ''), [RETARDO]) as time), cast([HORA DE SALIDA] as time)))) M(m)   
 where DATEPART(ISO_WEEK, @YA) = NoSem  
)  
select EMPLEADO,ID,timefromparts(SUM(m) / 60, SUM(m) % 60, 0, 0, 0) as [hours worked]  
from cte   
group by EMPLEADO,ID;  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.