Calculating Daily hours between multiple clock events

Question

Need to display multiple clock events in single line with considering TimeSpan. And existing code is displaying only two clock in and two clockout instead of table as is clock event.

 

Current & Table Result,

 

 

 

 

 

 

 

 

 

 

 

 

Expected Result,

 

 

 

 

 existing code is with limited 2 clock in and clock out.

 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:coreConnectionString %>" SelectCommand="WITH Prep AS (SELECT CAST(dbo.UtcToLocal(creation_date) AS date) AS creation_date,rcd_num, person_num, full_name,preffered_name,government_num,first_name, MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END) AS CLOCK_IN1, MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END) AS CLOCK_OUT1, NULLIF (MAX(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END), MIN(CASE event_name WHEN 'CLOCK_IN' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END)) AS CLOCK_IN2, NULLIF (MAX(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END), MIN(CASE event_name WHEN 'CLOCK_OUT' THEN CAST(dbo.UtcToLocal(creation_date) AS TIME) END)) AS CLOCK_OUT2 FROM TEST GROUP BY ALL CAST(dbo.UtcToLocal(creation_date) AS date), person_num, full_name, preffered_name,government_num, first_name,rcd_num) SELECT creation_Date,  person_num, preffered_name, government_num,rcd_num, full_name, CLOCK_IN1, CLOCK_OUT1, CLOCK_IN2, CLOCK_OUT2, DATEDIFF(MINUTE, CLOCK_IN1, CLOCK_OUT1) / 60 + ISNULL(DATEDIFF(MINUTE, CLOCK_IN2, CLOCK_OUT2) / 60, 0) AS HoursWorked FROM Prep