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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.