CODEWITHSUNDEEP
sql-server
syedcic
syedcic

Reputation: 307

SQL Server 2008 / 2012 [time] multiple ins & outs- advanced query

I have a table with raw data, wherein a particular id can have multiple time entries for each day, it's not static, but it will be in pairs, if there is an in entry, then there will also be an out entry as below:

Row_Num Tr_Date     Tr_Time    Machine_Serial   Machine_ID  ID
-----------------------------------------------------------------
75251   04/05/2014  7:31:56 AM  243496             71767    8525
75259   04/05/2014  8:14:21 AM  243512             71767    8525
75265   04/05/2014  9:07:21 AM  244005             73330    8525
75312   04/05/2014  9:29:09 AM  244115             71767    8525
75465   04/05/2014  1:01:45 PM  244658             71767    8525
75985   04/05/2014  5:34:30 PM  245501             73330    8525

I want to mark each row with 'START' and 'END' according to the pair.

The first time should be 'START' , second time should be 'END', then third time should be 'START' and fourth time should be 'END', it goes on like this. As I said before there is no predefined set.

Kindly help on the query.

Thanks and regards,

Desired result:

Row_Num Tr_Date      Tr_Time    Machine_Serial  Machine_ID  ID      Type
---------------------------------------------------------------------------
75251   04/05/2014  7:31:56 AM  243496             71767    8525    START
75259   04/05/2014  8:14:21 AM  243512             71767    8525    END
75265   04/05/2014  9:07:21 AM  244005             73330    8525    START
75312   04/05/2014  9:29:09 AM  244115             71767    8525    END
75465   04/05/2014  1:01:45 PM  244658             71767    8525    START
75985   04/05/2014  5:34:30 PM  245501             73330    8525    END

Upvotes: 1

Views: 46

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

You can use ROW_NUMBER() OVER(), and just output the status depending on whether it's even or odd;

SELECT *,
  CASE WHEN ROW_NUMBER() 
            OVER (ORDER BY row_num) % 2 = 0 
  THEN 'END' ELSE 'START' END type
FROM mytable
ORDER BY row_num;

An SQLfiddle to test with.

(of course you could also calculate the row number over tr_date and tr_time, but in this case the row_num field seems sufficient)

Upvotes: 1

Related Questions