I have tried using a piece of query-code that I previously used with no problems. The code creates a new table, based on a previous table and looks like this:
Select a.*, b.obstime as PrevObsTime, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn
from SP a, SP b
where b.obstime=(select max(obstime) from SP where obstime<a.obstime)
When I try this for top 10 or top 100, it works absolutely fine and I know that it worked in the past as well with tables as large as the SP table, which I'm basing this on. However, when I try it now, I get an error message like this:
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'Job2' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I guess this may be due to the fact that I'm using the free version of SQL server 2005. When I try to look at the properties of my database, it says that it has reached the maximum initial size of 4096 MB, but this happens also if I try the code in other databases - then they go from initial size of e.g. 12 MB to the 4096...
Does anyone have a clue on what I should do here to get this to work? It'd be a great help to me and I would appreciate it alot!
Well, the number of rows are approximately 400.000, but they were the same in the previous tables I worked with, where I had no problems.
What I need to do is the following:
1) I'm working with intra-day data from the S&P500. I have a table with the Date, TimeOfDay and the ClosePrice. This is the "SP" table.
2) Basically I need a new table where the returns of each of the 5-minute interval price observations are computed, which is why the code sets up a lagged version of the table for using as the "previous" price-observation in the computation of a return.
That's pretty much it - so basically I just need to compute the returns, but I don't know if there's a smarter way to do this? I'm not familiar with the INSERT syntax and how it works, I'm afraid.
The version of my SQL server 2005 (free version) is 9.00.3042.00