i have table following sample data:
tag loc time1 10 6/2/15 8:00 10 6/2/15 7:50 10 6/2/15 7:30 20 6/2/15 7:20 20 6/2/15 7:15 b 10 6/2/15 7:12 b 10 6/2/15 7:11 10 6/2/15 7:10 10 6/2/15 7:00 i need sql select first (earliest) row in sequence until location changes, select earliest row again until location changes. in other words need following output above:
tag loc time1 10 6/2/15 7:30 20 6/2/15 7:15 10 6/2/15 7:00 b 10 6/2/15 7:11 i tried giorgos - lines select duplicated:
declare @temptbl table (rowid int primary key identity, tag nvarchar(1), loc int, time1 datetime) declare @tag nvarchar(1), @loc int, @time1 datetime insert @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:15 am') insert @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:20 am') insert @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:25 am') insert @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:20 am') insert @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:25 am') insert @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:30 am') insert @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:35 am') insert @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:40 am') select * @temptbl select tag, loc, min(time1) time2 ( select tag, loc, time1, row_number() on (order time1) - row_number() on (partition tag, loc order time1) grp @temptbl ) t group tag, loc, grp here results (there should 1 line each tag)
tag loc time2 1 20 2015-06-05 07:15:00.000 1 20 2015-06-05 07:25:00.000 4 20 2015-06-05 07:20:00.000 4 20 2015-06-05 07:30:00.000
can try this, change yourtable table name want
declare @temptbl table (rowid int primary key identity, tag nvarchar(1), loc int, time1 datetime) declare @tag nvarchar(1), @loc int, @time1 datetime declare tempcur cursor select tag, loc, time1 yourtable -- order here time or whatever columns want open tempcur fetch next tempcur @tag, @loc, @time1 while (@@fetch_status = 0) begin if not exists (select top 1 * @temptbl tag = @tag , loc = @loc , rowid = (select max(rowid) @temptbl)) begin print 'insert' print @tag print @loc print @time1 insert @temptbl (tag, loc, time1) values (@tag, @loc, @time1) end else begin print 'update' print @tag print @loc print @time1 update @temptbl set tag = @tag, loc = @loc, time1 = @time1 tag = @tag , loc = @loc , rowid = (select max(rowid) @temptbl) end fetch next tempcur @tag, @loc, @time1 end deallocate tempcur select * @temptbl 
Comments
Post a Comment