CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user7629904
user7629904

Reputation:

How to select and insert to another temp table

   ID   LogDate       LogTime         Mode  

   1    2017-02-23  19:30:00.0000000    1   
   4    2017-02-24  08:00:00.0000000    0

i got the above result.

I need to get the output as below 

   LogDate        InTime               OutTime

   2017-02-23     19:30:00.0000000    08:00:00.0000000

Updated Question Actually below table is the original table.From this table I needed to get the first and last records and display first logtime as InTime and second logtime as OutTime as output

   ID   LogDate     LogTime        InoutMode    
    1   2017-02-23  19:30:00.0000000    1   
    2   2017-02-23  23:00:00.0000000    0   
    3   2017-02-23  23:30:00.0000000    1   
    4   2017-02-24  08:00:00.0000000    0

Upvotes: 1

Views: 54

Answers (2)

Meysam Chegini
Meysam Chegini

Reputation: 999

you need primery key for this select but 

     SELECT LogDate,
     LogTime  AS InTime,
     (
         SELECT TOP 1 t1.LogTime
         FROM   yourTable AS t1
         WHERE  Mode = 0
     )        AS OutTime   FROM   yourTable  WHERE  Mode = 1

Upvotes: 3

Utsav
Utsav

Reputation: 8093

Use this

http://sqlfiddle.com/#!6/c74ea/5

WITH cte AS
  (SELECT t.*,
          row_Number() over (
                             ORDER BY logdate,logtime) AS inc,
          row_Number() over (
                             ORDER BY logdate DESC,logtime DESC) AS dec
   FROM table1 t)
SELECT t1.logdate,
       t1.logtime AS intime,
       t2.logtime AS outtime
FROM
  (SELECT logdate,
          logtime,
          inc
   FROM cte
   WHERE inc=1) t1
INNER JOIN
  (SELECT logdate,
          logtime,dec
   FROM cte
   WHERE dec=1) t2 ON t1.inc=t2.dec

Upvotes: 1

Related Questions