Row Level Transaction

[EdwardSalas]

Afternoon all,

I've been so far removed from SQL programming that, I know this is possible, however I cannot figure out how to do it. Here is the sample Table :

Date - Code - Bill - UnitSold
10/3/2005 123456 4.99 100
10/10/2005 123456 4.99 200
10/17/2005 123456 300
10/25/2005 123456 5.99 400

On 10/17, there was no Bill of the respective products, hence a null in its stead. How can, assuming I hit a null in a row, look at the previous date (in this case 10/10) and fill the null with this amount (in this case 4.99)?

Many thanks!

Edward

 

[EdwardSalas]

Morning,

Reading up on some information, I'm not sure I can do this with a simple loop...would a cursor be needed for this?

Thanks

 

[hmckillop]

Here is what I ended up with to do this.

/*create table ttest (mydate datetime, code varchar(10), bill money, unitsold int)
insert into ttest values(2005-10-03, '123456', 4.99, 100)
insert into ttest values(2005-10-10, '123456', 4.99, 200)
insert into ttest values(2005-10-17, '123456', NULL, 300)
insert into ttest values(2005-10-25, '123456', 5.99, 400)
insert into ttest values(2005-10-25, '123456', NULL, 400)
insert into ttest values(2005-10-25, '123456', NULL, 400)
*/
declare @v_temp table (OID int identity(1,1), mydate datetime, code varchar(10), bill money, unitsold int)
insert into @v_temp (mydate , code , bill , unitsold)
select * from ttest

select * from @v_temp
declare @v_missingid int, @v_newIDToMatchWithBillValue int

select @v_missingid = OID from @v_temp where bill IS NULL 

WHILE @v_missingid IS NOT NULL
BEGIN

	--get the previous row with a value
	SELECT @v_newIDToMatchWithBillValue = OID FROM @v_temp WHERE OID < @v_missingid and bill is not null

	UPDATE @v_temp 
	SET bill = (select bill from @v_temp where oid = @v_newIDToMatchWithBillValue)
	WHERE OID = @v_missingid
		

	SET @v_missingid = NULL
	select @v_missingid = OID from @v_temp where bill IS NULL
END

select * from @v_temp

At the start is some base table and data setup

"I'm living so far beyond my income that we may almost be said to be living apart