Multiply the hours worked SQL 2012

chavarin chuy 41 Reputation points
2022-09-03T13:31:21.437+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,638 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
    2022-09-05T02:11:52.853+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful