Share via

Missing column values

Rontech10111 181 Reputation points
2025-07-24T04:52:41.85+00:00

Hi,

I have sample student data as in the below.

CREATE TABLE [dbo].[test_table](

[ID] [bigint] NULL,

[Score] [numeric](38, 6) NOT NULL,

[Ratio] [numeric](38, 6) NOT NULL,

[Desc] [varchar](50) NULL,

[Report] [varchar](8) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.205128 AS Numeric(38, 6)), CAST(0.794872 AS Numeric(38, 6)), N'Base2', N'ODDS')

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', N'RET')

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', N'BUSI')

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.187500 AS Numeric(38, 6)), CAST(0.812500 AS Numeric(38, 6)), N'Base2', N'ODDS')

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', N'BUSI')

INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.296875 AS Numeric(38, 6)), CAST(0.703125 AS Numeric(38, 6)), N'Base2', N'ODDS')

GO

Each student ID should have an associated report. For each student ID there are always 3 possible reports:

BUSI - with a default score of 0.25

ODDS - with a default score of 0.5

RET - with a default value of 0.25

What I require is to update the Score column where the Report is NULL. Thus, for example for student ID = 5, the missing report is RET, and this report has a default Score of 0.25 (if the report column is NULL)

And so on, for all records in my table

Kindly assist

Many thanks

SQL Server | SQL Server Transact-SQL
0 comments
Accepted answer
  1. Erland Sommarskog 124.3K Reputation points MVP Volunteer Moderator
    2025-07-24T08:49:22.6133333+00:00

    What if there are two rows that are NULL for the same student?

    The solution below tries to cater for this, but since I don't know the actual underlying business problem, I may not be on target.

    CREATE TABLE [dbo].[test_table](
[ID] [bigint] NULL,
[Score] [numeric](38, 6) NOT NULL,
[Ratio] [numeric](38, 6) NOT NULL,
[Desc] [varchar](50) NULL,
[Report] [varchar](8) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.205128 AS Numeric(38, 6)), CAST(0.794872 AS Numeric(38, 6)), N'Base2', N'ODDS')
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (9, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', N'RET')
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', N'BUSI')
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (5, CAST(0.187500 AS Numeric(38, 6)), CAST(0.812500 AS Numeric(38, 6)), N'Base2', N'ODDS')
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.000000 AS Numeric(38, 6)), CAST(1.000000 AS Numeric(38, 6)), N'Base2', NULL)
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.250000 AS Numeric(38, 6)), CAST(0.750000 AS Numeric(38, 6)), N'Base2', NULL)
INSERT [dbo].[test_table] ([ID], [Score], [Ratio], [Desc], [Report]) VALUES (3, CAST(0.296875 AS Numeric(38, 6)), CAST(0.703125 AS Numeric(38, 6)), N'Base2', N'ODDS')
go
SELECT * FROM test_table
go
DECLARE @reports TABLE (rep  char(4) NOT NULL PRIMARY KEY,
                        score decimal (38,6) NOT NULL)
INSERT @reports (rep, score)
   VALUES('BUSI', 0.25),
         ('ODDS', 0.5),
         ('RET',  0.25)
; WITH numberingmissing AS (
   SELECT Score, Report, ID, rowno = row_number() OVER(PARTITION BY ID ORDER BY Ratio)
   FROM   test_table
   WHERE  Report IS NULL
)
UPDATE nm 
SET    Report = X.rep,
       Score  = X.score
FROM   numberingmissing nm
CROSS  APPLY (SELECT  Y.rep, Y.score
              FROM    (SELECT rep, score, rowno = row_number() OVER(ORDER BY rep)
                       FROM   @reports r
                       WHERE  NOT EXISTS (SELECT *
                                          FROM   test_table tt
                                          WHERE  tt.ID = nm.ID
                                            AND  tt.Report = r.rep)) AS Y
              WHERE   Y.rowno = nm.rowno) AS X
go
SELECT * FROM test_table
go
DROP TABLE test_table
    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rodger Kong 370 Reputation points
    2025-07-24T09:17:23.16+00:00

    The hardest part of this question is how to find out which Report value is missing.

    I tried some method, and find a way to find the missing value. It likes bitwise operation in programing.

    Assume a bits string, the low bit identify 'BUSI', the 2nd bit is 'ODDS', the 3th is 'RET'. If all report values are there, the bits would be 111. If 'ODDS' is missing, the bits would be 101, use NOT operate on it, it will return 010, the 2nd bit is 1 means 'ODDS', that's what we need.

    Code is here 

    --Make a table with id, report values and scores
;WITH V AS
(
	SELECT * FROM (VALUES (0, 'BUSI', 0.25), (1, 'ODDS', 0.5), (2, 'RET', 0.25)) AS TD(i, r, s)
)
, CTE AS --Prepair a table that will be operate with bitwise OR  
(
	SELECT *, ISNULL(POWER(2, V.i), 0) AS p FROM test_table A 
	LEFT JOIN V
	ON A.Report = V.r
)
,M AS -- Find the missing Report value by some bitwise operation
(
	SELECT ID, LOG(~(CAST(SUM(p) AS tinyint) | 0xF8),2) AS MI
	FROM CTE 
	GROUP BY ID
)
UPDATE T --Update the table
SET 
	T.Score = V.s,
	T.Report = V.r
FROM
M INNER JOIN test_table T ON T.ID = M.ID AND T.Report IS NULL
INNER JOIN V ON M.MI = V.i
    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.