Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
tp3
BC160401693
TO find max marks of students
MyWall
Q2_5MIN
Create Date Batches from Date Range - SQL Server
/Users/svetlanakanevskaa/Downloads/ACDB_LIGHT_MS.sql
Split name
https://www.hsbc.co.in/ways-to-bank/online-banking/9247/
String agg
Please log in to post a comment.