c# - How to get the difference between time on same table but on different lines

Question

I would like to know how to get the difference between time if the data is in the same table but on two separate lines. For example, below I have a screenshot of the data, every employee have two records, the first clock point and the last clock point. I want the difference between those times and save it into a new table. How do I do that?

Table structure:

CREATE TABLE [dbo].[RefinedData](
[ClockNo] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Department] [nvarchar](50) NULL,
[ClockPoint] [nvarchar](50) NULL,
[Date] [nvarchar](50) NULL,
[Time] [int] NULL
) ON [PRIMARY]

Layout of Data:

So in this case I want the time difference of the user Gerard saved in a new table but only one record per user.

Answer 1

It is apparent from your sample data , you have 2 rows of data per user. row_number will work for you. Subtracting rn2-rn1 will return required output

with cte as
(select  [ClockNo] ,
        [FirstName] ,
        [LastName] ,
        [Department] ,
        [ClockPoint] ,
        [Date] ,
        time,
        ROW_NUMBER() over partition by clockno,firstname order by date,time) rn
        from mytable 
        ) 

        select  c.[ClockNo] ,
        c.[FirstName] ,
        c.[LastName] ,
        c.[Department] ,
        c.[ClockPoint] ,
        c.[Date] ,
        c1.time -c.time
        from cte c inner join (select * from  cte where rn= 2) c1 
        on c.rn = c1.rn-1 and c.firstname = c.firstname and c.clockno = c1.cloclno 
         where c.rn = 1