one fancy query. Is it possible

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

one fancy query. Is it possible

Author

Topic

rkulseth
Starting Member

3 Posts

Posted - 2005-03-04 : 16:52:14

I have a database that holds price information for stocks in it. each line has the open high low close for a stock (symbol) a particular day (tradeDay) and a particular minute (min). I need to come up with a query that will take these one minute samples and sumarize them into N minute samples. so I have to take these records N units at a time and return one record for each subset, where openPx is the openPx of the first record in my subset of N records, closePx is the closePx of the last record in my subset, highPx is the highest of the highPx in my subset, lowPx is the lowest of the N lowPx's in my subset asd volume and ticks is the sum of their respective columns.

here is a subset of my data in 1 minute intervals before transformation

tradeDay    min  symbol  openPx   highPx    lowPx    closePx volume   ticks
3/4/2005    0    MSFT    25.2     25.2      25.19    25.2    3990     9
3/4/2005    1    MSFT    25.19    25.25     25.19    25.2    2300     6
3/4/2005    2    MSFT    25.19    25.2      25.18    25.2    1620     5
3/4/2005    3    MSFT    25.19    25.2      25.19    25.2    8315     21
3/4/2005    4    MSFT    25.19    25.2      25.19    25.2    790      5
3/4/2005    5    MSFT    25.2     25.2      25.17    25.2    4485     10
3/4/2005    6    MSFT    25.2     25.26     25.19    25.19   6923     12
3/4/2005    7    MSFT    25.19    25.2      25.19    25.1901 6588     12
 

Given this data I want a query that will return the following 2 four minute records

3/4/2005    0    MSFT    25.2      25.25    25.18    25.2    16225   41
3/4/2005    4    MSFT    25.19     25.26    25.17    25.1901 18786   39

I don't think it can be done in a single statement.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-04 : 17:41:42

>> I ~~don't~~ think it can be done in a single statement.
This is sqlteam.

create table #quote
(
	tradeDay datetime
	,[min] smallint
	,symbol varchar(8)
	,openPx numeric(11,4)
	,highPx numeric(11,4)
	,lowPx numeric(11,4)
	,closePx numeric(11,4)
	,volume int
	,ticks int
)

insert #quote
select '3/4/2005','0','MSFT','25.2','25.2','25.19','25.2','3990','9' union all
select '3/4/2005','1','MSFT','25.19','25.25','25.19','25.2','2300','6' union all
select '3/4/2005','2','MSFT','25.19','25.2','25.18','25.2','1620','5' union all
select '3/4/2005','3','MSFT','25.19','25.2','25.19','25.2','8315','21' union all
select '3/4/2005','4','MSFT','25.19','25.2','25.19','25.2','790','5' union all
select '3/4/2005','5','MSFT','25.2','25.2','25.17','25.2','4485','10' union all
select '3/4/2005','6','MSFT','25.2','25.26','25.19','25.19','6923','12' union all
select '3/4/2005','7','MSFT','25.19','25.2','25.19','25.1901','6588','12'


declare @N smallint
set @N = 4

select
	tradeDay
	,symbol
	,[min] as [min]
	,max(case when rem = 0 then openPx else -999 end) as openPx
	,max(highPx) as highPx
	,min(lowPx) as lowPx
	,max(case when rem = @N-1 then closePx else -999 end) as closePx
	,sum(volume) as volume
	,sum(ticks) as ticks
from
	(
		select tradeDay,@N*([min]/@N) as [min],[min]%@N as rem, symbol,openPx,highPx,lowPx,closePx,volume,ticks from #quote
	) as derivedquote
group by
	tradeDay
	,[min]
	,symbol
having
	count(*) = @N
order by
	tradeDay
	,[min]
	,symbol

drop table #quote

rockmoose

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-03-04 : 17:59:31

Ahhh!!!! Turn it off! Turn it off!!

Sorry having some bad flashbacks..

Computrac and Meta Stock... If you know what I am talking it about, I feel your pain/joy (depending on which side of the 87 crash you where on). Although Meta Stock was the gun program in the DOS days.... Damn thing wanted a math coprocessor in version 3.. Thank heavens for the 386DX chip..

DavidM

A front-end is something that tries to violate a back-end.

rkulseth
Starting Member

3 Posts

Posted - 2005-03-07 : 09:27:03

Wow!!! Maybe some day, I'll understand what the heck that is doing. Thank You for setteling my bet!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources