asked on

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.

paololabe

Hope I got it your needs:

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

P.

dqmq

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

jogos

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

Johny Bravo

ASKER

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

ASKER CERTIFIED SOLUTION

paololabe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Johny Bravo

ASKER

Thanks.
Exactly what I was looking for

dqmq

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