August 3, 2011 at 9:39 am
I know there has got to be a way to do this in T-SQL, I am a VB.Net developer and am kind of new to T-SQL.
Here is my question....
I'm tying to do the following query?
Select Manager, Field2, Field3, Parent_ID,
(Select Count(*) from Table1 where Parent_ID = Manager) as NumOfChildren
from Table1
As each one of the manager records changes I would like to count the children for output that would look like this.
Manager, Field2, Field3, Parent_Id, NumOfChildren
John, data,data,William,1
Greg, data,data,John,2
Phil, data,data,Greg,0
Mike,data,data,Greg,0
Can someone show me a way to do this?
Thanks in advance for your help,
August 3, 2011 at 10:16 am
Can you provide sample data and table schema?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2011 at 10:29 am
Ok, table structure looks like this.
CREATE TABLE [tbl_CDE_Hierarchy_Export] (
[Load Date] [smalldatetime] NULL ,
[Percent_Elapsed_Days] [float] NULL ,
[Level] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVP_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVP_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CM_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CM_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VM_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VM_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Coach_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Coach_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_ATTUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comp Modification] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UVERSE TOTAL] [float] NULL ,
[UVERSE TOTAL TARGET] [float] NULL ,
[Inward DSL Units] [float] NULL ,
[DSL Units Target] [float] NULL ,
[Inward DSL_Uverse_VOIP Comp Plan Retention Units] [float] NULL ,
[Inward DSL_Uverse_VOIP Comp Plan Retention Units Target] [float] NULL ,
[Inward DBS Units] [float] NULL ,
[DBS Units Target] [float] NULL ,
[Inward Wireless Units] [float] NULL ,
[Wireless Units Target] [float] NULL ,
[Inward DBS_Wireless Comp Plan Retention Units] [float] NULL ,
[Inward DBS_Wireless Comp Plan Retention Units Target] [float] NULL ,
[Posted Sales Revenue (ABR)] [float] NULL ,
[Sales Revenue Target] [float] NULL ,
[TACRFT Q1 Score YTD] [float] NULL ,
[Q1 Positive Response Count Target] [float] NULL ,
[TACRFT Qualifier YTD] [float] NULL ,
[TACRFT Qualifier Target] [float] NULL ,
[Inward Save Units] [float] NULL ,
[Save Units Target] [float] NULL ,
[Total Save Unit Percent] [float] NULL ,
[SAVE RATE TARGET] [float] NULL ,
[Inward IPTV Units] [float] NULL ,
[IPTV Video Units Target] [float] NULL ,
[Inward Uverse HSIA Units] [float] NULL ,
[Uverse HSIA Units Target] [float] NULL ,
[Inward VOIP Units] [float] NULL ,
[VOIP Units Target] [float] NULL ,
[Net Inward DSL Basic] [float] NULL ,
[Net Inward DSL Express] [float] NULL ,
[Net Inward DSL Pro] [float] NULL ,
[Net Inward DSL Elite] [float] NULL ,
[Inward Basic IPTV Units] [float] NULL ,
[Inward Family IPTV Units] [float] NULL ,
[Inward U100 IPTV Units] [float] NULL ,
[Inward U200 IPTV Units] [float] NULL ,
[Inward U300 IPTV Units] [float] NULL ,
[Inward U450 IPTV Units] [float] NULL ,
[Inward UV HSIA BASIC Units] [float] NULL ,
[Inward UV HSIA EXPRESS Units] [float] NULL ,
[Inward UV HSIA PRO Units] [float] NULL ,
[Inward UV HSIA ELITE Units] [float] NULL ,
[Inward UV HSIA MAX Units] [float] NULL ,
[Inward UV HSIA MAX18 Units] [float] NULL ,
[Inward UV HSIA MAX24 Units] [float] NULL ,
[Inward UV VOIP 250 Units] [float] NULL ,
[Inward UV VOIP UNLIMITED Units] [float] NULL ,
[Inward ACLN Units] [float] NULL ,
[ACLN Units Target] [float] NULL ,
[Inward ACLN Save Units] [float] NULL ,
[ACLN Save Units Target] [float] NULL ,
[ACLN Save Units Percent] [float] NULL ,
[AL SAVE RATE TARGET] [float] NULL ,
[Inward DSL Save Units] [float] NULL ,
[DSL Save Units Target] [float] NULL ,
[DSL Save Units Percent] [float] NULL ,
[DSL SAVE RATE TARGET] [float] NULL ,
[Inward Uverse Save Units] [float] NULL ,
[Uverse Save Units Target] [float] NULL ,
[Uverse Save Units Percent] [float] NULL ,
[UV SAVE RATE TARGET] [float] NULL ,
[Saves Net Positive Revenue] [float] NULL ,
[Saves Net Positive Revenue Target] [float] NULL ,
[TACRFT Q1 Score MTD] [float] NULL ,
[TACRFT Q7 Score MTD] [float] NULL ,
[Net Inward DSL LS Basic] [float] NULL ,
[Net Inward DSL LS Elite] [float] NULL ,
[Net Inward DSL LS Express] [float] NULL ,
[Net Inward DSL LS Pro] [float] NULL ,
[Net Inward DSL DL Basic] [float] NULL ,
[Net Inward DSL DL Elite] [float] NULL ,
[Net Inward DSL DL Express] [float] NULL ,
[Net Inward DSL DL Pro] [float] NULL ,
[DSL Chargeback] [float] NULL ,
[IPTV Chargeback Units] [float] NULL ,
[UV HSIA CHARGEBACK Units] [float] NULL ,
[UV VOIP CHARGEBACK] [float] NULL ,
[Calls Answered] [float] NULL ,
[Average Handle Time] [float] NULL ,
[Adherence Time Percent] [float] NULL ,
[Utilization Time] [float] NULL ,
[Transferred Calls] [float] NULL ,
[Repeats 60 Day] [float] NULL ,
[BB Total] [float] NULL ,
[BB Target] [float] NULL ,
[Video Total] [float] NULL ,
[Video Target] [float] NULL ,
[DSL 3M Plus] [float] NULL ,
[DSL LS 3M Plus] [float] NULL ,
[DSL DL 3M Plus] [float] NULL ,
[HSIA 6M Plus] [float] NULL ,
[IPTV U200 Plus] [float] NULL ,
[IPTV U300 Plus] [float] NULL ,
[Inward CT Units] [float] NULL ,
[CT Units Target] [float] NULL ,
[Inward SP Units] [float] NULL ,
[SP Units Target] [float] NULL ,
[Evaluation Observation Count] [float] NULL ,
[Evaluation Points Earned] [float] NULL ,
[Evaluation Points Possible] [float] NULL ,
[Inward CN VM Units] [float] NULL ,
[Inward Inline Units] [float] NULL ,
[Inward UM Units] [float] NULL ,
[Inward DOM LD Units] [float] NULL ,
[Inward INTL LD Units] [float] NULL ,
[Inward LD Total Units] [float] NULL ,
[Inward LD PIC Units] [float] NULL ,
[Inward Choice Basic Units] [float] NULL ,
[Inward Choice Enhanced Units] [float] NULL ,
[Inward All Distance Units] [float] NULL ,
[Inward Package Units] [float] NULL ,
[Inward DSL LS Units] [float] NULL ,
[DSL LS Units Target] [float] NULL ,
[Inward DSL DIR Units] [float] NULL ,
[DSL DIR Units Target] [float] NULL ,
[HSIA 12M Plus] [float] NULL ,
[Inward CT Units DSL] [float] NULL ,
[Inward CT Units Uverse] [float] NULL ,
[Inward SP Units DSL] [float] NULL ,
[Inward SP Units Uverse] [float] NULL ,
[Headcount] [float] NULL ,
[Inward UV HSIA REG BASIC Units] [float] NULL ,
[Inward UV HSIA REG EXPRESS Unit] [float] NULL ,
[Inward UV HSIA REG PRO Units] [float] NULL ,
[Inward UV HSIA REG ELITE Units] [float] NULL ,
[Inward UV HSIA REG MAX Units] [float] NULL ,
[Inward UV HSIA REG MAX18 Units] [float] NULL ,
[Inward UV HSIA REG MAX24 Units] [float] NULL ,
[Inward UV HSIA REG Total Units] [float] NULL ,
[Uverse HSIA Reg Units Target] [float] NULL ,
[Inward UV IPDSL BASIC Units] [float] NULL ,
[Inward UV IPDSL EXPRESS Units] [float] NULL ,
[Inward UV IPDSL PRO Units] [float] NULL ,
[Inward UV IPDSL ELITE Units] [float] NULL ,
[Inward UV IPDSL MAX Units] [float] NULL ,
[Inward UV IPDSL MAX18 Units] [float] NULL ,
[Inward UV IPDSL MAX24 Units] [float] NULL ,
[Inward UV IPDSL Total Units] [float] NULL ,
[Uverse IPDSL Units Target] [float] NULL ,
[Inward UV ERHSI PRO Units] [float] NULL ,
[Inward UV ERHSI ELITE Units] [float] NULL ,
[Inward UV ERHSI MAX Units] [float] NULL ,
[Inward UV ERHSI Total Units] [float] NULL ,
[Uverse ERHSI Units Target] [float] NULL ,
[HSIA Reg 6M Plus] [float] NULL ,
[HSIA Reg 12M Plus] [float] NULL ,
[IPDSL 3M Plus] [float] NULL ,
[IPDSL 6M Plus] [float] NULL ,
[IPDSL 12M Plus] [float] NULL
) ON [PRIMARY]
In this table is data about each Sales Associate their managers and they're subbordinates.
Everyone is linked by ATTUID, so the GM_ATTUID represents all people that report to that GM, the same is true with VP_ATTUID represents all people that report to that VP. The ATTUID represents the person that the record belongs to.
I have some code like this, but it didn't pull back any results because I need for the ATTUID for each record to be passed to either the GM_ATTUID, the VP_ATTUID, the CM_ATTUID, etc... to pull back their respective children.
Here is the code that I have initially...
SELECT [LOAD DATE], [LEVEL], [TITLE], [ATTUID], [NAME],
(SELECT COUNT(*)
FROM dbo.tbl_CDE_Hierarchy_Export
WHERE GM_ATTUID = [ATTUID] AND TITLE = 'REP') AS Universe
FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = 'DP5116' AND TITLE = 'GM'
I'm trying to get all the records for that GM that have a level as 'REP' or all of that GM's sales representatives.
August 3, 2011 at 10:49 am
Without the sample data I'll venture that something like this may work
SELECT [LOAD DATE], [LEVEL], [TITLE], [ATTUID], [NAME],
(SELECT COUNT(*)
FROM dbo.tbl_CDE_Hierarchy_Export
WHERE (SVP_ATTUID = [ATTUID]
OR VP_ATTUID = [ATTUID]
OR GM_ATTUID = [ATTUID]
OR CM_ATTUID = [ATTUID]
OR VM_ATTUID = [ATTUID]
OR Coach_ATTUID = [ATTUID]
)
AND TITLE = 'REP'
) AS Universe
FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = 'DP5116' AND TITLE = 'GM'
I would seriously look at normalizing that structure. Unless this is a warehouse database, it should be normalized. Changing this table could improve query performance as well as simplify some of these queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2011 at 11:00 am
This is great, and I have long considered normalizing this table. I inherit this data from another system that isn't normalized, actually it's coming in from a spreadsheet.
But I do have on question. The way you have the solution coded is the way that I was thinking as well, so does that mean the every time a record's ATTUID changes the code will take that record's ATTUID and compare it to the entire dataset's GM_Attuid?
August 3, 2011 at 11:37 am
Yes
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2011 at 12:05 pm
You are awesome!!!
Thanks for your help:cool:
August 3, 2011 at 2:27 pm
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply