I need to identify the time spent by each team in column x and column y using date column. To get that I'm working on backward filling of column x and forward filling of column y so that I can take difference between start time by each team and end time.

• Column X has to backward fill till a previous value within same number, note: it should not apply to another number.

• Column Y has to forward fill till a next value within same number, note: it should not apply to another number.

Need help in forward filling and backward filling for data from table.

This how data looks now.

How data looks after running query should be - Column X Backward & Y Forward

Here column X has to back fill till previous value and it should be within same number and Column Y has to forward fill till next value within same number.

hope somebody will be able to help me out with same scenario.

CodePudding user response：

You can do it with a scalar subquery. Unfortunately only Azur version of Sql Server supports IGNORE NULLS option in first_value() function.

select t1.* 
    , coalesce(Column_X, 
       (select top(1) t2.Column_X 
        from mytable t2 
        where t2.Number = t1.Number and t2.Column_X is not null and t2.Sl_NO > t1.Sl_NO order by Sl_NO)) x2
    , coalesce(Column_Y,
       (select top(1) t2.Column_Y 
        from mytable t2 
        where t2.Number = t1.Number and t2.Column_X is not null and t2.Sl_NO < t1.Sl_NO order by Sl_NO desc)) y2
from mytable t1

CodePudding user response：

This was posted by lptr as a comment. I am posting it as a community wiki answer to preserve it, in the event the comment is deleted again.

Here they use a derived table to perform windowed aggregates, which perform "cumulative" counts. Then they use that dervived table to get the MAX values, which are again windowed.

create table t(Number int, Date int, Sl_NO int, Column_X char(1), Column_Y char(1))

insert into t(Number, Date, Sl_No, Column_X, Column_Y)
values
(1, 44563, 1, NULL, NULL),
(1, 44563, 2, 'A', 'B'),
(1, 44563, 3, NULL, NULL),
(1, 44563, 4, NULL, NULL),
(1, 44563, 5, NULL, NULL),
(1, 44563, 6, NULL, NULL),
(1, 44563, 7, 'C', 'D'),
(1, 44563, 8, NULL, NULL),
(1, 44563, 9, 'E', 'F'),
(1, 44563, 10, NULL, NULL),
(1, 44563, 11, NULL, NULL),
(1, 44563, 12, NULL, NULL),

(2, 44563, 1, NULL, NULL),
(2, 44563, 2, 'A', 'B'),
(2, 44563, 3, NULL, NULL),
(2, 44563, 4, NULL, NULL),
(2, 44563, 5, 'C', 'D'),
(2, 44563, 6, NULL, NULL),
(2, 44563, 7, 'E', 'B'),
(2, 44563, 8, NULL, NULL);

select Number, Date, Sl_No, 
    Column_X,
    max(Column_X) over(partition by Number, Date, cntColumn_X) as Column_X_Backward,
    Column_Y,
    max(Column_Y) over(partition by Number, Date, cntColumn_Y) as Column_Y_Forward
from
(
select *, 
  count(Column_X) over(partition by Number, Date order by Sl_NO rows between unbounded preceding and 1 preceding) as cntColumn_X,
  count(Column_Y) over(partition by Number, Date order by Sl_NO) as cntColumn_Y
from t
) as t

db<>fiddle