SQL Server NULL replacement with dynamic value, Sql Server

SQL Server NULL replacement with dynamic value

IF OBJECT_ID('dbo.t', 'U') IS NOT NULL 
BEGIN
   DROP TABLE dbo.Table_basic   
END

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);
        
;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 
)
SELECT *
FROM ToUpdate

DROP TABLE dbo.Table_basic


run \| edit \| history \| help	0