CODEWITHSUNDEEP
sqlsql-serverdatetimesql-update
BrawlX
BrawlX

Reputation: 3

Add a 0 in the next row of a column after the last data point

I have written a query which gives the output as shown below:

Date                           Amount
01-01-2020                          
01-02-2020                      10000
01-03-2020                      20000
01-04-2020                      30000
01-05-2020                      40000
01-06-2020                           
01-07-2020                           
01-08-2020

In the above table, we can see that the amount is null for 01-01-2020, 01-06-2020, 01-07-2020, 01-08-2020. Now, I want to add a 0 to the amount column for just 1 row i.e for the date- 01-06-2020 which is after the last data point - 40000. And I'm not sure how to do it. Is there any straight forward query to achieve this? Thank you.

Upvotes: 0

Views: 146

Answers (1)

GMB
GMB

Reputation: 222472

You can use lag() and a case expression:

select date,
    case when amount is null and lag(amount) over(order by date) is not null 
        then 0
        else amount
    end as amount
from mytable

If you wanted an update statement:

with cte as (
    select amount,
        case when amount is null and lag(amount) over(order by date) is not null 
            then 0
        end as new_amount
    from mytable
)
update cte set amount = new_amount where new_amount = 0

Upvotes: 1

Related Questions