How calc change in value over several records for different categories

Question

Post reply

How calc change in value over several records for different categories

Digs

SSCertifiable

Points: 7333
More actions
April 10, 2013 at 10:35 pm

#292309

Test data
IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;
create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))
INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80)
INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48)
INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80)
CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)
SELECT *
FROM #priceData
ORDER By Symbol, quote_date
IF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;
I am trying to get this outcome...
AAPL-0.681 day Change-15.23 day change-16.176 day change
MSFT50.321 day Change62.83 day change34.836 day change
1 day change is AAPL is -0.68 is [2091104] 534.80 less [20091103] 535.48 = -0.68
3 day change is AAPL is -15.2 is [2091104] 534.80 less [20091030] 550.00 = -15.2
Any ideas how to do this?:-)

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

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

Mansfield SSCrazy Points: 2396 More actions · Answer 1

A couple of self-joins should do, something like that:

selectpt.symbol,

pt.quote_price - p3.quote_price as change_3_day,

pt.quote_price - p6.quote_price as change_6_day

from pricedata pt

left join pricedata p3 on p3.symbol = pt.symbol and p3.quote_date = dateadd(d, -3, @today)

left join pricedata p6 on p6.symbol = pt.symbol and p6.quote_date = dateadd(d, -6, @today)

where pt.quote_date = @today

Digs SSCertifiable Points: 7333 More actions · Answer 2

IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

create table #priceData(

[ID] [int] NULL,

[Symbol] [varchar](50) NULL,

[quote_date] [datetime] NULL,

[close_price] [decimal](18, 2) NULL

);

INSERT INTO #priceData (ID, symbol, quote_date, close_price) VALUES (8,'AAPL','20091026', 555.75)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (7,'AAPL','20091027', 550.97)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (6,'AAPL','20091028', 547.87)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (5,'AAPL','20091029', 543.01)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (4,'AAPL','20091030', 550.00)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (3,'AAPL','20091102', 537.08)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (2,'AAPL','20091103', 535.48)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (1,'AAPL','20091104', 534.80)

INSERT INTO #priceData (ID, symbol, quote_date, close_price) VALUES (8,'MSFT','20091026', 555.75)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (7,'MSFT','20091027', 550.97)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (6,'MSFT','20091028', 547.87)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (5,'MSFT','20091029', 543.01)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (4,'MSFT','20091030', 523.00)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (3,'MSFT','20091102', 537.08)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (2,'MSFT','20091103', 535.48)

INSERT INTO #priceData (ID, symbol, quote_date, close_price ) VALUES (1,'MSFT','20091104', 585.80)

CREATE CLUSTERED INDEX ix_goog on #priceData(ID)

--SELECT *

--FROM #priceData

--ORDER By Symbol, quote_date

selectpt.symbol,

pt.close_price AS 'Current',

p3.close_price AS '1 day back',

p6.close_price AS '6 day back',

pt.close_price - p3.close_price as change_3_day,

pt.close_price - p6.close_price as change_6_day

from #pricedata pt

left join #pricedata p3 on p3.symbol = pt.symbol and p3.ID = 2

left join #pricedata p6 on p6.symbol = pt.symbol and p6.ID = 6

where pt.ID = 1

IF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;

Thanks

Your idea, helped me produce the above desired result

AAPL534.80535.48547.87-0.68-13.07

MSFT585.80535.48547.8750.3237.93

Mansfield SSCrazy Points: 2396 More actions · Answer 3

One thing to consider also: Will there be "holes" in your data, e.g. a company does not have a stock quote for a day due to a trading halt or something.

If it won't happen then the LEFT JOIN can be substituted with an ordinary JOIN.