52 Weeks High/Low.. - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

52 Weeks High/Low..

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-09-29 : 21:23:00

Hi Good,

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.

I sign for fame not for shame but all the same, I sign my name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-29 : 21:37:04

what is the definition of your week ?

KH
[spoiler]Time is always against us[/spoiler]

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-09-29 : 21:42:50

Thanks khtan,

It's week in the year.. just like saying max(High) between week 1 and week 52.

Thanks.

I sign for fame not for shame but all the same, I sign my name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-29 : 21:48:07

i mean your week starts from Sunday ? Monday ?

week 1 is 1st of the year or first Sunday / Monday of the year ?

KH
[spoiler]Time is always against us[/spoiler]

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-09-29 : 21:50:27

It's starts from Monday. No trading on saturday and sunday.

Many Thanks.

I sign for fame not for shame but all the same, I sign my name.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-09-30 : 06:37:57

All, Kindly see my question well framed..

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

Many thanks

I sign for fame not for shame but all the same, I sign my name.

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources