CODEWITHSUNDEEP
sqlsql-server
user3191790
user3191790

Reputation: 29

How to calculate data from rows in sql server?

I have a table like this one below:

datatime      in_out     
---------------------
08:00         IN
08:30         OUT
09:30         OUT
10:00         IN
10:30         OUT

Is there any chance, after a SQL server query to get something like this:

IN       OUT
---------------
08:00    08:30
NULL     09:30
10:00    10:30

I spent about 2 weeks to find a solution. I am a beginner. The single solution was with min and max but it did not help me.

Upvotes: 1

Views: 124

Answers (2)

user645280
user645280

Reputation:

Guess I'm a bit slow to answer, but here's what I got using an inner query:

select
  (select top 1 
     IIF(a.in_out = b.in_out, null, datatime)
   from clk b 
   where a.datatime > b.datatime 
   order by b.datatime desc
  ) as [IN],
  a.datatime as [OUT]
from clk a
where a.in_out = 'OUT'

Note: doing it this way will "skip" the null rows either forwards or backwards depending on which way it's implemented...

Upvotes: 0

John Gibb
John Gibb

Reputation: 10773

This will solve it using row_numbering:

with Ordered as (
    select *, rn = row_number() over (order by datatime)
    from Input
)
select
    [In] = o_in.datatime
,   [Out] = o_out.datatime
from Ordered o_out
 left join Ordered o_in
   on o_in.rn = o_out.rn - 1
  and o_in.in_out = 'IN'
where o_out.in_out = 'OUT'

Upvotes: 1

Related Questions