Show diferents rows in the same column SQL SERVER 2012

chavarin chuy 41 Reputation points
2022-09-06T22:20:43.89+00:00

Hello, I have a table that shows me the time that people enter the work area, the hours are stored in different columns, "Entry time", "Delay", Exit time", if the person enters late, the time will be saved in the delay box and the entry time box will remain blank, what I want to do is, since I work 6 days a week, it creates 6 records for me, I already have a code that adds up the hours worked every days, but I also want it to send me another column, where it shows me the day and hour that it arrived late, something like this: view , What I want from there, is something like the column "Delay" and this is how is saved: view

This is my code: 238240-12122.txt

Anybody knows how to do it, im new in sql and I didnt find a tutorial about that:(

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,093 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,490 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,256 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,580 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,686 Reputation points
    2022-09-07T02:58:58.51+00:00

    Hi @chavarin chuy
    If your SQL Sever support STRING_AGG function, then try this:

    DECLARE @YA AS DATETIME = GETDATE();  
      
    ;WITH CTE AS  
    (  
     SELECT *,CASE WHEN [RETARDO] IS NOT NULL THEN LEFT(DATENAME(WEEKDAY,FECHA),1)+':'+CAST([RETARDO] AS VARCHAR(10)) ELSE NULL END AS [Delay]  
     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,NoSem  
          ,timefromparts(SUM(m) / 60, SUM(m) % 60, 0, 0, 0) as [HORAS TRABAJADAS]  
    	  ,STRING_AGG([Delay],',') AS [Delay]  
    FROM CTE   
    GROUP BY EMPLEADO,ID,NoSem;  
    

    If not, you could use 'STUFF' along with 'FOR XML PATH' instead, like this:

    DECLARE @YA AS DATETIME = GETDATE();  
      
    ;WITH CTE AS  
    (  
     SELECT *,CASE WHEN [RETARDO] IS NOT NULL THEN LEFT(DATENAME(WEEKDAY,FECHA),1)+':'+CAST([RETARDO] AS VARCHAR(10)) ELSE NULL END AS [Delay]  
     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 C2.EMPLEADO,C2.ID,C2.NoSem,SUM(C2.m)as [HORAS TRABAJADAS]  
          ,STUFF((SELECT ','+[Delay] FROM CTE C1 WHERE C1.ID = C2.ID FOR XML PATH ('')),1,1,'') AS [Delay]  
    FROM CTE C2  
    GROUP BY C2.EMPLEADO,C2.ID,C2.NoSem;  
    

    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