Join two entries rows ( Start time and End time) as a single row in Talend

Question

I have Data coming from a MS SQL Database, it is concerning the the working hours of employees.

The problem is that, the start time and the end time are stored as 2 different entries, so when the employee comes, he scans his badge and this is considered arrival time, and when he leaves, he scans his badge again and this is considered departure time. There is one column that helps to make the difference between the start and the end time (CodeNr column : B1 = StartTime, B2 = EndTime)

so this is how my Table looks like

Now i need this data as a single entry, in Talend oder from the Database,

so that should looks like

What to use in order to achieve this please (specially in Talend and when to complicate than in MS SQL)?

Answer 1

CREATE TABLE EmployeeWorkLoad(
EmployeeNr bigint,
Year int,
Month int,
Day int,
Hour int,
Minute int,
CodeNr char(2)
)


Insert into [EmployeeWorkLoad] ( [EmployeeNr],[Year],[Month] ,[Day],[Hour], [Minute] ,[CodeNr]) Values (1,2020,1,4,8,30,'B1'), 
(1,2020,1,4,16,45,'B2'), 
(1,2020,1,6,8,15,'B1'), 
(1,2020,1,6,16,45,'B2'),
(2,2020,3,2,8,10,'B1'), 
(2,2020,3,2,16,5,'B2')
  
  
GO

6 rows affected

WITH CTE AS (
select EmployeeNr,Year,Month,Day,

MAX(CASE WHEN CodeNr='B1' THEN Hour  END) AS StartHour,
MAX(CASE WHEN CodeNr = 'B1' THEN Minute END) AS StartMinute,
MAX(CASE WHEN CodeNr = 'B2' THEN Hour END) AS EndHour,
MAX(CASE WHEN CodeNr = 'B2' THEN Minute END) AS EndMinute

from EmployeeWorkLoad
group by EmployeeNr,Year,Month,Day )

SELECT * , ABS(EndHour-StartHour) AS DutationHour
,ABS(IIF(EndMinute <StartMinute, EndMinute+60, EndMinute)- StartMinute) AS DurationMinute
FROM
CTE

GO

EmployeeNr | Year | Month | Day | StartHour | StartMinute | EndHour | EndMinute | DutationHour | DurationMinute
---------: | ---: | ----: | --: | --------: | ----------: | ------: | --------: | -----------: | -------------:
         1 | 2020 |     1 |   4 |         8 |          30 |      16 |        45 |            8 |             15
         1 | 2020 |     1 |   6 |         8 |          15 |      16 |        45 |            8 |             30
         2 | 2020 |     3 |   2 |         8 |          10 |      16 |         5 |            8 |             55

db<>fiddle here