Subtract 2 different columns from 2 different rows to create new table

[reecem]

I have a table listing faults on rolls of material and need to work out the distance between faults on the same roll and create a new table with the results.

Current table

Batch Roll Fault_Str Fault_end
8701 1 2 3
8701 1 6 8
8701 1 10 14
8701 2 5 7
8701 4 3 4
8701 4 6 7

I need to subtract fault_end of the first fault (3) on Roll 1 from Fault_str (6) on second fault from Roll 1 and so on to create the new table below.

Batch Roll Distance (= Fault_Str - Fault_end)
8701 1 3 (= 6 - 3)
8701 1 2 (= 10 - 8)
8701 2 0 (No 2nd fault on Roll 2)
8701 4 2 (= 6 - 4)

Any help on this will be greatly appreciated.

 

[bborissov]

what makes these records sequence?
How do you know which is first, which second and so on..?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[reecem]

The table is sorted by Batch, Roll and Fault_str so the lowest starting fault for a roll is always first. Faults can never overlap.

 

[jsunderman]

I would suggest a stored procedure that loops through the data calculating the difference and then writing a new record to the new table.

If you need this real time create an insert trigger that would select the row before and write out the new record to the new table.

Otherwise I can't figure out a way to do this with a single SQL statement without getting invalid rows.

 

[r937]

The table is sorted by Batch, Roll and Fault_str ...

actually, that's not true, tables are never "sorted" (clustering indexes notwithstanding)


only queries which explicitly use ORDER BY product sorted results

r937.com | rudy.ca

 

[bborissov]

Is this a one time job?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RyanEK]

Hi,

Try...

declare @temp table (Batch int, Roll int, Fault_Str int, Fault_end int)
insert into @temp values (8701,1,2,3)
insert into @temp values (8701,1,6,8)
insert into @temp values (8701,1,10,14)
insert into @temp values (8701,2,5,7)
insert into @temp values (8701,4,3,4)
insert into @temp values (8701,4,6,7)

select batch, roll, 
isnull((fault_str - (select top 1 isnull(fault_end,0) 
              from @temp t2 
              where t2.batch = t1.batch 
			  and t2.roll = t1.roll
			  and t2.fault_str < t1.fault_str
              order by fault_str desc)),0) as Distance
from @temp t1

It has an extra row for the first record of each roll, but you could just filter this out.

Ryan

 

[reecem]

That works a treat, thanks Ryan.

Thanks all for your help.