Show diferents rows in the same column SQL SERVER 2012

Question

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:(

Answer 1

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.