\r
 --/topic/16572\r
 
set nocount on\r
\r
create table abc (\r
moment  datetime primary key,\r
first int, \r
last int, \r
hight int, \r
low int \r
)\r
\r
\r
declare @s int\r
declare @d datetime\r
set @d=convert(datetime,convert(char( 16 ),getdate(), 120 ), 120 )\r
set @s= 0 \r
while @s< 9 \r
begin\r
insert into abc (moment,first,last) values (dateadd(minute,@s,@d), 100 *rand(), 100 *rand())\r
set @s=@s+ 1 \r
end\r
\r
update abc \r
set hight=first+last,\r
low= case when first<last then first else last end\r
\r
go\r
declare @moment datetime\r
declare @period int\r
select @moment=min(moment) from abc\r
set @period= 3 \r
\r
select * from abc\r
\r
select moment, max(first),max(last),max(hight),max(low) from \r
(\r
select min(moment) as moment, \r
 0  as first,\r
 0  as last,\r
max(hight) as hight,\r
min(low) as low \r
\r
from abc\r
group by datediff(minute,@moment,moment)/@period\r
\r
union\r
\r
select moment,first, 0 , 0 , 0  from abc\r
where datediff(minute,@moment,moment)%@period= 0 \r
\r
union\r
\r
select dateadd(minute,-@period+ 1 ,moment), 0 ,last, 0 , 0  from abc\r
where datediff(minute,@moment,moment)%@period=@period- 1 \r
) as t\r
group by moment\r
\r
go\r
\r
drop table abc\r

moment                      first       last        hight       low         
 --------------------------- ----------- ----------- ----------- ----------- 
 
 2002 - 11 - 24   15 : 14 : 00 . 000       54            85            139           54 
 2002 - 11 - 24   15 : 15 : 00 . 000       97            27            124           27 
 2002 - 11 - 24   15 : 16 : 00 . 000       27            12            39            12 

 2002 - 11 - 24   15 : 17 : 00 . 000       5             72            77            5 
 2002 - 11 - 24   15 : 18 : 00 . 000       1             42            43            1 
 2002 - 11 - 24   15 : 19 : 00 . 000       30            97            127           30 

 2002 - 11 - 24   15 : 20 : 00 . 000       2             83            85            2 
 2002 - 11 - 24   15 : 21 : 00 . 000       61            20            81            20 
 2002 - 11 - 24   15 : 22 : 00 . 000       98            72            170           72 

                            first       last        hight       low         
 --------------------------- ----------- ----------- ----------- ----------- 
 
 2002 - 11 - 24   15 : 14 : 00 . 000       54            27            139           27 
 2002 - 11 - 24   15 : 16 : 00 . 000       27            42            77            1 
 2002 - 11 - 24   15 : 19 : 00 . 000       30            20            127           2 
 2002 - 11 - 24   15 : 22 : 00 . 000       98            72            170           72 

select min(t1.moment) as moment, 
min(t2.first) as first,
min(t3.last) as last,
max(t1.hight) as hight,
min(t1.low) as low
from abc t1 
join
(select * from abc where datediff(minute,@moment,moment)%@period= 0 ) t2
on 
datepart(minute,t1.moment)>=datepart(minute,t2.moment)
and
datepart(minute,t1.moment)<datepart(minute,t2.moment)+@period
join
(select * from abc where datediff(minute,@moment,moment)%@period=@period- 1 ) t3
on 
datepart(minute,t1.moment)>=datepart(minute,t3.moment)
and
datepart(minute,t1.moment)<datepart(minute,t3.moment)+@period

group by datediff(minute,@moment,t1.moment)/@period

declare @moment datetime
declare @period int
declare @delta int
select @moment=min(moment) from abc
set @period= 3 
set @delta=datediff(minute,convert(char( 8 ),@moment, 112 ),@moment)%@period
select @delta

select * from abc
select min(moment), 
(select top  1  [first] from abc b where (datepart(n,b.moment)-@delta)/@period = (datepart(n,a.moment)-@delta)/@period order by moment) as first,
(select top  1  [last] from abc b where (datepart(n,b.moment)-@delta)/@period = (datepart(n,a.moment)-@delta)/@period order by moment desc)as last, 
max([hight]) as hight,min([low])as low
from abc a
group by (datepart(n,moment)-@delta)/@period

moment                      first       last        hight       low         
 --------------------------- ----------- ----------- ----------- ----------- 
 
 2002 - 11 - 24   16 : 28 : 00 . 000       36            85            121           36 
 2002 - 11 - 24   16 : 29 : 00 . 000       37            57            94            37 
 2002 - 11 - 24   16 : 30 : 00 . 000       82            55            137           55 

 2002 - 11 - 24   16 : 31 : 00 . 000       56            29            85            29 
 2002 - 11 - 24   16 : 32 : 00 . 000       43            70            113           43 
 2002 - 11 - 24   16 : 33 : 00 . 000       49            25            74            25 

 2002 - 11 - 24   16 : 34 : 00 . 000       8             8             16            8 
 2002 - 11 - 24   16 : 35 : 00 . 000       8             95            103           8 
 2002 - 11 - 24   16 : 36 : 00 . 000       81            38            119           38 

 2002 - 11 - 24   16 : 37 : 00 . 000       94            52            146           52 

                            first       last        hight       low         
 --------------------------- ----------- ----------- ----------- ----------- 
 
 2002 - 11 - 24   16 : 28 : 00 . 000       36            55            137           36 
 2002 - 11 - 24   16 : 31 : 00 . 000       56            25            113           25 
 2002 - 11 - 24   16 : 34 : 00 . 000       8             38            119           8 
 2002 - 11 - 24   16 : 37 : 00 . 000       94            52            146           52