52 weeks hig/low

Question

Post reply

52 weeks hig/low

Olalekan Adewale

Right there with Babe

Points: 767
More actions
September 29, 2011 at 7:24 pm

#244612

Hi Good people,
I have a stock table. Kindly see below
select
INDEX_CODE
,INDEX_DATE
,[OPEN]
,HIGH
,LOW
,[CLOSE]
from
dbo.Market
I want to generate below additional fields on the table
- 52 week high
- 52 week low
- 24 week high
- 24 week low
- 12 week high
- 12 week low
This is base on Year(INDEX_DATE). i.e the fields will be done yearly.
Many thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Olalekan Adewale Right there with Babe Points: 767 More actions · Answer 1

Thanks Boss,

See below

DECLARE @Stock table (

INDEX_CODE varchar(20)

,INDEX_DATE date

,[OPEN] decimal(32,2)

,HIGH decimal(32,2)

,LOW decimal(32,2)

,[CLOSE] decimal(32,2)

)

insert into @Stock (INDEX_CODE,INDEX_DATE,[OPEN],HIGH,LOW,[CLOSE])

select 'AA', '2011/01/01', 2.0, 3.2,1.2,2.4 UNION ALL

select 'AB', '2011/01/01', 0.2, 1.1,0.1,0.1 UNION ALL

select 'AC', '2011/01/01', 5.0, 5.2,5.0,5.0 UNION ALL

select 'AA', '2011/01/02', 2.4, 2.4,2.4,2.4 UNION ALL

select 'AB', '2011/01/02', 0.1,2.0,1.2,1.0 UNION ALL

select 'AC', '2011/01/02', 5.0, 5.0,3.5,3.8

SELECT

INDEX_CODE

,INDEX_DATE

,[OPEN]

,HIGH

,LOW

,[CLOSE]

FROM

@Stock T1

Want to generate the following fields

-- 52 weeks high : from index_date to 52 weeks back get highest high

-- 52 weeks low : from index_date to 52 weeks back get lowest low

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Please will you show expected results for your sample data. It's not clear whether you're looking for the highest high or the highest high for each individual index code.

Thanks

John

R.P.Rozema SSChampion Points: 12307 More actions · Answer 3

I think below query does what you want: return the columns from @Stock, plus from all rows that have the same INDEX_CODE as that on the row and have an INDEX_DATE of at most 52 weeks before and not after the INDEX_DATE of that row, the highest value from [HIGH] and the lowest value from [LOW].

SELECT s1.INDEX_CODE

,s1.INDEX_DATE

,s1.[OPEN]

,s1.HIGH

,s1.LOW

,s1.[CLOSE]

--,history.[HIGH] as HIGHEST

--,history.[LOW] as LOWEST

FROM @Stock s1

outer apply (

select s2.INDEX_CODE,

max(s2.[HIGH]) as [HIGH],

min(s2.[LOW]) as [LOW]

from @Stock s2

where s2.INDEX_CODE = s1.INDEX_CODE

and s2.INDEX_DATE > dateadd(week, -52, s1.INDEX_DATE)

and s2.INDEX_DATE <= s1.INDEX_DATE

group by s2.INDEX_CODE

) history

order by s1.INDEX_CODE,

s1.INDEX_DATE

INDEX_CODEINDEX_DATEOPENHIGHLOWCLOSEHIGHESTLOWEST

AA2011-01-012.003.201.202.403.201.20

AA2011-01-022.402.402.402.403.201.20

AB2011-01-010.201.100.100.101.100.10

AB2011-01-020.102.001.201.002.000.10

AC2011-01-015.005.205.005.005.205.00

AC2011-01-025.005.003.503.805.203.50

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?