Help with Sql Query - Recursion

Hi Experts,
I have a table where customer follow-ups are stored.
When there is new Lead, FollowUpLeadId = 0

LeadId CustomerId FollowUpDate Remark FollowUpLeadId
  16               12        2011-12-29    xxxx           0
  17               11        2011-12-29    yyyy           0
  18               12        2011-12-31    zzzz          16
  19               12        2012-12-01    rrrrrr          18

Now I am passing LeadId '19', and I want to get all the previous entries for this followup.

Thanks in advance.
LVL 8
Johny BravoAsked:
Who is Participating?
 
paololabeCommented:
sorry, now I undrstand.
I don't see other way than define a scalar function i.e IsPrevious(@LeadId,@CurrentId)
and make a query like this:

select * from Table_1 where dbo.IsPrevious(19,LeadId)>0

P.


Create FUNCTION IsPrevious
(
	-- Add the parameters for the function here
	@LeadId int,@currentLeadId int
)
RETURNS bit
AS
BEGIN
	
	declare @res as bit
	declare @currParent as int
	set @res=0
	select @currParent=LeadId from Table_1 where followUpLeadId=@currentLeadId 
	if @currParent=@LeadId 
	begin
		set @res=1
	end
	else
		if @currParent>0	
			set @res=dbo.IsPrevious(@LeadId,@currParent)
		
		
	return @res

END
GO

Open in new window

0
 
paololabeCommented:
Hope I got it your needs:

select * from yourTable
where FollowUpDate <(select FollowUpDate  from yourTable where LeadId=19 )

P.
0
 
dqmqCommented:
Like this perhaps?

;with myCTE as
(Select * from t where leadid=16
union all
select t.* from mycte inner join t
   on t.followupleadid = mycte.leadid
  )
Select * from myCTE  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jogosCommented:
You want recursive lead 19, 18 and 16.

CTE or Common Table Expressions are very good in that, see example C for recursion
http://msdn.microsoft.com/en-us/library/ms175972.aspx

;WITH CTE_leads(LeadId, CustomerId, FollowUpDate, Remark, FollowUpLeadIdl,Level) AS 
(
    SELECT LeadId, CustomerId, FollowUpDate, Remark, FollowUpLeadIdl, 0 AS Level
    FROM dbo.leads
    WHERE LeadId = 19
    UNION ALL
    SELECT e.LeadId, e.CustomerId, e.FollowUpDate,e.Remark, e.FollowUpLeadIdl, d.Level + 1
    FROM dbo.leads AS e
        INNER JOIN cte_leads AS d
        ON e.FollowUpLeadIdl= d.LeadId
)
SELECT LeadId, CustomerId, FollowUpDate, Remark, FollowUpLeadIdl,Level
FROM cte_leads
ORDER BY Level;
GO

Open in new window

0
 
Johny BravoAuthor Commented:
hi Experts,

Thanks for your help.

paololabe:
Your query will return all the followups before 19, but I need only those followups which are 16 and 18.

dqmq and jogos::
 I don't have 16 i.e the original LeadId, 18 lead Id is the result of 16 and 19 LeadId is the result of 18.
Like this it can go upto any level. So if I am passing 19, I want 16 and 18.
Suppose if pass 18 then it should show 16.
I mean I need to move upwards, need to get previous records for the Lead

Thanks

0
 
Johny BravoAuthor Commented:
Thanks.
Exactly what I was looking for
0
 
dqmqCommented:
Oops, I misunderstood slightly.  using a CTE is a better solution, it just needs a little tweak:


;with myCTE as
(Select * from #t as t where leadid=19
union all
select t.* from #t as t inner join mycte
   on t.leadid = mycte.followupleadid
  )
Select * from myCTE  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.