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
ms sql where ,like
Pro DB's project
c12part2
Service broker
MSSQL NVARCHAR to INT conversions
28-02
TABLE
bc160401693
MixT
split a string into pairs of words