Solved

Calculating Integral Function by SQL sentence.

Posted on 2000-02-16
7
1,528 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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 to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 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