0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

declare @t table 
    (
    timestamp datetime,
    [open] int,
    high int,
    low int,
    [close] int
    )

insert @t
          select '2012-02-17 15:15:0', 102, 110, 100, 105
union all select '2012-02-17 15:16:0', 106, 112, 105, 107
union all select '2012-02-17 15:17:0', 106, 110, 98, 105
union all select '2012-02-17 15:18:0',105, 109, 104 ,106
union all select '2012-02-17 15:19:0',107, 112, 107 ,112
union all select '2012-02-17 15:20:0',115, 125, 115 ,124

        
select  min(timestamp)
,       max(high) as Highest
,       min(low) as Lowest
,       min(case when rn_asc = 1 then [open] end) as first
,       min(case when rn_desc = 1 then [close] end) as Last
from    (
        select  row_number() over (
                    partition by cast(cast(timestamp as float) * 24 * 12 as int)
                    order by timestamp) as rn_asc
        ,       row_number() over (
                    partition by cast(cast(timestamp as float) * 24 * 12 as int)
                    order by timestamp desc) as rn_desc
        ,       *
        from    @t
        ) as SubQueryAlias
group by
        cast(cast(timestamp as float) * 24 * 12 as int)

fork query

edited may 8 12 anonymous

created feb 18 12 jon.doe6270