sql - how to update multiple values null using multiple case statements -
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 have table , trying update values in places of "zero's". if there 1 0 able update table, if there consecutive zero's 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].[mytable] ) ,ctee ( select *, abs((previousvalue*100-nextvalue*100)/nextvalue) 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
the error getting
divide 0 error encountered.
please tell me doing wrong. if alternate way of doing through?
any appreciated.
output should be:
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 | 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
need check if nextvalue 0 before dividing it..
hope solves problem.
with cte ( select *, lead(pr_output,1) on (order (select null)) previousvalue, lag(pr_output,1) on (order (select null)) nextvalue [dbo].[mytable] ) ,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
Comments
Post a Comment