0

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

Mister X
  • 3,406
  • 3
  • 31
  • 72
  • 5
    *"I have table that stores the employe info in multiple rows"* This type of design is always difficult to work with and rarely performs well in a *relational* database. Your breaking the fundamental principles of Normal Form here. I really suggest you fix your design; then what you are after is a simple `DATEDIFF`. – Thom A May 24 '21 at 08:15
  • 1
    Ideally the table(s) should be normalised, but if you have no option but use it as it is then you may want to try grouping by Name – Jayvee May 24 '21 at 08:15
  • 1
    [We-are-not-a-code-writing-service](https://meta.stackoverflow.com/questions/372166/we-are-not-a-code-writing-service-comments-are-they-the-good-the-bad-or-the-u), so what did you try to solve this simple task yourself ? – Luuk May 24 '21 at 08:18
  • 1
    What is the datatype of the column `Time`? It's name suggest that it is a time column, but .... – Luuk May 24 '21 at 08:21
  • 1
    It *can't* be a `time`, @Luuk because of the ERP design. It will most certainly be a `(n)varchar`. Unless the OP is using `sql_variant`... I *really* hope not. – Thom A May 24 '21 at 08:25
  • @Larnu: But I do not want to guess that – Luuk May 24 '21 at 08:27
  • That doesn't change my statement that is **cannot** be a `time`, @Luuk . `CONVERT(time, 'No')` and `CONVERT(time,5)` will both error. If the OP's column `Time` *is* a `time`, their sample data is invalid, as it can't exist. – Thom A May 24 '21 at 08:35
  • Exactly, it cannot be `time`, and I do no want to guess, so I am asking `Mister X` what he did use. (and not guessing that it will be a `varchar()` – Luuk May 24 '21 at 08:38
  • Sometimes we forget that likely the design is not made by the OP, and he is just struggling with it. @Luuk, the Time column cannot be other than string, otherwise it couldn't store No, 5, and 10:00:00. – James May 24 '21 at 08:46

1 Answers1

3

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

James
  • 2,954
  • 2
  • 12
  • 25