Replace the null value with a value in the column

Question

If there is both null and numeric value in the offset and duration columns, I want to replace that null value with that numeric value for that particular set Monday to Sunday.

laboraccountid | weekday   | offset | duration
---------------+-----------+--------+---------
190685         | Monday    | 200    | 500
190685         | Tuesday   | 200    | 500
190685         | Wednesday | null   | null
190685         | Thursday  | null   | null
190685         | Friday    | 200    | 500
190685         | Sunday    | 200    | 500
125686         | Monday    | 1435   | 5687
125686         | Tuesday   | 1435   | 5687
125686         | Wednesday | 1435   | 5687
125686         | Thursday  | 1435   | 5687
125686         | Friday    | 1435   | 5687
125686         | Saturday  | 1435   | 5687
125686         | Sunday    | 1435   | 5687

I tried using the case statement but I am unable to do it.

Answer 1

You could try something like, adding a CTE to get the replacement NULL values grouped by the account id.:

WITH BestValue
AS
(
SELECT
    laboraccountid,
    MAX(offset) AS offset,
    MAX(duration) AS duration
FROM
    MyTable
WHERE
    offset IS NOT NULL AND duration IS NOT NULL
GROUP BY
    laboraccountid
)
SELECT
    MT.laboraccountid,
    MT.weekday,
    ISNULL(MT.offset,BV.offset) as offset,
    ISNULL(MT.duration, BV.duration) as duration
FROM
    MyTable AS MT
    JOIN
    BestValue AS BV
    ON MT.laboraccountid = BV.laboraccountid

This will give you the maximum value for that week as the NULL replacement.

This assumes that there is at least one non-null value for each week.

The case equivalent is

    CASE
        WHEN offset IS NULL THEN BV.offset
        ELSE offset
    END AS MT.offset,
    ....