December 9, 2009 at 6:01 am
Will shortest path algorithm be useful for this problem?
I really need help regarding this one.. Any thoughts?
Thanks..
December 11, 2009 at 10:05 am
Sorry for the delayed response. Got caught up a little bit at my work place. Here is a new approach to the problem. i have tested it with different scenarios and it worked so far. please let me know if it works for you.
DECLARE @StateCycle TABLE
(record_id int, date_changed datetime, new_state int, min_id int, max_id int)
DECLARE @counter int
set @counter = 1
WHILE @counter <= (select count(*) from #table)
begin
insert into @StateCycle
select T1.record_id, T1.date_changed, T1.new_state, min(T1.id) min_id, max(T1.id) max_id
from #table T1,
(select record_id, date_changed, new_state
from #table T
where not exists (select 1 from @StateCycle S where (T.id > S.min_id and T.id <= S.max_id)
and (T.date_changed = S.date_changed))
and (T.id = @counter)) T2
where T1.new_state = T2.new_state
and T1.record_id = T2.record_id
and T1.date_changed=T2.date_changed
and T1.id >= @counter
group by T1.record_id, T1.date_changed, T1.new_state
set @counter = @counter + 1
end
select T.* from #table T, @StateCycle S
where T.id = S.min_id
December 16, 2009 at 7:47 pm
Thank you very much khawaja.irfan! It really works great.. Nice solution!:-D
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply