Colin O'Brien Colin O'Brien - 5 months ago 10
SQL Question

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

I have a table which looks like:

enter image description here

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:

enter image description here

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

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
        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
        b.OPSEQ < a.OPSEQ
    order by OPSEQ DESC
) prev_NONEMPTY_EMPNO