How do I CTE for this

Question

4.27/5 (3 votes)

See more:

SQL-Server-2008R2

Hi CP Members,
This is my Sample table Structure,

SQL

Create table #table(advId int identity(1,1),name nvarchar(100),ranks nvarchar(5),ReferId int ,ReferalRank nvarchar(5))

insert into #table(name,ranks,ReferId,ReferalRank) values('King','MGR',0,'0')
insert into #table (name,ranks,ReferId,ReferalRank) values('Maceij','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Los1','ADV',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Griff','MGR',1,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('SA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('CASSANDRA','MGR',2,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Jason','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Smith','MGR',3,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',6,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Akee','MGR',10,'MGR')
insert into #table (name,ranks,ReferId,ReferalRank) values('Manasa','ADV',10,'MGR')

select *from #table

Let me have words about my Table Structure
 Here ,
 AdvId 1 is Referred by admin ,
  (2,3,4,5) are 1st level of 1
 (6,7,8,9) are 2nd Level of 1
 (10,11) are 3rd level of 1
 (12,13) are 4 th Level of 1
 same Logic  For Each Advisors

like this Structure [^]

How do i Select the Count Of Manager(how many manager under the agent) for Each Advisors up to 3 Levels

SQL

advId name     CountOfmanager
1     king       8            --2,3,5,6,7,8,9,10
2     Maceij     3            --6,7,10
3      los       2            --8,9
4     Los1       0            -- nobody
5     Griff      0        -- nobody
6     SA         2            -- 10,12
7     CASSANDRA  0            -- nobody
8     Jason      0
9     Smith      0
10    Akee       1             --12
11    manasa     0
12    Akee       0
13    Manasa     0

This is What I Tried.

SQL

with cte (advId,ReferId,Level)
as
(
 select AdvId,ReferId,1 as Level from table where ReferId=40
 union all
 select a.AdvId,a.ReferId ,Level+1 from table   as a inner join cte as b on b.AdvId=a.ReferId
 )
 select COUNT(b.AdvId From cte as a inner join table as b on a.advId=b.advId where a.level<=3 and b.ranks='MGR'

I hope its Clear ,Assist me to get the Result

Thanks,

Posted 29-Oct-14 22:06pm

King Fisher

Updated 31-Oct-14 1:33am

v8

Add a Solution

Comments

Herman<T>.Instance 30-Oct-14 5:30am

Are you familiair with CTE queries in SQL?

King Fisher 30-Oct-14 5:33am

this What I tried with CTE but I couldn't get Record For Entire Table

with cte (advId,ReferId)
as
(
select AdvId,ReferId from table where ReferId=40
union all
select a.AdvId_id,a.ReferId from table as a inner join cte as b on b.AdvId=a.ReferId
)
select * From cte as a inner join table as b on a.advId=b.advId

:(

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maddy selva · Accepted Answer · 2014-10-30T01:14:00

SQL

DECLARE @ID int
DECLARE @cnt int
DECLARE IDs CURSOR LOCAL FOR select advid from #table
Create table #table1(advId int ,name nvarchar(100),cont bigint)
OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN   
with cte (AdvId,ReferId,Level)
as
(
 select AdvId,ReferId,1 as Level from #table where ReferId=@ID
 union all
 select a.AdvId,a.ReferId ,Level + 1 from #table   as a inner join cte as b on b.AdvId=a.ReferId
 )
select @cnt= COUNT(b.AdvId) From cte as a inner join #table as b on a.advId=b.advId where a.level<=3 and b.ranks='MGR'

insert into #table1 select advid,name,'' from #table where advId=@ID
update #table1 set cont=@cnt where advId=@ID


    FETCH NEXT FROM IDs into @ID
END

CLOSE IDs
DEALLOCATE IDs
select * from #table1



Drop table #table1

Maciej Los · Accepted Answer · 2014-10-30T03:59:00

The simplest way:

SQL

SELECT advId, name, (SELECT COUNT(*) FROM #table WHERE ReferId =  t1.advId AND ReferalRank = 'MGR') AS CountOfMgr
FROM #table AS t1

[EDIT]
Sorry, King_Fisher, because above query does not meets your needs. It counts the employees in direct relationship to its chief ;)

Here is an example, which shows entire hierarchy ;)

SQL

;WITH HierarchicalList(EmpName, Hierarchy, EmpId, RefersTo, Lvl)
AS (
    SELECT e.name AS EmpName, CONVERT(NVARCHAR(MAX), e.name) AS Hierarchy, e.advId AS EmpId, CONVERT(NVARCHAR(MAX),'') AS RefersTo, 1 AS Lvl
    FROM #table  AS e
    WHERE e.ReferId = 0
    UNION ALL
    SELECT e.name as EmpName, CONCAT(Hierarchy, '->' , e.name) AS Hierarchy, e.advId  AS EmpID, CONCAT(RefersTo, e.ReferId, ',') AS RefersTo, Lvl + 1
    FROM #table  AS e JOIN HierarchicalList AS d ON e.ReferId  = d.EmpId
    )
SELECT *
FROM HierarchicalList
WHERE Lvl<4
ORDER BY RefersTo

Result:


EmpName		Hierarchy				EmpId	RefersTo Lvl
King		King					1				1
Maceij		King->Maceij			2	1,			2
Los			King->Los				3	1,			2
Los1		King->Los1				4	1,			2
Griff		King->Griff				5	1,			2
SA			King->Maceij->SA		6	1,2,		3
CASSANDRA	King->Maceij->CASSANDRA	7	1,2,		3
Jason		King->Los->Jason		8	1,3,		3
Smith		King->Los->Smith		9	1,3,		3

I believe you'll be able to change it to your needs, because you're very smart person ;)

Tip: Have a look at Christian's articles[^] about "SQL Wizardry...". He explains how to write such of query using CTE.

Cheers,
Maciej

King Fisher · Accepted Answer · 2014-10-31T01:16:00

Solution 3

Thank to Maciej Los and Maddy Selva.

The Question is Solved at StackOverFlow and i hereby post the Solution to remove the Question From UnAnswered Queue and this may help somebody. Thank you .:)

http://stackoverflow.com/questions/26669468/how-do-i-use-cte-for-this[^]

Posted 31-Oct-14 1:16am

King Fisher

Comments

Maciej Los 31-Oct-14 7:22am

Please, mark all valuable answers as a solution (including yours).

How do I CTE for this

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0