# Calculating Integral Function by SQL sentence.

I have a table that contains two fields: CoordX and CoordY. I'd like to calculate an integral function using an SQL
sentence and not a script. One can describe this function by the following script:

Result = 0
Set Rs = OpenRecordset("SELECT * FROM Table ORDER BY CoordX")
PrevX = Rs.Fields("CoordX")
PrevY = Rs.Fields("CoordY")
Rs.MoveNext()
while(not Rs.EOF)
Result = Result + PrevY * (Rs.Fields("CoorX")-PrevX)
PrevX = Rs.Fields("CoordX")
PrevY = Rs.Fields("CoordY")
Rs.MoveNext()
while end
###### Who is Participating?

Commented:
Here's what i've come up with:

if object_id('tempdb..#tmp') > 0
drop table #tmp

create table #tmp (ID int identity(1,1), CoordX int, CoordY int)

insert #tmp(CoordX, CoordY)
select CoordX, CoordY
from <YourTableHere>
order by <OrderColumnHereWhichYouMustHaveAnyway>

select  sum(t2.CoordY * (t1.CoordX -t2.CoordX))
from #tmp t1 join #tmp t2 on t1.id = t2.id+1

The idea is to let SQL Server generate sequence numbers through identity column of a temp table and to self join temp table is a shrewd manner

HTH,

</wqw>

p.s. Hope this is "Pure" enough ;-)
0

Commented:
Here is a TSQL Code which calculates what you want. Put it in a stored procedure. When you execute this stored procedure it will return a recordset with one row and one column, containing the result

use pubs
declare mycursor cursor for select CoordX,CoordY from table3 order by CoordX
open mycursor
declare @CoordX float, @CoordY float
declare @PrevX float, @PrevY float
declare @Result float
select @Result=0
fetch next from mycursor into @PrevX, @PrevY
fetch next from mycursor into @CoordX, @CoordY
while (@@FETCH_STATUS=0)
begin
select @Result=@Result+@PrevY*(@CoordX-@PrevX)
select @PrevX=@CoordX
select @PrevY=@CoordY
fetch next from mycursor into @CoordX, @CoordY
end
select @Result as 'Result'
close mycursor
deallocate mycursor
0

Author Commented:
Thank you very much.

But I new about it. What I need is "pure" SQL. Can you help me?
0

Commented:
What do you meen by "Pure" SQL?

If you want a single select statement then you can't do it.

And by the way you can execute the above stored procedure inm a single SQL Statement and get the result
0

Commented:
Reject the answer and i'll give you my "Pure" SQL solution.

Have to get some points here. Tchalkov is so far ahead of me. :-)))

</wqw>
0

Author Commented:
wqw, you're welcome.
0

Author Commented:
Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.