I am working with SQL Server 2008 R2 and new to relational database. I need to run a simple calculation but the calculation involves using a previous row value.
Example:
(Value of X) / ((Value of Y at time t + Value of Y at time t-1) / 2)
Example:
select (x/[(y@time,t + y@time,t-1)/2]) as 'Value'
from datatable
select ((c.ACHQ)/(c.RECTQ(row:n) + c.RETQ(row:n-1))/2) as 'AR'
from co_ifndq c
where c.GVKEY in
(select GVKEY
from spidx_cst
where DATADATE = '2012-03-12'
and INDEXID = '500')
and c.DATAFMT = 'std'
and c.DATADATE > '1990-12-30'
order by c.GVKEY, datadate desc
Cenas :
As I understand you want to make a calculation base on a date difference and not really on a row order, right?\n\nIf so, if you have a table like this\n\nCREATE TABLE YourTable(\n ACHQ float ,\n RECTQ float,\n DATE datetime)\n\nINSERT INTO YourTable VALUES (100,10,'20100101')\nINSERT INTO YourTable VALUES (200,20,'20110101')\nINSERT INTO YourTable VALUES (300,30,'20120101')\nINSERT INTO YourTable VALUES (400,40,'20130101')\nINSERT INTO YourTable VALUES (500,50,'20140101')\nINSERT INTO YourTable VALUES (600,60,'20150101')\n\n\nyou can do something like this\n\nSELECT\n ((c.ACHQ)/(c.RECTQ + cPreviousYear.RECTQ)/2) as 'AR'\nFROM\n YourTable c\n LEFT JOIN YourTable cPreviousYear\n ON YEAR(c.Date) - 1 = YEAR(cPreviousYear.Date)\n\n\nI simplified the calculation just to show that you can link the table to itself directly to the row with the wanted date difference and then calculate the value. you can even use ON DATEADD(y, -1, c.Date) = cPrevious.Date if you want the real date diference\n\nSorry if I missed the point.",
2012-03-22T12:04:48