CODEWITHSUNDEEP
sqlsql-server
Mahendran V M
Mahendran V M

Reputation: 3496

Find difference between two rows in sql

I have table that stores the employe info in multiple rows and it having the common name for it along with its user login time and log out time for website, and would like to achieve the result and it may contains multiple names such as (N1,N2,N3..etc)

Name,Key,Time,
N1,TotalExp,No
N1,TotalYears,5
N1,LoggedIn,10:00:00
N1,LoggedOut,20:00:00

Expected Output will like below,

N1,TotalExp,TotalYrs,LoggedDifference
N1,No,5,10

Any one help me to achieve this

Upvotes: 0

Views: 533

Answers (1)

James
James

Reputation: 3015

Even it's a fact that the design of your database doesn't look well, you can query your data this way:

with your_data as (
  select 'N1' as Name,'TotalExp' as [Key],'No' as Time union all
  select 'N1','TotalYears','5' union all
  select 'N1','LoggedIn','10:00:00' union all
  select 'N1','LoggedOut','20:00:00'
)
select
  Name,
  max(case when [Key] = 'TotalExp' then Time else null end) as TotalExp,
  max(case when [Key] = 'TotalYears' then Time else null end) as TotalYrs,
  datediff(
    hour,
    max(case when [Key] = 'LoggedIn' then convert(time, Time) else null end),
    max(case when [Key] = 'LoggedOut' then convert(time, Time) else null end)
  ) as LoggedDifference
from your_data 
group by Name

You can test on here

Upvotes: 3

Related Questions