sql - How to find the average value for consecutive zeros? -


this table.

site_name | date& time              | poweroutput          ----------+-------------------------+------------------ act0001   | 2013-07-21 01:00:00.000 | 196.852984494331   act0001   | 2013-07-21 02:00:00.000 | 0    xyz0001   | 2013-07-21 03:00:00.000 | 196.852984494331    xyq0001   | 2013-07-21 04:00:00.000 | 196.958395639561  xys0001   | 2013-07-21 05:00:00.000 | 0 xyd0001   | 2013-07-21 06:00:00.000 | 197.20098185022  xye0001   | 2013-07-21 07:00:00.000 | 0  xyg0001   | 2013-07-21 08:00:00.000 | 0      cfg0001   | 2013-07-21 09:00:00.000 | 197.412144323522  acb0001   | 2013-07-21 10:00:00.000 | 0  bdf0001   | 2013-07-21 11:00:00.000 | 0  olk0001   | 2013-07-21 12:00:00.000 | 196.886233049016

i trying update values in places of zeros. if there 1 0 able update table, if there consecutive zeros finding difficult update table.

the logic :

((previous value-next value)/previous value)*100 <5

if true should insert previous value

((previous value-next value)/previous value)*100 >=5

if true should remain zero.

this code have far:

;with cte (     select           *,         lead(pr_output,1) on (order (select null)) previousvalue,         lag(pr_output,1) on (order (select null)) nextvalue             [dbo].[my_table] ) ,ctee (     select          *,         abs((previousvalue*100-nextvalue*100)/(case when nextvalue = 0 1 else nextvalue end)) checkflag             cte  ) select     site_name,[date&time],     case          when pr_output <>0 pr_output         else             case                  when checkflag >= 5 0                 else previousvalue             end     end pr_output      ctee

i unable update records in case of consecutive zeros.

expected output:

site_name | date& time              | poweroutput          ----------+-------------------------+------------------ act0001   | 2013-07-21 01:00:00.000 | 196.852984494331   act0001   | 2013-07-21 02:00:00.000 | 196.852984494331    xyz0001   | 2013-07-21 03:00:00.000 | 196.852984494331    xyq0001   | 2013-07-21 04:00:00.000 | 196.958395639561  xys0001   | 2013-07-21 05:00:00.000 | 196.958395639561 xyd0001   | 2013-07-21 06:00:00.000 | 197.20098185022  xye0001   | 2013-07-21 07:00:00.000 | 197.20098185022  xyg0001   | 2013-07-21 08:00:00.000 | 197.20098185022    cfg0001   | 2013-07-21 09:00:00.000 | 197.412144323522  acb0001   | 2013-07-21 10:00:00.000 | 197.412144323522 bdf0001   | 2013-07-21 11:00:00.000 | 197.412144323522 olk0001   | 2013-07-21 12:00:00.000 | 196.886233049016

thank you

if you're trying replace 0 values in poweroutput recent previous non-zero poweroutput value in cases where:

((previous value-next value)/previous value)*100 <5

then believe trick:

select  main.sitename,         main.[datetime],         case when main.poweroutput = 0              case when ((previousnonzero.poweroutput - nextnonzero.poweroutput) / previousnonzero.poweroutput) * 100 < 5                     previousnonzero.poweroutput                     else 0                  end             else main.poweroutput         end poweroutput [dbo].[my_table] main outer apply (     select top 1 poweroutput      [dbo].[my_table] prev      prev.[datetime] < main.[datetime]     , poweroutput <> 0     order prev.[datetime] desc ) previousnonzero outer apply (     select top 1 poweroutput      [dbo].[my_table] future     future.[datetime] > main.[datetime]     , poweroutput <> 0     order future.[datetime] ) nextnonzero order [datetime]

i'm not 100% sure whether this:

((previous value-next value)/previous value)*100 <5

is logic want use though. looks you're trying identify whether value changes more or less 5% previous non-zero value next one, work if changes occur in 1 direction, otherwise negative values (your sample dataset has values calculation ranging -0.23 0.27).

if want identify instances poweroutput value changing more 5% (in either direction), alter inner case statement in query above from:

case when ((previousnonzero.poweroutput - nextnonzero.poweroutput) / previousnonzero.poweroutput) * 100 < 5

to:

case when (abs(previousnonzero.poweroutput - nextnonzero.poweroutput) / previousnonzero.poweroutput) * 100 < 5

Comments

Post a Comment

Popular posts from this blog

sql server - Cannot query correctly (MSSQL - PHP - JSON) -

i have little problem on project. , turns out query result returns nothing when value of first name or last name has 'ñ' in it. here's code config.php: <?php ini_set('mssql.charset', 'utf-8'); header('content-type: text/html; charset=utf-8'); $servername = "192.168.1.21"; /* ip add of db server */ $connection = array("database" => "db", "uid" => "?", "pwd" => "***"); $conn = sqlsrv_connect($servername, $connection); if(!$conn){ echo "connection not established."; die(print_r(sqlsrv_errors(), true)); } ?> myquery: $idnumber = $_post["idnum"]; $response = array(); $query2 = "select clname, cfname, cmname, cqualifier student cidno = '$idnumber'"; try{ $stmt2 = sqlsrv_query($conn, $query2); } catch(pdoexeption $ex){ $response["success"] = 0; $response["message"] = "d...
Read more

php - trouble displaying mysqli database results in correct order -

i have table in database stores events , date expire. want display events on website display current events first expired events after? i have tried following sql $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; but returns expired results first due expirydate being ordered asc in query. my tbl_notice structure looks this noticeid => int(4) notice => varchar(100) noticedesc => text noticedate => timestamp noticeimg => varchar(40) expirydate => datetime expired => int(1) 0 current or 1 expired my php code is $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; $result = mysqli_query($conn,$sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { ...
Read more

C++ Linked List -

i trying create linked list download txt file , use linked list handle file line line. when handling downloaded linked list operations performed on such text editor would. encountering problems however. seems "node(string value)" section of code has wrong though original node() declaration no arguments passes. unable figure out quite is. node.h class node { public: node(); node(string value); void setnext(node *nextnode); // allows user set "next" pointer of node points friend class linkedlist; private: string data; // data box node* next; // pointer box }; node.cpp # include <string> # include "node.h" using namespace std; node::node() { data = ""; next = null; } node::node(string value) { data = value; next = null; } void node::setnext(node *nextnode) // allows user set "next" pointer of node points { this->next = nextnode; } your #include <string> ...
Read more