• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1885
  • Last Modified:

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
0
kalle73
Asked:
kalle73
  • 3
  • 2
  • 2
1 Solution
 
tchalkovCommented:
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
 
kalle73Author Commented:
Thank you very much.

But I new about it. What I need is "pure" SQL. Can you help me?
0
 
tchalkovCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now