Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Cursor with Update and Order By clause

Author  Topic 

Armand1
Starting Member

1 Post

Posted - 2011-08-28 : 19:50:48
Hi everyone,

I know that this is a know issue, and there is maybe another way to do it but I haven't find it.

Let me explain:
I have a table that contains time stamped values, which is pretty basic. Except the system is not recording each samples at the same rate. It add a new row only when the value is different to get the DB as small as possible.

So with that, I need to get a sum of the samples on a second basis. So I used a cursor to determine from the previous row what was the time in between the 2 values and multiply with the sample value.

The second one is a Median Value (order in sample value) and get the median (for example the 50st out of 100)... So I had to create 2 cursors, 1 to do the same as before by processing the DB in order by time and a second to process the table ordered by sample value... The only problem is I had to update the table with those information to retreive at the end the median value... And I cannot use the ORDER BY with the UPDATE statement.... I tried without order by but the data are not sorted...

Can you help me please?
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 00:33:38
if you want sum based on second basis cant you do something like

SELECT DATEADD(ss,DATEDIFF(ss,0,yourtimestamp),0) as [second slot], sum(sample) as total
from table
group by DATEADD(ss,DATEDIFF(ss,0,yourtimestamp),0)


if you want to dynamically determine slot use below

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -