Get data from previous row if a particular column is blank

Transact-SQL
1

I want to get the previous row value in a particular column if it is blank. How can I do this may be using lag function.

create table [test].[dbo].[test](
CusName nvarchar(50) NULL,
Value INT NULL)

-- Insert some data
insert [test].[dbo].[test] (CusName, Value)
values ('CustomerA', 151),
('', 1400),
('', 2600),
('CustomerB', 111),
('', 359)

SQLResultExpected

Appreciate it if you can give some suggestion on how to achieve this.
Thank you

3

hi

please see the below link ... hope this helps !!! :slight_smile:

the links way is ... using FIRST VALUE .. ROWS unbounded PRECEEDING
all SQL Server 2012 .. or greater stuff !!!

4

hi

i was able to do it .. but in a different slightly long way !!!
if it helps great ! :slight_smile:

please click arrow tot he left for drop create data 
drop table #data
go 

create table #data(
CusName nvarchar(50) NULL,
Value INT NULL)

-- Insert some data
insert #data (CusName, Value)
values ('CustomerA', 151),(null, 1400),(null, 2600),(null,334),
       ('CustomerB', 111),(null, 359),(null,4444),(null,222),(null,56),
	   ('CustomerC', 123),(null, 345),(null,555),(null,4),(null,678),(null,666)
	   

select 'sample data ', * from #data
go

image
image465×580 8.1 KB 

; with 
    rn_cte as 
   (
 select ROW_NUMBER() over(order by (select null)) as rn , * from #data 
   ) 
  , 
   cte_notnull_rn as 
  (
select ROW_NUMBER() over(order by rn) as rn12 ,a.* from 
      ( 
	    select rn, CusName from rn_Cte where CusName is not null 
           union all 
        select max(rn) +1, 'Last Row' from rn_cte 
	    ) a 
 
   ) 
  , 
   cte_fin as 
  (
     select a.rn12 as arn12,a.CusName as acusname,a.rn as minrn ,  b.rn12 as brn12,b.CusName as bcusname,b.rn as maxrn 
       from 
	      cte_notnull_rn a 
		     join 
		 cte_notnull_rn b 
		     on 
		   a.rn12 + 1 = b.rn12
  )
  	SELECT 
	      'SQL OUTPUT',
		  ACUSNAME AS CUSNAME ,
	      VALUE 
	FROM  
	   cte_fin a 
		JOIN 
	   rn_cte b 
		 ON 
		  b.rn BETWEEN a.minrn and a.maxrn-1    
	go

image
image505×578 7.94 KB

5

Umm...

So what happens if the following is done before the query:

CREATE CLUSTERED INDEX ixc_temp_data_value ON #data([Value]);

Would you care to explain the result?

Do you think non-deterministic queries are a good idea?

Do you think it might be better to explain basic relational theory to the OP?

6

Hi ifor

Sure I can do that

Please give me some time

Thanks,

7

how do you know what order the data is in?

8

As mike01 noted, we must know the order of data. I've added an identity column to the table to provide an order. I also added more rows so that the same cust name appearing multiple times could be tested.

create table [dbo].[test](
id int identity(1, 1) NOT NULL,
CusName nvarchar(50) NULL,
Value INT NULL)

-- Insert some data
TRUNCATE TABLE [dbo].[test];
insert [dbo].[test] (CusName, Value)
values ('CustomerA', 151), ('', 1400), ('', 2600),
('CustomerB', 111), ('', 359),
('CustomerC', 333), ('', 334), ('', 335),
('CustomerA', 2151), ('', 21400), ('', 22600)
SELECT * FROM dbo.test ORDER BY id;

;WITH cte_CusNames AS (
    SELECT id, CusName
    FROM dbo.test
    WHERE CusName <> ''
)
UPDATE t
SET CusName = lookup_CusName.CusName
--SELECT *
FROM dbo.test t
CROSS APPLY (
    SELECT TOP (1) CusName
    FROM cte_CusNames cc
    WHERE t.id > cc.id
    ORDER BY id DESC
) AS lookup_CusName
WHERE (t.CusName IS NULL OR t.CusName = '')
SELECT * FROM dbo.test ORDER BY id;
9

Hi Mike and Scott

With regards to order by mentioned by both of you

I used
Order by (select null)

Which means
The order in which data is inserted
Into the table

Unless an order by needs to be specified

Please let me know if I am missing
Anything

Thanks,

10

What makes you think a relation/table has an order?

11

Hi. ..IFOR

Could you please explain

That to me

Thank you

12

Hi ifor

I mean it may need to be explained in a different way

Order by ( select null )
If this is what you are talking about

What is your opinion ..

Are you saying
That there is NO such thing as
Order in which data inserted into table

Could you please explain
I am more than happy to learn .

Not sure what you mean .

Thanks

13

Yes, that is exactly what SQL Server itself states. Unless you explicitly specify an order, no order is guaranteed. If it's a new table, SQL is almost certainly going to load it in input order, so an identity is virtually sure, but it's not 100%. And I would certainly not rely on (SELECT NULL) in an order by to produce consistent results.

14

In relational theory, a relation (table) is an unordered set.
In SQL Server, due to the way it is implemented, a table can often appear to have a default order but it is not guaranteed. ie Only the result set, or windowed function, can be ordered. ORDER BY (SELECT NULL) should only be used when ORDER BY is required and you really do not care about the order. This is unlikely in production code.

With your test data, putting a clustered index on [Value], changed the apparent order of the table and completely messed up your query. The result of a query should be deterministic. ie The same results should be returned regardless of indexing etc. (Better indexing should only affect the speed at which the result is returned.)

With this sort of question you can:

  1. suggest that they have an ordering column; like Scott.
  2. suggest the original data was loaded from a list, where order has meaning, like an Excel sheet and the best thing to do is to add another column, like line number, before importing the data into SQL server.
15

Thanks Scott

Thanks ifor

I kind of had that in the back of my mind

Somewhere I read that . Long long time ago

Will keep that in mind..

:+1::+1:

16

Hi ScottPletcher,
It worked, thank you so much