quote: Originally posted by Kristen "Just to reiterate what others have said: FIRST() makes no sense unless you also describe by-what-order the record considered to be "First" is defined.Kristen
Ok here is some sample data:create table StockQuotes( DateTimeStamp datetime not null, Price dec(9,4) not null, Volume int not null)create index IND_StockQuotes on StockQuotes (DateTimeStamp, Price, Volume)/* inserting: some tick by tick data */insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:000',121), 125.0, 50) --market opening--insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:000',121), 125.0, 150)insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:00:123',121), 125.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 09:00:01:456',121), 126.0, 75)insert into StockQuotes values (convert(datetime,'2006-10-01 09:04:25:789',121), 124.0, 600)-- insert into StockQuotes values (convert(datetime,'2006-10-01 09:05:00:000',121), 123.0, 300)insert into StockQuotes values (convert(datetime,'2006-10-01 09:06:00:000',121), 122.0, 300)insert into StockQuotes values (convert(datetime,'2006-10-01 09:07:00:000',121), 125.0, 200)insert into StockQuotes values (convert(datetime,'2006-10-01 09:08:00:000',121), 127.0, 400)insert into StockQuotes values (convert(datetime,'2006-10-01 09:09:00:000',121), 126.0, 200)-- insert into StockQuotes values (convert(datetime,'2006-10-01 09:10:00:000',121), 126.0, 100)insert into StockQuotes values (convert(datetime,'2006-10-01 09:11:00:000',121), 126.0, 100)insert into StockQuotes values (convert(datetime,'2006-10-01 09:12:00:000',121), 127.0, 200)insert into StockQuotes values (convert(datetime,'2006-10-01 09:13:00:000',121), 125.0, 400)insert into StockQuotes values (convert(datetime,'2006-10-01 09:14:00:000',121), 124.0, 100)-- insert into StockQuotes values (convert(datetime,'2006-10-01 09:15:00:000',121), 124.0, 100)insert into StockQuotes values (convert(datetime,'2006-10-01 09:16:00:000',121), 123.0, 100)insert into StockQuotes values (convert(datetime,'2006-10-01 09:17:00:000',121), 124.0, 200)insert into StockQuotes values (convert(datetime,'2006-10-01 09:18:00:000',121), 125.0, 400)insert into StockQuotes values (convert(datetime,'2006-10-01 09:19:00:000',121), 126.0, 100)-- Hourlyinsert into StockQuotes values (convert(datetime,'2006-10-01 10:00:00:000',121), 125.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 10:15:00:000',121), 121.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 10:30:00:000',121), 124.0, 125)insert into StockQuotes values (convert(datetime,'2006-10-01 10:45:00:000',121), 126.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 10:59:59:999',121), 125.0, 325)-- insert into StockQuotes values (convert(datetime,'2006-10-01 11:00:00:000',121), 125.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 11:15:00:000',121), 124.0, 300)insert into StockQuotes values (convert(datetime,'2006-10-01 11:30:00:000',121), 123.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 11:45:00:000',121), 122.0, 425)insert into StockQuotes values (convert(datetime,'2006-10-01 11:59:59:999',121), 121.0, 325)-- insert into StockQuotes values (convert(datetime,'2006-10-01 12:00:00:000',121), 120.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 12:15:00:000',121), 119.0, 625)insert into StockQuotes values (convert(datetime,'2006-10-01 12:30:00:000',121), 118.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 12:45:00:000',121), 117.0, 300)insert into StockQuotes values (convert(datetime,'2006-10-01 12:59:59:999',121), 116.0, 325)-- insert into StockQuotes values (convert(datetime,'2006-10-01 13:00:00:000',121), 115.0, 325)insert into StockQuotes values (convert(datetime,'2006-10-01 13:15:00:000',121), 114.0, 225)insert into StockQuotes values (convert(datetime,'2006-10-01 13:30:00:000',121), 113.0, 300)insert into StockQuotes values (convert(datetime,'2006-10-01 13:45:00:000',121), 112.0, 825)insert into StockQuotes values (convert(datetime,'2006-10-01 13:59:59:999',121), 111.0, 325)-- insert into StockQuotes values (convert(datetime,'2006-10-01 14:00:00:000',121), 110.0, 123)insert into StockQuotes values (convert(datetime,'2006-10-01 14:15:00:000',121), 109.0, 432)insert into StockQuotes values (convert(datetime,'2006-10-01 14:30:00:000',121), 110.0, 345)insert into StockQuotes values (convert(datetime,'2006-10-01 14:45:00:000',121), 111.0, 456)insert into StockQuotes values (convert(datetime,'2006-10-01 14:59:59:999',121), 110.0, 756)-- insert into StockQuotes values (convert(datetime,'2006-10-01 15:00:00:000',121), 109.0, 234)insert into StockQuotes values (convert(datetime,'2006-10-01 15:15:00:000',121), 110.0, 432)insert into StockQuotes values (convert(datetime,'2006-10-01 15:30:00:000',121), 110.0, 234)insert into StockQuotes values (convert(datetime,'2006-10-01 15:45:00:000',121), 111.0, 456)insert into StockQuotes values (convert(datetime,'2006-10-01 15:59:59:999',121), 110.0, 765)-- insert into StockQuotes values (convert(datetime,'2006-10-01 16:00:00:000',121), 111.0, 123)insert into StockQuotes values (convert(datetime,'2006-10-01 16:15:00:000',121), 112.0, 432)insert into StockQuotes values (convert(datetime,'2006-10-01 16:30:00:000',121), 113.0, 543)insert into StockQuotes values (convert(datetime,'2006-10-01 16:45:00:000',121), 114.0, 765)insert into StockQuotes values (convert(datetime,'2006-10-01 16:59:59:999',121), 115.0, 575) --market closing---- Dailyinsert into StockQuotes values (convert(datetime,'2006-10-02 09:00:00:000',121), 114.0, 654) --market opening--insert into StockQuotes values (convert(datetime,'2006-10-02 10:00:00:000',121), 113.0, 456)insert into StockQuotes values (convert(datetime,'2006-10-02 11:00:00:000',121), 114.0, 465)insert into StockQuotes values (convert(datetime,'2006-10-02 12:00:00:000',121), 115.0, 356)insert into StockQuotes values (convert(datetime,'2006-10-02 13:00:00:000',121), 116.0, 543)insert into StockQuotes values (convert(datetime,'2006-10-02 14:00:00:000',121), 117.0, 234)insert into StockQuotes values (convert(datetime,'2006-10-02 15:00:59:999',121), 118.0, 243)insert into StockQuotes values (convert(datetime,'2006-10-02 16:59:59:999',121), 117.0, 134) --market closing---- insert into StockQuotes values (convert(datetime,'2006-10-03 09:00:00:000',121), 117.0, 534) --market opening--insert into StockQuotes values (convert(datetime,'2006-10-03 10:00:00:000',121), 116.0, 354)insert into StockQuotes values (convert(datetime,'2006-10-03 11:00:00:000',121), 117.0, 756)insert into StockQuotes values (convert(datetime,'2006-10-03 12:00:00:000',121), 118.0, 876)insert into StockQuotes values (convert(datetime,'2006-10-03 13:00:00:000',121), 119.0, 567)insert into StockQuotes values (convert(datetime,'2006-10-03 14:00:00:000',121), 120.0, 467)insert into StockQuotes values (convert(datetime,'2006-10-03 15:00:59:999',121), 121.0, 466)insert into StockQuotes values (convert(datetime,'2006-10-03 16:59:59:999',121), 122.0, 342) --market closing---- insert into StockQuotes values (convert(datetime,'2006-10-04 09:00:00:000',121), 120.0, 534) --market opening--insert into StockQuotes values (convert(datetime,'2006-10-04 10:00:00:000',121), 119.0, 354)insert into StockQuotes values (convert(datetime,'2006-10-04 11:00:00:000',121), 121.0, 234)insert into StockQuotes values (convert(datetime,'2006-10-04 12:00:00:000',121), 124.0, 876)insert into StockQuotes values (convert(datetime,'2006-10-04 13:00:00:000',121), 129.0, 234)insert into StockQuotes values (convert(datetime,'2006-10-04 14:00:00:000',121), 129.0, 467)insert into StockQuotes values (convert(datetime,'2006-10-04 15:00:59:999',121), 128.0, 235)insert into StockQuotes values (convert(datetime,'2006-10-04 16:59:59:999',121), 127.0, 342) --market closing-- First and last in my case means the first and last tick in a given period (according to the DateTimeStamp column) For different periods I need to group the data and extract: total volume, highest- lowest- open- and close prices.The different periods are (weekly - daily - hourly -15min - 5min – 1min), but if I can do it for one period, the other must be relative simple.So for daily periods the expected output must look like this:Daily PriceHigh PriceLow PriceOpen PriceClose TotVolume 2006-10-01 127.0000 109.0000 125.0000 115.0000 180962006-10-02 118.0000 113.0000 114.0000 117.0000 30852006-10-03 122.0000 116.0000 117.0000 122.0000 43622006-10-04 129.0000 119.0000 120.0000 127.0000 3276 Any attempt to access the aggregate set in a sub select failed, but I am not so good with SQL so maybe I‘m overlooking something hire. This is one of the things I tried:/* Daily periods */select Convert(Varchar(10), DateTimeStamp, 120) as DailyDate, Max(Price) as PriceHigh, Min(Price) as PriceLow, (select top 1 Price from StockQuotes where DateTimeStamp = DailyDate order by DailyDate) as PriceOpen, (select top 1 Price from StockQuotes where DateTimeStamp = DailyDate order by DailyDate desc) as PriceClose, Sum(Volume) as TotVolumefrom StockQuotesgroup by Convert(Varchar(10), DateTimeStamp, 120)order by Convert(Varchar(10), DateTimeStamp, 120)/* Daily periods */select Convert(Varchar(10), DateTimeStamp, 120) as DailyDate, Max(Price) as PriceHigh, Min(Price) as PriceLow, (select top 1 Price from StockQuotes where DateTimeStamp = (select Min(DateTimeStamp) from StockQuotes)) as PriceOpen, (select top 1 Price from StockQuotes where DateTimeStamp = (select Max(DateTimeStamp) from StockQuotes)) as PriceClose, Sum(Volume) as TotVolumefrom StockQuotesgroup by Convert(Varchar(10), DateTimeStamp, 120)order by Convert(Varchar(10), DateTimeStamp, 120) If anybody has a need way of solving this I would be very grateful.mvgKees |