Dynamic data selection (Solved)

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

General SQL Server Forums

New to SQL Server Programming

Dynamic data selection (Solved)

Author

Topic

puvpul
Starting Member

6 Posts

Posted - 2011-10-11 : 07:15:07

Dear Experts,
I’m trying to create view/stored procedure that would subtract current quarter value to the previous quarter value. Few of the data for current quarter value are not available. In this case it will subtract previous quarter to the previous one. example are shown below:
CREATE TABLE profitq (
ticker varchar(16) NOT NULL,
year int NOT NULL,
pq1 decimal(12,2) DEFAULT NULL,
pq2 decimal(12,2) DEFAULT NULL,
pq3 decimal(12,2) DEFAULT NULL,
pq4 decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,year)
)
go
INSERT profitq
VALUES
('ABBANK', 2010, '20', '25', null, null),
('ALARA', 2010, '10', null, null, null),
('BASIA', 2010, '20', null, null, null)
go
CREATE TABLE profitq10 (
ticker varchar(16) NOT NULL,
year int NOT NULL,
pq1 decimal(12,2) DEFAULT NULL,
pq2 decimal(12,2) DEFAULT NULL,
pq3 decimal(12,2) DEFAULT NULL,
pq4 decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,year)
)
go
INSERT profitq10
VALUES
('ABBANK', 2010, '20', '25', '10', '15'),
('ALARA', 2010, '10', '15', '12', '6'),
('BASIA', 2010, '20', '30', '40', '50')
The view I’ve created is following:

select ticker,
((pq2/pq1)-1) as quartergrowth
from profitq

current quarterly data for few companies is not available yet. In this case I need to perform the calculation like
select a.ticker,
((a.pq1/b.pq4)-1) as qurtergrowth
From profitq a
Join profitq10 b
On a.ticker=b.ticker;
[formula-- (currentquarteramount/previousquarteramount)-1]
Can you please help me on this. i'm using sql server 2008. thx in advance
Regards
Wakil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 07:19:01

[code]
select a.ticker,
(coalesce((a.pq2/a.pq1),(a.pq1/b.pq4))-1) as qurtergrowth
From profitq a
left Join profitq10 b
On a.ticker=b.ticker;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

puvpul
Starting Member

6 Posts

Posted - 2011-10-11 : 07:53:41

Thx a lot visakh16. That solved my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 12:22:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources