1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| declare @MaTable table (Sample_TDate datetime, Signal_Index int, Sample_Value int);
insert into @MaTable (Sample_TDate, Signal_Index, Sample_Value)
select '2012-07-23 01:06:57.000', 5, 82 union all
select '2012-07-23 01:06:58.000', 5, 83 union all
select '2012-07-23 01:06:59.000', 5, 82 union all
select '2012-07-23 01:07:00.000', 5, 82 union all
select '2012-07-23 01:07:01.000', 5, 83 union all
select '2012-07-23 01:07:02.000', 5, 83 union all
select '2012-07-23 01:07:56.000', 5, 79 union all
select '2012-07-23 01:07:57.000', 5, 82 union all
select '2012-07-23 01:08:02.000', 5, 80 union all
select '2012-07-23 01:08:03.000', 5, 79;
With Sample_TMP (Sample_TDate, Signal_Index, Sample_Value, ord)
as
(
select Sample_TDate, Signal_Index, Sample_Value
, case
when DATEPART(SECOND,[Sample_TDate]) = 0
then 1
when Sample_TDate = max(Sample_TDate) over(partition by convert(varchar(16), dateadd(second, 59, Sample_TDate), 121))
then 2
end as ord
from @MaTable
)
select Sample_TDate, Signal_Index, Sample_Value
from Sample_TMP
where ord is not null;
Sample_TDate Signal_Index Sample_Value
----------------------- ------------ ------------
2012-07-23 01:07:00.000 5 82
2012-07-23 01:07:57.000 5 82
2012-07-23 01:08:03.000 5 79 |
Partager