Skip to Content

Query For Linking Activity

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    May 05, 2014 at 09:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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