October 6, 2021 at 8:04 pm
Hi
I'm not even sure if I'm thinking about this the right way... Nevertheless, the setup is to find the distance from a Pass to the previous Pass, where the calculation for Pass itself involves the distance.
Is there any other way to do this, such as recursion?
declare @T table(N int not null primary key clustered, Result char(1) null, Distance int null)
insert @T(N) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
--starting point
update @T
set
Result = 'P',
Distance = 0
where N = 1
declare @ThisN int = 2
declare @MaxN int = 10
while @ThisN <= @MaxN
begin
----some calculation involving N AND Distance (not shown)
declare @Result char(1) = cast(case when @ThisN in (2, 6, 9) then 'P' else 'F' end as char(1))
update @T
set
Result = @Result,
Distance = @ThisN - (select max(N) from @T where @Result = 'P' and N < @ThisN and Result = 'P') --dependent on previous result
where N = @ThisN
set @ThisN = @ThisN + 1
end
select * from @T order by N
October 6, 2021 at 11:53 pm
Isn't the total distance covered the sum of all distances up to this pass? If that's the case, then why not use a windowing function and sum(distance)? and then use ROWS BETWEEN UNBOUNDED PRECEDING to get a running total?
October 10, 2021 at 12:52 pm
The scenario is:
Result (Pass or Fail) is a function of Distance, where Distance = N - (N of previous latest Pass)
How can you work out Result for row N without first finding the 'previous latest Pass'. Can only think of iteration, is there another way?
October 18, 2021 at 2:38 pm
A windowed lag function might work. I'm farther away from efficient SQL production queries, so there might be better ways, but I was thinking something like:
select N, Result, Distance = N - lag(N,1,1) OVER (partition by Result ORDER BY N)
from @T
where Result = 'P'
The third parameter of lag is the default, which should by the N of the first Pass to get a Distance of zero. In this example, that is N = 1, but could be replaced with a variable/return from query (select min(N) from @T where Result='P').
Join this back to @T on N and update the joined records.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply