How to calculate data from rows in sql server?

Question

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.

The basic trick here is to join the table to itself, where the matching rule (ON condition) is the first OUT row that is after the IN row, or the first IN row that is before the OUT row. The trick is the NULL in your example: the exact code you need will depend on how you want to handle that (which is why this is just a comment). Tell us how to handle the nulls, and we can give you a complete answer. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 13, 2014 at 20:59
I want to make another column for duration were to calculate in and out time with datediff. Its not important the null. If i have null on in or out column then null to duration column to. Here is an query for the first table. SELECT [UnitTS], case when RdrHead = 'A' THEN 'OUT' ELSE 'IN 'END as Reader ,[RdrName] ,[CrdName] FROM [Pontaj].[dbo].[History] where (UnitNr = '3' and RdrNr IN ('0','2','3') OR UnitNr = '4' and RdrNr IN('1','6')) and Type = 'A' and RdrHead in ('A','B') and Substring(unitts,1,8) = '20140108' and CrdName = '' — user3191790
– user3191790, Commented Jan 13, 2014 at 21:07
What version of SQL Server are you using? Also what is your actual table structure? Is it really just time or is there a date somewhere? — Martin Smith
– Martin Smith, Commented Jan 13, 2014 at 21:10
If you have IN, IN, OUT, OUT, then how do you assign the values? Also, do you ever have IN with no matching OUT, or just OUT with no matching IN? — Gordon Linoff
– Gordon Linoff, Commented Jan 13, 2014 at 21:13

John Gibb · Accepted Answer · 2014-01-13 21:14:47Z

1

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'

answered Jan 13, 2014 at 21:14

John Gibb

10.8k2 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3191790 Over a year ago

WOW, John is this magic ? :) Its working. Thank you, have a nice day.

user645280 Over a year ago

Note: If you get IN, IN in a row... this'll skip that row (should still be accurate calculated results, there will just be no row instead of a NULL row in the results) Not that I have a solution to that yet, and if this works I won't bother looking.

user3191790 Over a year ago

I will make some test tomorrow and i will take a look to see if this will skip the row

user645280 · Accepted Answer · 2014-01-13 21:29:16Z

0

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...

answered Jan 13, 2014 at 21:29

user645280

Collectives™ on Stack Overflow

How to calculate data from rows in sql server?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related