How to find continuous zero in a column?

Question

How do I find continuous zero number(0) in a column and stop when I find a non-zero. Then I need to replace the all the zeroes with the non-zero number.

1. The grouping will be done by column 1
2. After we get a non zero value, for eg - ABC, replace all the values above it with that non zero value.
3. The arrangement in a group will be done by descending order of date.

Please tell me how to update the zero values with the non-zero number.

Answer 1

Try this

DECLARE @T TABLE
(
    Col1 VARCHAR(5),
    Col2 INT,
    Col3 DATE
)

INSERT INTO @T
values( 'ABC',0,'01-01-2017'),('ABC',0,'01-01-2017'),('ABC',0,'01-01-2014'),('ABC',3,'01-01-2013'),
('DEF',0,'01-01-2017'),('DEF',2,'01-01-2017'),('DEF',0,'01-01-2014')

;WITH CTE
AS
(
SELECT
    SeqNo = ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2,Col3),
    RN = ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 DESC),
    *
    FROM @T
)
SELECT
    T1.Col1,
    NewCol2 = ISNULL(T2.Col2,T1.Col2),
    OldCol2 = T1.Col2,
    T1.Col3 
    FROM CTE T1
        LEFT JOIN CTE T2
            ON T1.Col1 = t2.Col1
                AND T1.Col2 = 0
                AND T2.RN =1

This is the output

Program Logic :

• It Creates a sequence for each Col1 based on the Col2 and col3.
• For every record with Col2 as 0, it does a lookup on the same table for records having same value in col1 and Rank = 1 ( Rank 1 will be the record with values <> 0 )
• if a match is found based on the above, matching value is displayed else the value is displayed as in the column

Answer 2

You can try this solution, but you will also need an idcolumn (auto-increment i recommend):

declare @tbl as table (
    id int
    ,column1 varchar(5)
    ,column2 int
)

insert into @tbl values (1, 'ABC', 0)
insert into @tbl values (2, 'DEF', 0)
insert into @tbl values (3, 'GHI', 0)
insert into @tbl values (4, 'JKL', 3)
insert into @tbl values (5, 'GHI', 0)
insert into @tbl values (6, 'JKL', 0)
insert into @tbl values (7, 'JKL', 4)

SELECT
    id
    ,column1
    ,CASE WHEN
        column2 = 0 THEN
            (SELECT TOP 1 column2 
            FROM @tbl <-- here you query the table again
            WHERE id > T.id <-- based on this condition you are looking onto the next row (as explained below)
                AND column2 <> 0
            ORDER BY id)
        ELSE column2
    END AS column2
FROM @tbl T <-- Here is T; is the alias of the table

UPDATE

---

If your column2 = 0 the subquery looks into your table and takes the first non-zero value it finds.

I was telling you about the id column cause you need a sub-query from the same table, with this condition WHERE id > T.id the query looks on the next row, if it has a non-zero value will return it, otherwise will look at the next row and so on ...

So for example when id = 1 the subquery search in your table search for an id > 1 to take the first non-zero value it finds; in this case the subquery will find the record with id = 4 and returns the value 3 and the CASE statement will do the rest.