How to skip the first and consider the second row whenever duplicate cells come in a column SQL Server -
i have following table. startdatetime column repeated when duration on day. want write query skips first row , count difference of second row whenever duplicate cell in startdatetime column come. e.g. in case of following sample table output want given in table.
create table test ([name] varchar(50), [startdatetime1] datetime, [enddatetime2] datetime, diffy int) ; insert 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 used
select name, datediff(second, startdatetime, enddatetime) diffy test
i think want group by
:
select name, datediff(second, startdatetime, max(enddatetime)) diffy test group name, startdatetime;
Comments
Post a Comment