Replacing NULL SQL Server with dynamic value

Question

I want to replace NULL values ​​with a mode value based on data in a table.

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

Here is an installation example:

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 InDate mode for hardware in 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 specific modes to populate a NULL InDate for a given process id.

An example of the desired result:

 (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 as follows:

 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 mode calculation uses SiteId . It's not a question. I do not know what is the reference to NULL values ​​for EquipmentId . This is not a question either.

However, you can easily change this for other groups for modes or other columns.

Answer 2

For UPDATE :

you can use the following query:

 ;WITH CTE_CountofEquipment AS ( SELECT InDate, ProcessID, SiteID, COUNT(*) OVER (PARTITION BY ProcessID, SiteID, InDate) AS cnt FROM dbo.Table_basic ), ToUpdate AS ( SELECT InDate, ProcessID, SiteID, FIRST_VALUE(InDate) OVER (PARTITION BY ProcessID, SiteID ORDER BY cnt DESC ) AS mode FROM CTE_CountofEquipment ) UPDATE ToUpdate SET InDate = mode WHERE InDate IS NULL 

The query uses window functions to calculate the mode value:

• COUNT OVER() used to determine the totality of each InDate segment in each section ProcessID, SiteID
• FIRST_VALUE(InDate) is used to select the InDate` has the largest population

Demo here