-1

I have a table IOData as shown below 

   E Name   Date            Time    Status
   A1   08/06/14    19:36:10.00 Entry
   A1   08/06/14    19:36:16.00 Exit
   A1   08/06/14    19:36:18.00 Entry
   A1   08/07/14    00:26:39.00 Exit
   A1   08/07/14    00:38:36.00 Entry
   A1   08/07/14    02:41:14.00 Exit
   B1   08/07/14    19:12:29.00 Entry
   B1   08/08/14    04:14:28.00 Exit
   B1   08/08/14    04:14:31.00 Entry
   B1   08/08/14    04:14:33.00 Exit
   C1   08/06/14    19:26:10.00 Entry
   C1   08/06/14    19:36:16.00 Exit
   C1   08/06/14    19:36:50.00 Entry
   C1   08/06/14    23:06:28.00 Exit

I want the output as first Entry and Last Exit as show below 

 EName    Date        Time      Status
   A1    08/06/14   19:36:10.00 Entry
   A1    08/07/14   02:41:14.00 Exit
   B1    08/07/14   19:12:29.00 Entry
   B1    08/08/14   04:14:33.00 Exit
   C1    08/06/14   19:26:10.00 Entry
   C1    08/06/14   23:06:28.00 Exit

I have no Idea to perform this task. I have googled but no help. Please do the needful.

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dinesh kumar
  • 35
  • 2
  • 8

2 Answers2

0
;WITH MyCTE AS
(
    SELECT EName,
           Date,
           Time,
           Status,
           ROW_NUMBER() OVER(PARTITION BY EName ORDER BY Date, Time) AS FirstRn,
           ROW_NUMBER() OVER(PARTITION BY EName ORDER BY Date DESC, Time DESC) AS LastRn
    FROM   IOData 
)
SELECT   EName,
         Date,
         Time,
         Status
FROM     MyCTE  
WHERE    FirstRn = 1 
         OR LastRn = 1
ORDER BY EName,
         Date,
         Time

SQL Fiddle DEMO

Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
0

DECLARE @IOData AS TABLE(Name VARCHAR(MAX), [Date] DATE, [Time] TIME, [Status] VARCHAR(MAX)) INSERT INTO @IOData(Name, [Date], [Time], [Status]) VALUES ('A1', '08/06/14', '19:36:10.00', 'Entry'), ('A1', '08/06/14', '19:36:16.00', 'Exit'), ('A1', '08/06/14', '19:36:18.00', 'Entry'), ('A1', '08/07/14', '00:26:39.00', 'Exit'), ('A1', '08/07/14', '00:38:36.00', 'Entry'), ('A1', '08/07/14', '02:41:14.00', 'Exit'), ('B1', '08/07/14', '19:12:29.00', 'Entry'), ('B1', '08/08/14', '04:14:28.00', 'Exit'), ('B1', '08/08/14', '04:14:31.00', 'Entry'), ('B1', '08/08/14', '04:14:33.00', 'Exit'), ('C1', '08/06/14', '19:26:10.00', 'Entry'), ('C1', '08/06/14', '19:36:16.00', 'Exit'), ('C1', '08/06/14', '19:36:50.00', 'Entry'), ('C1', '08/06/14', '23:06:28.00', 'Exit')

;WITH _FirstEntries AS ( SELECT DISTINCT FirstEntries.* FROM @IOData AS ID OUTER APPLY (SELECT TOP 1 * FROM @IOData AS ID2 WHERE [Status] = 'Entry' AND ID2.Name = ID.Name ORDER BY ID2.[Date] ASC , ID2.[Time] ASC) AS FirstEntries ) , _LastExits AS( SELECT DISTINCT LastExit.* FROM @IOData AS ID OUTER APPLY (SELECT TOP 1 * FROM @IOData AS ID2 WHERE [Status] = 'Exit' AND ID2.Name = ID.Name ORDER BY ID2.[Date] DESC , ID2.[Time] DESC) AS LastExit )
SELECT * FROM ( SELECT * FROM _FirstEntries UNION ALL
SELECT * FROM _LastExits ) AS ResultUnSorted ORDER BY Name, [Status]

miezke
  • 51
  • 2
  • Thanks Miezke. Its very helpful for me. I have another query regarding this. How to give date range in where condition if I want to filter the data by date. – dinesh kumar Aug 29 '14 at 10:14
  • You mean [this](http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates) ? – miezke Aug 29 '14 at 12:11