0

I have the following table. The startdatetime column is repeated when the duration is over a day. What I want is to write a query which skips the first row and count the difference of the second row whenever a duplicate cell in a startdatetime column come. E.g. In the case of following sample table the output I want is given in the table.

CREATE TABLE TEST
    ([Name] varchar(50), [StartDateTime1] datetime, [EndDateTime2] datetime, Diffy int)
;

INSERT INTO Test
    ([Name], [StartDateTime], [EndDateTime2], [Diffy])
VALUES
    ('ABC', '2015-07-21 16:08:02.000', '2015-07-21 16:18:10.000', '608' ),
    ('ABC', '2015-07-21 16:18:10.000', '2015-07-21 23:06:46.000', '24516' ),
    ('ABC', '2015-07-21 16:18:10.000', '2015-07-23 12:37:35.000', '159565' ),
    ('ABC', '2015-07-23 17:33:35.000', '2015-07-24 11:07:00.000', '63205' )
;


╔══════╦════════╗
║ Name ║ Diffy  ║
╠══════╬════════╣
║ ABC  ║    608 ║
║ ABC  ║ 159565 ║
║ ABC  ║  63205 ║
╚══════╩════════╝

The simple query I used is 

Select 
 Name,
 DateDiff(SECOND, StartDateTime, EndDateTime) As Diffy
From
 Test
Nimko
  • 139
  • 1
  • 8
  • Order isn't guaranteed. so by "Second row" you mean the one with the largest enddatetime2? Is there a possibility of more than 2 records? would you always want the one with the largest endDatetime2? – xQbert Jan 07 '16 at 17:01
  • I wonder how Diffy is `INT` but store strings – Juan Carlos Oropeza Jan 07 '16 at 17:04
  • I achieved the above task using the [http://stackoverflow.com/questions/8273987/select-unique-rows-based-on-single-distinct-column](http://stackoverflow.com/questions/8273987/select-unique-rows-based-on-single-distinct-column) – Nimko Jan 07 '16 at 23:31

1 Answers1

1

I think you just want group by:

Select Name,
       DateDiff(SECOND, StartDateTime, MAX(EndDateTime)) As Diffy
From Test
GROUP BY Name, StartDateTime;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • to skip the second row? – Nimko Jan 07 '16 at 17:04
  • @Nimko . . . This doesn't "skip" the second row. It only considers the last `EndDateTime` for each `StartDateTime`, which has the same effect if there are exactly two duplicates. – Gordon Linoff Jan 07 '16 at 17:37
  • Not it is only a sample of 22 lac records. On every day change the database duplicate the last transaction like second and third row in the above sample table. – Nimko Jan 07 '16 at 17:40