mir safi mir safi - 26 days ago 8
SQL Question

how to update multiple values null using multiple case statements

This is my 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 this table and I am trying to update the values in places of "ZERO's".
If there is only one zero then I am able to update the table, but if there are consecutive zero's I am finding it difficult to update the table.

The logic is :


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

    If this is true then it should insert the previous value

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

    If this is true then it should remain as zero.



This is the code I have so far:

with cte as
(
SELECT
*,
lead(pr_output,1) OVER (ORDER BY (select null)) As PreviousValue,
lag(pr_output,1) OVER (ORDER BY (select null)) As NextValue
FROM
[dbo].[Mytable]
)
,ctee as
(
select
*,
abs((PreviousValue*100-NextValue*100)/NextValue) as CheckFlag
from
cte
)
select
Site_name,[DATE&Time],
case
when pr_output <>0 then pr_output
else
case
when CheckFlag >= 5 then 0
else PreviousValue
end
end as pr_output
from
ctee


The error I am getting is


Divide by zero error encountered.


Please tell me what am I doing wrong. If their is an alternate way of doing this through?

Any help is 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

Answer

Need to check if NextValue is zero before dividing by it..

Hope this solves your problem.

with cte as
(
    SELECT  
        *,
        lead(pr_output,1) OVER (ORDER BY (select null)) As PreviousValue,
        lag(pr_output,1) OVER (ORDER BY (select null)) As NextValue
    FROM
        [dbo].[Mytable]
)
,ctee as
(
    select 
        *,
        abs((PreviousValue*100-NextValue*100)/(case when NextValue = 0 then 1 else NextValue end)) as CheckFlag
    from
        cte 
)
select
    Site_name,[DATE&Time],
    case 
        when pr_output <>0 then pr_output
        else
            case 
                when CheckFlag >= 5 then 0
                else PreviousValue
            end
    end as pr_output
from 
    ctee
Comments