Query For Linking Activity

[ANKIT_CHAUHAN]

Dear All,

I need your help in writing a query. My Scenario is as follows:

I add an Activity whose Code is 95. Now using activity no. 95, I make a follow-up whose no. is 103. Now using 103, I create another follow-up, no. is 110. And using 110 I create my last follow-up, no. is 118.

Now if user selects activity no. 118 & want to see the history or previous conversation made against this activity then he/she should get 110,103 & 95

And if user selects activity no. 95 & want to see the history or conversation made against this activity then he/she should get 103, 110 & 118.

Please help me in writing this query.

Any kind of help would be appreciated.

Thanks & Regards

Ankit Chauhan

[former_member201110 · Accepted solution]

Hi Ankit,

The following script works for me in SBO query generator:

declare @c int
declare @n int
declare @p int
declare @OCLG table (idx int identity(1, 1), ClgCode int, CntctDate datetime, Details nvarchar(60), prevActvty int)

set nocount on

select @c = T0.ClgCode from OCLG T0 where T0.ClgCode = [%0]

set @p = @c
-- Forwards
while @p is not null
     begin
          set @n = null;
          select @n = ClgCode from OCLG where prevActvty = @p;
          if @n is not null
               insert into @OCLG select @n, CntctDate, Details, prevActvty from OCLG where ClgCode = @n

          set @p = @n;
     end

set @n = @c
-- Backwards
while @n is not null
     begin
          set @p = null;
          select @p = prevActvty from OCLG where ClgCode = @n;

          if @p is not null
               insert into @OCLG select @p, CntctDate, Details, prevActvty from OCLG where ClgCode = @p

               set @n = @p;
      end

set nocount off

select ClgCode as [Activity], CntctDate as [Date], Details as [Remarks] from @OCLG order by ClgCode

This script searches backwards and forwards to include all the activities linked to a particular activity. You can add additional fields to the results by adding the fields to the temp table definition at the top and then adding the same fields to the two insert statements.

Kind Regards,

Owen

[ANKIT_CHAUHAN · Accepted solution]

Hi ,

Thank you dear so much... It is working in forward condition. But can you please check for the backward condition. It is not giving me result for backward condition.

Thanks & Regards

Ankit Chauhan

[edy_simon · Accepted solution]

Hi Ankit,

If the forward is already working,

You should look into his logic and apply your self for the backward and see why it is not working.

After all it is all crystal clear query, no secret no magic.

Should not rely on others to spoon feed the whole thing....

Cheers..

Edy