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")
while(not Rs.EOF)
   Result = Result + PrevY * (Rs.Fields("CoorX")-PrevX)
   PrevX = Rs.Fields("CoordX")
   PrevY = Rs.Fields("CoordY")
while end
Who is Participating?
wqwConnect With a Mentor 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 =

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



p.s. Hope this is "Pure" enough ;-)
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)
      select @Result=@Result+@PrevY*(@CoordX-@PrevX)
      select @PrevX=@CoordX
      select @PrevY=@CoordY
      fetch next from mycursor into @CoordX, @CoordY
select @Result as 'Result'
close mycursor
deallocate mycursor
kalle73Author Commented:
Thank you very much.

But I new about it. What I need is "pure" SQL. Can you help me?
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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

kalle73Author Commented:
wqw, you're welcome.
kalle73Author Commented:
Thanks a lot.
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.

All Courses

From novice to tech pro — start learning today.