I am looking to replace NULL values with a mode value based on data within a table.
In the following example I would like to replace the NULL InDate of the EquipmentID with the mode value of the InDates for that ProcessID. I have calculated the mode InDate for a ProcessID, I just can't work out how to use that value to replace the NULL value for the EquipmentID withing the ProcessID
Here is an example setup:
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 the mode of the InDate for the Equipment within the 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 determined modes to fill in the NULL InDate for a given ProcessID.
Desired result example:
(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
I would do the calculation like this:
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 calculation of the mode uses the SiteId
. That is not part of the question. I don't know what the reference is to NULL
values for EquipmentId
. That is not part of the question either.
However, you should be able to easily modify this for other groupings for the modes or other columns.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With