Share via

Take 2 – Filling NULL values with last non-Null value.

SQL Baby 161 Reputation points
2021-07-01T13:16:47.957+00:00

I posted this question couple of days ago & Viorel-1 was kind enough to provide a solution but my business rules were incorrect.

What I need is for each VesselID partition, staring from the very first repair [Vessel_RepairNo]= 1, is to fill current row NULL value in column [Vessel_CurrentRepairTypeID] by looking at the last row where column [Vessel_CurrentRepairTypeID] IS NOT NULL and then from that row taking the MAX value between two columns ([Vessel_CurrentRepairTypeID] & [Vessel_NextRepairTypeID]) from that non NULL row.

If the very first repair row i.e. [Vessel_RepairNo]= 1 itself has [Vessel_CurrentRepairTypeID] IS NULL, then look for next row where [Vessel_CurrentRepairTypeID] IS NOT NULL and use that to fill the first row’s NULL.

After that to, To fill the rest of the gaps/NULL row where current row [Vessel_CurrentRepairTypeID] IS NULL, we are taking the MAX value from [Vessel_CurrentRepairTypeID] & [Vessel_NextRepairTypeID] from the last non-NULL row and using that MAX value of those two repair type IDs from the last non-NULL row to populate the NULL in the current row.

:) If this is confusing then my apologies, hopefully screen shots of table & expected results will show what I am looking for.

In table below, [Vessel_CurrentRepairTypeID] column NULL values are in bold.
Column [Derived_FindMissing#s] is the expected output.

Have been trying to use windows functions plus ISNULL(),COALESCE() and bits below but not getting the desired results
-- LAG/LEAD
--LAST_VALUE(Vessel_NextRepairTypeID) OVER (PARTITION BY VesselID ORDER BY Vessel_RepairNo ASC rows unbounded preceding )
--LAST_VALUE(Vessel_NextRepairTypeID) IGNORE NULL OVER (PARTITION BY VesselID ORDER BY Vessel_RepairNo ASC) -- SQL Server doesn't implement it (Oracle, does).

MAX(Vessel_CurrentRepairTypeID,Vessel_NextRepairTypeID) OVER
(ORDER BY Vessel_CurrentRepairDate ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_Values

MAX(Vessel_CurrentRepairTypeID,Vessel_NextRepairTypeID) OVER
(ORDER BY Vessel_RepairNo ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_Values

110985-image.png

Expected output:
110963-image.png

--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   
  
  
 /*  
 -- Viorel-1 ANSWER  
-- Viorel-1 avatar imageViorel-1Follow  
-- Microsoft MVP  
   
 -- 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   
 */
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,191 Reputation points
    2021-07-02T06:57:04.99+00:00

    Hi @SQL Baby ,

    Actually you could refer Viorel-1's query which is working fine already.

    Please refer below query and check whether it is a little helpful to you. I'm not proud of my answer but at least it works. 

    ;with cte0 as (  
	select VesselID, MAX(Vessel_RepairNo) as Max FROM @REPAIRS GROUP BY VesselID)  
,cte01 as (  
	select a.VesselID,Vessel_TotalRepairs,Vessel_Last_RepairTypeID,Vessel_RepairNo,Vessel_CurrentRepairDate  
	,Vessel_NextRepairDate  
	,case when a.Vessel_RepairNo=b.Max and Vessel_CurrentRepairTypeID is null then Vessel_Last_RepairTypeID else Vessel_CurrentRepairTypeID end Vessel_CurrentRepairTypeID  
	,Vessel_NextRepairTypeID  
	from @REPAIRS a   
	left join cte0 b on a.VesselID=b.VesselID)  
,cte as (  
	select *,isnull(Vessel_CurrentRepairTypeID,LAG(Vessel_NextRepairTypeID) over (partition by VesselID order by Vessel_RepairNo) ) Vessel_CurrentRepairTypeIDnew  
	from cte01)  
,cte1 as (  
   SELECT *, COUNT(Vessel_CurrentRepairTypeIDnew) OVER(partition by VesselID ORDER BY Vessel_RepairNo ROWS UNBOUNDED PRECEDING) As MyGroup  
   FROM cte)  
,cte2 AS (  
	SELECT *, case when MyGroup=0 then lead(Vessel_CurrentRepairTypeIDnew,1) over (partition by VesselID ORDER BY Vessel_RepairNo) else  
	First_Value(Vessel_CurrentRepairTypeIDnew) OVER(partition by VesselID,MyGroup ORDER BY Vessel_RepairNo ROWS UNBOUNDED PRECEDING) end As Vessel_CurrentRepairTypeupdated  
    FROM cte1  
)  
select VesselID,Vessel_TotalRepairs,Vessel_Last_RepairTypeID,Vessel_RepairNo,Vessel_CurrentRepairDate  
,Vessel_NextRepairDate,Vessel_CurrentRepairTypeupdated Vessel_CurrentRepairTypeID,Vessel_NextRepairTypeID   
from cte2

    111210-output.png

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. SQL Baby 161 Reputation points
    2021-07-02T07:27:19.1+00:00

    Thank you Mellisa.

    I was about to say whether amending Viorel-1 solution coalesce expression was a viable solution for my needs.

    coalesce(
    Vessel_CurrentRepairTypeID,
    (
    select
    --top (1) Vessel_CurrentRepairTypeID
    --top(1) Vessel_CurrentRepairTypeID
    top(1) ISNULL(Vessel_NextRepairTypeID,Vessel_CurrentRepairTypeID) -- added this
    from @REPAIRS
    where VesselID = r.VesselID
    and Vessel_CurrentRepairTypeID is not null
    and Vessel_RepairNo < r.Vessel_RepairNo -- amended this
    order by Vessel_RepairNo desc
    ),
    LAG(Vessel_CurrentRepairTypeID) OVER (PARTITION BY VesselID ORDER BY Vessel_RepairNo desc) -- added this
    ) as Derived_Vessel_CurrentRepairTypeID

    select 
     VesselID,
     Vessel_TotalRepairs,
     Vessel_Last_RepairTypeID,
     Vessel_RepairNo,
     Vessel_CurrentRepairDate,
     Vessel_NextRepairDate,
     Vessel_CurrentRepairTypeID,
     Vessel_NextRepairTypeID,
     coalesce(
         Vessel_CurrentRepairTypeID,
         (
             select
             --top (1) Vessel_CurrentRepairTypeID
             --top(1) Vessel_CurrentRepairTypeID
             top(1) ISNULL(Vessel_NextRepairTypeID,Vessel_CurrentRepairTypeID) -- added this
             from @REPAIRS 
             where VesselID = r.VesselID 
             and Vessel_CurrentRepairTypeID is not null 
             and Vessel_RepairNo < r.Vessel_RepairNo  -- amended this
             order by Vessel_RepairNo desc
         ),
       LAG(Vessel_CurrentRepairTypeID) OVER (PARTITION BY  VesselID ORDER BY Vessel_RepairNo desc) -- -- added this
     ) as Derived_Vessel_CurrentRepairTypeID

 from @REPAIRS r
 --Where VesselID = 12488727
 order by VesselID, Vessel_RepairNo

    Just read Itzik Ben-Gan The Last non NULL puzzle article & was looking into his approach to solve my problem.

    Will try your solution and mark as answer if all goes well. Thanks!

  3. Ryan Abbey 1,181 Reputation points
    2021-07-08T22:08:06.627+00:00

    I don't have a SQL Server to try on but what is wrong with something like

    COALESCE(Vessel_CurrentRepairTypeID, MAX(Vessel_NextRepairTypeID) OVER
    (PARTITION BY VesselID ORDER BY Vessel_CurrentRepairDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS nonNULL_Values

    0 comments