SQL Server NULL replacement with dynamic value

Question

I am looking to replace NULL values with a mode value based on data within a table.

In the following example I would like to replace the NULL InDate of the EquipmentID with the mode value of the InDates for that ProcessID. I have calculated the mode InDate for a ProcessID, I just can't work out how to use that value to replace the NULL value for the EquipmentID withing the ProcessID

Here is an example setup:

 CREATE TABLE dbo.Table_basic (
                InDate INT,
                EquipmentID INT,
                ProcessID nvarchar(50),
                SiteID INT
                )

INSERT INTO Table_basic (InDate, EquipmentID, ProcessID, SiteID)
VALUES (2001, 1,'1PAA',1),
        (2001,2,'1PAA',1),
        (NULL, 3,'1PAA',1),
        (2001,4,'1PAA',1),
        (1999, 5,'1PAA',1),
        (2001,6,'1PAB',1),
        (2001,7,'1PAC',1),
        (2001, 8,'2AA',2),
        (1999,9,'2AB',2),
        (NULL, 10,'2AB',2),
        (1999,11,'2AB',2),
        (1998,12,'2AB',2),
        (2001, 13,'2AB',2),
        (1999,14,'2AB',2),
        (2001, 15,'2AC',2),
        (2001,16,'2AC',2),
        (1986, 17,'3AA',3),
        (1985,18,'3AA',3),
        (1985,19,'3AA',3),
        (NULL, 20,'3AC',3),
        (2005,21,'3AC',3),
        (2005, 22,'3AC',3),
        (2005,23,'3AC',3);

This is how I find the mode of the InDate for the Equipment within the ProcessID.

WITH CTE_CountofEquipment AS
 (
  SELECT
    ProcessID
   ,SiteID
   ,cnt   = COUNT(1)
   ,rid   = ROW_NUMBER() OVER (PARTITION BY ProcessID ORDER BY COUNT(1) DESC)
   ,InDate
    FROM dbo.Table_basic 
  GROUP BY  SiteID, ProcessID, InDate
 )
 SELECT
   ProcessID
  ,cnt = cnt
  ,[SiteID]
  ,InDate
 FROM CTE_CountofEquipment
 WHERE rid = 1
 ORDER BY SiteID;

I would like to use these determined modes to fill in the NULL InDate for a given ProcessID.

Desired result example:

(NULL, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(1999, 3,'1PAA',1),
(2000, 3,'1PAA',1),
(2001, 3,'1PAA',1),

becomes

(2001, 3,'1PAA',1), -- InDate updated to modal value
(2001, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(1999, 3,'1PAA',1),
(2000, 3,'1PAA',1),
(2001, 3,'1PAA',1),

Thanks

Answer 1

I would do the calculation like this:

with modes as (
      select p.*
      from (select tb.processId, tb.indate, count(*) as cnt,
                   row_number() over (partition by tb.processId order by count(*) desc) as seqnum
            from table_basic tb
            group by tb.processId, tb.indate
           ) p
      where seqnum = 1
     )
update tb
    set indate = m.indate
    from table_basic tb join
         modes m
         on tb.processId = m.processId
    where indate is null;

This answers your question. I have no idea why your calculation of the mode uses the SiteId. That is not part of the question. I don't know what the reference is to NULL values for EquipmentId. That is not part of the question either.

However, you should be able to easily modify this for other groupings for the modes or other columns.