Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
subquery & correlated subquery & "is null" condition
h
Task_5_Final
Common Table Expression
MyWall
SQL Server - DateFirst Example
Delete double entries from a table without primary key
sql
SQL - Find series of timestamps with small gaps
practice sql_12AUG_Upddated