SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select: copy field content from previous row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter01
Starting Member

United Kingdom
12 Posts

Posted - 08/12/2014 :  19:58:15  Show Profile  Reply with Quote
Hello SQLTeam,

I´m fairly new to SQL and I´m puzzle with the query below. Please help!
Best regards,
Peter


I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code

date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |

I though of using the LAG function but the table does not have any specific order.
So after running the select, the table should look like:

date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |

Can anyone help, please?
Thanks,
Pater

gbritton
Flowing Fount of Yak Knowledge

1089 Posts

Posted - 08/13/2014 :  07:40:18  Show Profile  Reply with Quote
You need to have some ordering, or the effect of your query (whatever you work up) will be unpredictable.
Go to Top of Page

Peter01
Starting Member

United Kingdom
12 Posts

Posted - 08/13/2014 :  13:03:14  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

You need to have some ordering, or the effect of your query (whatever you work up) will be unpredictable.

Go to Top of Page

Peter01
Starting Member

United Kingdom
12 Posts

Posted - 08/13/2014 :  16:37:44  Show Profile  Reply with Quote
Many thanks Gbritton.

I followed your advised and added a new column with the desired order:

alter table Products
add ID_linea int identity(1,1)


and now my table has 3 columns:

date | product_code | ID_Linea
01/01/14 | BIC01 |1
03/01/14 | |2
01/01/14 | |3
04/01/14 | BIC01 |4
02/01/14 | CASH |5
05/01/14 | |6
03/01/14 | CASH |7
But I still don´t know how to write a select to fill in the empty product_code with the previous product_code.
Can anyone help, please?
Peter
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1089 Posts

Posted - 08/13/2014 :  17:18:26  Show Profile  Reply with Quote
This might work for you:


declare @t table (date date, product_code varchar(10), ID_Linea int)
insert into @t(date, product_code, ID_Linea) values
('01/01/14 ',' BIC01 ',1),
('03/01/14 ',' ',2),
('01/01/14 ',' ',3 ),
('04/01/14 ',' BIC01 ',4 ),
('02/01/14 ',' CASH ',5),
('05/01/14 ',' ',6),
('03/01/14 ',' CASH ',7)

update t1
set product_code = t2.product_code
from @t t1
cross apply (
	select top (1) * from @t t2
	where t1.ID_Linea > t2.ID_Linea
	and t2.product_code <> ''
	order by t2.ID_Linea desc) t2

select * from @t
Go to Top of Page

Peter01
Starting Member

United Kingdom
12 Posts

Posted - 08/13/2014 :  19:07:35  Show Profile  Reply with Quote
Bang On!!!
Many thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000