Find missing NULL values, fill NULL gaps.

Question

Am trying to find the missing NULL values (See table image below) shown in BOLD in column [Vessel_CurrentRepairTypeID] for each [VesselID] partition.

Have tried using windows LAG/LEAD functions plus ISNULL(),COALESCE() but not getting the desired results…

The collated data we have put together in table below identifies total number of repairs [Vessel_TotalRepairs] performed on each VesselID and then find details associated with each previous individual repair row via [Vessel_CurrentRepairTypeID].

The problem is we have gaps, missing data and thus NULL values for [Vessel_CurrentRepairTypeID]..

So, the need to derive those [Vessel_NextRepairTypeID] for the NULLs & fill those gaps.

[Vessel_RepairNo] identifies each previous individual repair performed. Where [Vessel_RepairNo]= 1 is the oldest repair

To fill the gaps/NULLs, row where [Vessel_CurrentRepairTypeID] IS NULL, we are comparing [Vessel_CurrentRepairTypeID] & [Vessel_NextRepairTypeID] from the previous row and using the MINIMUM of those two repair type IDs from the previous row to populate the NULL in the current row.

To further clarify, if the current row [Vessel_CurrentRepairTypeID] IS NULL then look at the previous row and use it’s [Vessel_CurrentRepairTypeID].

For example:

For [VesselID] = 12488727, We have [Vessel_TotalRepairs] = 7 - total repairs performed & also the last repair performed - and we are interested in finding [Vessel_CurrentRepairTypeID] NULL values for the previous 6 individual repairs i..e. [Vessel_RepairNo] 1-6.

[Vessel_RepairNo] = 6 is the next repair after [Vessel_TotalRepairs] = 7, so we compare [Vessel_CurrentRepairTypeID] & [Vessel_NextRepairTypeID] values in the current row for [Vessel_RepairNo] = 6 and use the Smallest (min) value as the value to populate the next row if it is NULL. Since both vessel RepairTypeIDs are NULL, to get the ball rolling we can use COALESCE(MIN([Vessel_CurrentRepairTypeID], [Vessel_NextRepairTypeID]), [Vessel_Last_RepairTypeID]) to populate the NULL [Vessel_CurrentRepairTypeID] for row [Vessel_RepairNo]=6.

After that, the thinking is to use LEAD() windows functions to assign a current NULL row with previous row’s MIN( [Vessel_CurrentRepairTypeID], [Vessel_NextRepairTypeID]) value.

Hope this makes sense. Please let me know otherwise.

This is what the sample data looks like:

Expected Output:

--Create Test Table   
DECLARE @REPAIRS TABLE (  
VesselID int NULL ,  
Vessel_TotalRepairs Int NULL,  
Vessel_Last_RepairTypeID Int NULL,  
Vessel_RepairNo int NULL ,  
Vessel_CurrentRepairDate DateTime NULL,  
Vessel_NextRepairDate DateTime NULL,  
Vessel_CurrentRepairTypeID int NULL ,  
Vessel_NextRepairTypeID int NULL   
  
)  
  
-- Test  Data  
  
INSERT INTO @REPAIRS   
(VesselID, Vessel_TotalRepairs, Vessel_Last_RepairTypeID, Vessel_RepairNo, Vessel_CurrentRepairDate, Vessel_NextRepairDate, Vessel_CurrentRepairTypeID, Vessel_NextRepairTypeID)  
  
SELECT 12488727, 7, 73497996, 1, cast('2015-06-23 15:29:01.130' as Datetime), cast('2018-05-02 12:53:13.550' as Datetime), 44186692, 73497996 UNION ALL  
SELECT 12488727, 7, 73497996, 2, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12488727, 7, 73497996, 3, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12488727, 7, 73497996, 4, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12488727, 7, 73497996, 5, cast('2018-05-02 12:53:13.550' as Datetime), NULL, 73497996, NULL UNION ALL  
SELECT 12488727, 7, 73497996, 6, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 1, cast('2015-06-23 15:32:35.887' as Datetime), cast('2017-01-27 09:12:48.823' as Datetime), 44187281, 57073090 UNION ALL  
SELECT 12489284, 10, 69537303, 2, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 3, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 4, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 5, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 6, cast('2017-01-27 09:12:48.823' as Datetime), cast('2017-01-27 09:14:06.200' as Datetime), 57073090 , 57073091 UNION ALL  
SELECT 12489284, 10, 69537303, 7, cast('2017-01-27 09:14:06.200' as Datetime), cast('2018-01-08 14:28:49.860' as Datetime), 57073091, 69537303 UNION ALL  
SELECT 12489284, 10, 69537303, 8, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489284, 10, 69537303, 9, cast('2018-01-08 14:28:49.860' as Datetime), NULL, 69537303, NULL UNION ALL  
SELECT 13736493, 6, 76611557, 1, cast('2016-09-07 12:42:06.040' as Datetime), cast('2018-08-29 12:30:47.633' as Datetime), 53321421, 76611557 UNION ALL  
SELECT 13736493, 6, 76611557, 2, NULL, NULL, NULL, NULL UNION ALL  
SELECT 13736493, 6, 76611557, 3, NULL, NULL, NULL, NULL UNION ALL  
SELECT 13736493, 6, 76611557, 4, NULL, NULL, NULL, NULL UNION ALL  
SELECT 13736493, 6, 76611557, 5, cast('2018-08-29 12:30:47.633' as Datetime), NULL, 76611557, NULL UNION ALL  
SELECT 12489270, 5, 69537290, 1, NULL, NULL, NULL, NUll UNION ALL  
SELECT 12489270, 5, 69537290, 2, cast('2015-06-23 15:32:34.070' as Datetime), cast('2018-01-08 14:28:49.600' as Datetime), 44187267, 69537290 UNION ALL  
SELECT 12489270, 5, 69537290, 3, NULL, NULL, NULL, NULL UNION ALL  
SELECT 12489270, 5, 69537290, 4, NULL, NULL, NULL, NULL UNION ALL  
SELECT 13551645, 4, 96688505, 1, NULL, NULL, NULL, NULL UNION ALL  
SELECT 13551645, 4, 96688505, 2, cast('2015-06-23 15:32:34.070' as Datetime), cast('2018-01-08 14:28:49.600' as Datetime), 96688504, 96688504 UNION ALL  
SELECT 13551645, 4, 96688505, 3, NULL, NULL, NULL, NULL  
  
  
SELECT * FROM @REPAIRS ORDER BY VesselID, Vessel_RepairNo

Accepted Answer

Check if the next query satisfies the rules:

select 
    VesselID,
    Vessel_TotalRepairs,
    Vessel_Last_RepairTypeID,
    Vessel_RepairNo,
    Vessel_CurrentRepairDate,
    Vessel_NextRepairDate,
    coalesce(
        Vessel_CurrentRepairTypeID,
        (
            select top(1) Vessel_CurrentRepairTypeID
            from @REPAIRS 
            where VesselID = r.VesselID 
            and Vessel_CurrentRepairTypeID is not null 
            and Vessel_RepairNo > r.Vessel_RepairNo 
            order by Vessel_RepairNo 
        ),
        Vessel_Last_RepairTypeID
    ) as Vessel_CurrentRepairTypeID,
    Vessel_NextRepairTypeID
from @REPAIRS r
order by VesselID, Vessel_RepairNo

Share via

Find missing NULL values, fill NULL gaps.

0 additional answers

Your answer