July 27, 2009 at 6:27 am
Hi there,
I'm working on a project that I've picked up again after some time. The data had to be altered slightly, so some of the original tables I used were replaced with new data, though used the same name.
Now I wish to calculate the same data as before using these new tables, but I find myself having trouble getting the originally created tables re-created.
Short summary of computations:
- I have two data-tables, SP and TY.
- I wish to calculate some returns from these (thus listing them with their values along with their values "lagged 1 time" in order to calculate the returns from this)
- Via these returns I'll calculate some key-numbers.
The problem:
- During these computations, I recall creating a view last time, which was used in the proces of creating a new table, combining the original data-tables. This table is called ReturnTotal and is the one I'm having trouble creating. The code for the whole thing looks as follows:
----------------------------------
Select * From SP
inner join TY
on SPDATE = TYDATE and SPTIME = TYTIME
select * from vwspty
Select * into SPTY from vwspty
Select a.*, b.ObsTime as PrevObsTime, b.SPOPEN as PrevSPOPEN, b.SPCLOSE as PrevSPCLOSE, b.TYOPEN as PrevTYOPEN, b.TYCLOSE as PrevTYCLOSE, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn, log(a.TYCLOSE)-log(b.TYCLOSE) as TYreturn
into ReturnTotal
from spty a ,spty b
where b.obstime=(select max(obstime) from spty where obstime < a.obstime)
[/b]
Select * From ReturnTotal
where datediff(hour, PrevObsTime, ObsTime) < 8
b
select *, covSPTY/sqrt(RVSP*RVTY) as CorSPTY from
(
Select
sum(SPreturn*SPreturn) as RVSP,
sum(TYreturn*TYreturn) as RVTY,
sum(SPReturn*TYreturn) as CovSPTY,
count(*) as AntalObs
From ReturnTotal
--where datediff(hour, PrevObsTime, ObsTime) < 9
) a
log
/*
Select 'SP', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
(Select SPDate, count(*) as cnt from SP
group by SPdate) c
Union
Select 'TY', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
(Select TYDate, count(*) as cnt from TY
group by TYdate) c
Union
Select 'SPTY', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
(Select ObsDate, count(*) as cnt from SPTY
group by Obsdate) c
--update SPTY
--Set ObsDate = convert(varchar, obstime, 112)
--Select * From SPTY
*/
Select top 20 *, convert(varchar, obstime, 120), convert(varchar, prevobstime, 120) from ReturnTotal order by obstime desc
select * from vwSPTY
---------------------------------
- vwspty is the view I created for the purpose.
- I've highlighted the command I'm trying to pull off currently above. When trying to process it, I get a "Executing Query" for 20 min +, which didn't happen last time for sure.
- I don't know if this is enough information for anyone to be able to give a hint at what I might be missing (as you can probably guess, I'm very new at this), but I'd be truly grateful if anyone could give me some directions as to how I can get this working. Thank you very much,
Martin Falch
July 27, 2009 at 7:55 am
I might add that executing the code seems to work when adding for instance "Top 10" after the SELECT. However, executing the whole thing takes way longer than I've experienced before. Is this perhaps a Primary Key issue or something like that? I've set ObsTme and ObsDate as primary keys in the SPTY table, same thing for the SP and TY separate tables.
Is there something that I might be missing that rapidly increases the speed at which the table-creation is executed?
Thanks in advance 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply