Demo for Torment March

0

Please login or register to vote for this query.

(click on this box to dismiss)

For SO answer: http://stackoverflow.com/a/8275771/939860

Korean Language

Q&A for linguists, teachers and students of the Korean language

-- Enter Query Title
-- Enter Query Description

create table #t (CheckDate date, CheckType nvarchar(3), CheckTime time)
Insert into #t values
 ('2011-11-24', 'OUT', '14:30:11')
,('2011-11-25', 'IN',  '14:40:00')
,('2011-11-25', 'OUT', '14:45:00')
,('2011-11-25', 'IN',  '14:50:00')
,('2011-11-25', 'OUT', '14:55:00')
,('2011-11-25', 'IN',  '15:00:00')
,('2011-11-25', 'OUT', '15:05:00')
,('2011-11-25', 'IN',  '15:15:00')
,('2011-11-25', 'OUT', '15:20:00')
,('2011-11-25', 'IN',  '15:25:00')
,('2011-11-25', 'IN',  '15:26:00')
,('2011-11-25', 'OUT', '15:30:00')
,('2011-11-25', 'OUT', '15:40:00')
,('2011-11-25', 'IN',  '15:45:00')

;WITH x AS (
   SELECT CheckDate, CheckType, CheckTime
         ,row_number() OVER (ORDER BY CheckDate, CheckTime) As rn
   FROM #t )
SELECT CASE WHEN x.CheckType = 'IN' OR x.rn = 1 THEN x.CheckDate 
                                               ELSE y.CheckDate END AS CheckDate
      ,CASE WHEN x.CheckType = 'IN'  THEN x.CheckTime ELSE NULL END AS CheckIn
      ,CASE WHEN y.CheckType = 'OUT' THEN y.CheckTime
            WHEN x.CheckType = 'OUT' THEN x.CheckTime ELSE NULL END AS CheckOut
FROM   x
LEFT   JOIN x AS y ON y.rn = x.rn + 1
WHERE  x.CheckType = 'IN'
OR     y.CheckType = 'OUT'
OR     x.rn = 1

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results