TSQL - Populating values above and below an entry by referencing other columns

Question

I have a table which looks like:

This can be recreated from the code below:

CREATE TABLE dbo.EmpnoProblem
(
DATE date NULL,
WORKNO nvarchar(50) NULL,
OPSEQ int NULL,
RELEASED nchar(10) NULL,
PRODUCT nvarchar(50) NULL,
EMPNO nvarchar(50) NULL
) ;

INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 10, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 20, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 30, '10', '5454ABC', '345');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 40, '10', '5454ABC', '345');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 50, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 60, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 70, '10', '5454ABC', '698');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 80, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 90, '10', '5454ABC', NULL);

I need to populate the EMPNO column.

When 3 columns are the same: WORKNO, RELEASED, PRODUCT, I'm dealing with an individual grouping and my dataset is made up of these groupings.

Within this grouping, if an EMPNO is null then firstly it needs to be populated by the next EMPNO down which isn't null (this may be several entries down). The next EMPNO down is determined by OPSEQ number. If there is no EMPNO which isn't null further down in the grouping (such as the two null entries at the end), then it needs to be populated by the last EMPNO above it which isn't null.

The table should look like:

Apologies for a lack of code attempts on my end. I'm not sure where to start with this one. Any pointers at all would be greatly appreciated.

Answer 1

There are multiple ways to solve this. One is with APPLY.

select 
    a.*,
    COALESCE(a.EMPNO,next_NONEMPTY_EMPNO.EMPNO,prev_NONEMPTY_EMPNO.EMPNO) EMPNO_Fixed
from #EmpnoProblem a
OUTER APPLY (
    select TOP 1
        EMPNO
    from #EmpnoProblem b
    where
        EMPNO is not null and
        a.WORKNO = b.WORKNO and
        a.RELEASED = b.RELEASED and
        a.PRODUCT = b.PRODUCT and
        b.OPSEQ > a.OPSEQ
    order by OPSEQ ASC
) next_NONEMPTY_EMPNO
OUTER APPLY (
    select TOP 1
        EMPNO
    from #EmpnoProblem b
    where
        EMPNO is not null and
        a.WORKNO = b.WORKNO and
        a.RELEASED = b.RELEASED and
        a.PRODUCT = b.PRODUCT and
        b.OPSEQ < a.OPSEQ
    order by OPSEQ DESC
) prev_NONEMPTY_EMPNO

Answer 2

Below code might be helpful.

First update will populate EMPNO using next not null EMPNO

Second update will populate remaining EMPNO using previous not null EMPNO

Using two updates in this scenario will reduce IO when compared with use of single query.

UPDATE Dest
SET Dest.EMPNO=NewVal.EMPNO
FROM EmpnoProblem AS Dest
CROSS APPLY (SELECT TOP 1 Src.EMPNO FROM  EmpnoProblem AS Src WHERE Dest.WORKNO=Src.WORKNO AND Dest.RELEASED=Src.RELEASED AND Dest.PRODUCT=Src.PRODUCT AND Dest.OPSEQ<Src.OPSEQ AND Src.EMPNO IS NOT NULL ORDER BY Src.OPSEQ ASC) AS NewVal
WHERE Dest.EMPNO IS NULL;


UPDATE Dest
SET Dest.EMPNO=NewVal.EMPNO
FROM EmpnoProblem AS Dest
CROSS APPLY (SELECT TOP 1 Src.EMPNO FROM  EmpnoProblem AS Src WHERE Dest.WORKNO=Src.WORKNO AND Dest.RELEASED=Src.RELEASED AND Dest.PRODUCT=Src.PRODUCT AND Dest.OPSEQ>Src.OPSEQ AND Src.EMPNO IS NOT NULL ORDER BY Src.OPSEQ DESC) AS NewVal
WHERE Dest.EMPNO IS NULL;


SELECT *
FROM  dbo.EmpnoProblem
ORDER BY WORKNO,RELEASED,PRODUCT,OPSEQ