Replace a set of null values for the same date with a set of existing values

Transact-SQL
,
1

Hello everybody! I'm pretty new at SQL, so I have a problem and want to ask for help.
I have a table that looks something like this (here's an example):

table
As you can see, names in 'Name' column are repeated for each date from 'DT' column so it's probably a set of rows with different names for the same data.

For the sets of rows with null name values I want to replace (I assume update) them with the set of names from previous date so the result shoud be like this:

|ID| DT | Name | Cos_fi | isCalculated |
| 1 | 2019-07-18 04:33:43.000 | Name1 | 0.28 | 0 |
| 2 | 2019-07-18 04:33:43.000 | Name2 | 0.43 | 0 |
| 3 | 2019-07-18 04:33:43.000 | Name3 | 0.34 | 0 |
| 4 | 2019-07-18 04:33:43.000 | Name4 | 0.62 | 0 |
| 5 | 2019-07-18 04:33:43.000 | Name5 | 0.62 | 0 |
| 6 | 2019-07-18 04:33:51.000 | Name1 | NULL | 1 |
| 7 | 2019-07-18 04:33:51.000 | Name2 | NULL | 1 |
| 8 | 2019-07-18 04:33:51.000 | Name3 | NULL | 1 |
| 9 | 2019-07-18 04:33:51.000 | Name4 | NULL | 1 |
|10| 2019-07-18 04:33:51.000 | Name5 | NULL | 1 |
|11| 2019-07-18 04:34:01.000 | Name1 | NULL | 1 |
|12| 2019-07-18 04:34:01.000 | Name2 | NULL | 1 |
|13| 2019-07-18 04:34:01.000 | Name3 | NULL | 1 |
|14| 2019-07-18 04:34:01.000 | Name4 | NULL | 1 |
|15| 2019-07-18 04:34:01.000 | Name5 | NULL | 1 |
|16| 2019-07-18 04:35:33.000 | Name1 | 0.21 | 0 |
|17| 2019-07-18 04:35:33.000 | Name2 | 0.42 | 0 |
|18| 2019-07-18 04:35:33.000 | Name3 | 0.34 | 0 |
|19| 2019-07-18 04:35:33.000 | Name4 | 0.62 | 0 |
|20| 2019-07-18 04:35:33.000 | Name5 | 0.62 | 0 |

Is there any way to do it in T-SQL? Please ignore null values in 'Cos_fi' column - it's not important now.

Creating table code is shown below.

DECLARE @test TABLE (
ID INT IDENTITY(1, 1),
DT DATETIME NOT NULL,
Name VARCHAR(10) NULL,
Cos_fi FLOAT NULL,
isCalculated BIT
);

INSERT INTO @test(DT, Name, Cos_fi, isCalculated)
VALUES
('2019-07-18T04:33:43.000', 'Name1', 0.28, 0),
('2019-07-18T04:33:43.000', 'Name2', 0.43, 0),
('2019-07-18T04:33:43.000', 'Name3', 0.34, 0),
('2019-07-18T04:33:43.000', 'Name4', 0.62, 0),
('2019-07-18T04:33:43.000', 'Name5', 0.62, 0),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:35:33.000', 'Name1', 0.21, 0),
('2019-07-18T04:35:33.000', 'Name2', 0.42, 0),
('2019-07-18T04:35:33.000', 'Name3', 0.34, 0),
('2019-07-18T04:35:33.000', 'Name4', 0.62, 0),
('2019-07-18T04:35:33.000', 'Name5', 0.62, 0);

SELECT *
FROM @test
ORDER BY DT

Any advice will be appreciated.
Thanks in advance!

2

Hi

Case when column null then
Lag name order by dt else column end

Lag ....

Screenshot_2019-10-03-16-17-06-61_c0dc27f5c07cb0fb3541d6073dfd6932
Screenshot_2019-10-03-16-17-06-61_c0dc27f5c07cb0fb3541d6073dfd6932.png1080×2340 275 KB

1 Like
3 
DECLARE @test TABLE (
ID INT IDENTITY(1, 1),
DT DATETIME NOT NULL,
Name VARCHAR(10) NULL,
Cos_fi FLOAT NULL,
isCalculated BIT
);

INSERT INTO @test(DT, Name, Cos_fi, isCalculated)
VALUES
('2019-07-18T04:33:43.000', 'Name1', 0.28, 0),
('2019-07-18T04:33:43.000', 'Name2', 0.43, 0),
('2019-07-18T04:33:43.000', 'Name3', 0.34, 0),
('2019-07-18T04:33:43.000', 'Name4', 0.62, 0),
('2019-07-18T04:33:43.000', 'Name5', 0.62, 0),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:33:51.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:34:01.000', NULL, NULL, 1),
('2019-07-18T04:35:33.000', 'Name1', 0.21, 0),
('2019-07-18T04:35:33.000', 'Name2', 0.42, 0),
('2019-07-18T04:35:33.000', 'Name3', 0.34, 0),
('2019-07-18T04:35:33.000', 'Name4', 0.62, 0),
('2019-07-18T04:35:33.000', 'Name5', 0.62, 0);



SELECT *
FROM @test
ORDER BY DT



update a
set Name = New_Name
from @test a
left join (
			Select 
			ID
			,'Name'+convert(varchar(10),ROW_NUMBER() over (partition by DT order by (select 0)))as New_Name
			from @test
			where Name is NULL
) b on a.id =b.id
where Name is NULL




SELECT *
FROM @test
ORDER BY DT
1 Like
4

@harishgg1, sorry for answering so late. Thanks a lot for your reply! It is very useful and I have learned about some new SQL functions :slight_smile:

5

@Muj9, sorry for a long reply. Thanks a ton! Your code works exactly what I wanted :slight_smile: