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.
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 likeSELECT DATEADD(ss,DATEDIFF(ss,0,yourtimestamp),0) as [second slot], sum(sample) as totalfrom tablegroup by DATEADD(ss,DATEDIFF(ss,0,yourtimestamp),0) if you want to dynamically determine slot use belowhttp://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|