Creating a specific table - 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

General SQL Server Forums

New to SQL Server Programming

Creating a specific table

Author

Topic

MartinFalch
Starting Member

5 Posts

Posted - 2009-07-27 : 08:14:44

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)
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 with red. 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

MartinFalch
Starting Member

5 Posts

Posted - 2009-07-27 : 09:55:54

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 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources