Solved

Calculating Integral Function by SQL sentence.

Posted on 2000-02-16
7
1,632 Views
Last Modified: 2011-09-20
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
Comment
Question by:kalle73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:tchalkov
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

by:kalle73
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

by:tchalkov
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 4

Expert Comment

by:wqw
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

by:kalle73
ID: 2545812
wqw, you're welcome.
0
 
LVL 4

Accepted Solution

by:
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

by:kalle73
ID: 2590420
Thanks a lot.
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question