Solved

# Calculating Integral Function by SQL sentence.

Posted on 2000-02-16
1,422 Views
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
0
Question by:kalle73
• 3
• 2
• 2

LVL 7

Expert Comment

ID: 2526821
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 Comment

ID: 2541815
Thank you very much.

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

LVL 7

Expert Comment

ID: 2541873
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

LVL 4

Expert Comment

ID: 2545632
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 Comment

ID: 2545812
wqw, you're welcome.
0

LVL 4

Accepted Solution

wqw earned 110 total points
ID: 2545856
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

Author Comment

ID: 2590420
Thanks a lot.
0

## Featured Post

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.