sql - TSQL Recursive Query with Count -

sql - TSQL Recursive Query with Count -

i have recursive query have working part. here have far:

declare @table table(mgrqid varchar(64), qid varchar(64), ntid varchar(64), fullname varchar(64), lvl int, dt datetime, countofdirects int) emplist(mgrqid, qid, ntid, fullname, lvl, metadate) ( select top 1 mgrqid, qid, ntid, firstname+' '+lastname, 0, meta_logdate dbo.employeetable_historical qid in (select director dbo.attritiondirectors) , meta_logdate <= @pit order meta_logdate desc union select b.mgrqid, b.qid, b.ntid, b.firstname+' '+b.lastname, lvl+1, b.meta_logdate emplist cross apply dbo.fetch_directshistorical_by_qid(a.qid, @pit)b ) insert @table(mgrqid, qid, ntid, fullname, lvl, dt) select emplist.mgrqid , emplist.qid , emplist.ntid , emplist.fullname , emplist.lvl , emplist.metadate emplist order lvl option(maxrecursion 10)

now, @table has list of qids in it. need bring together employee table , find out how many people study each of qid's.

so, there need update happens @table provides count of employees study each of qid's.

here catch.. employee table historical table can contain multiple records same people. time piece of info updated new record created changes.

if wanted pull recent record 1 right now, utilize this:

select top 1 e.* employeetable_historical e e.qid = a.[subqid] , convert (date, getdate()) > convert (date, e.[meta_logdate]) order meta_logdate desc

the question..

i need able count of employees in historical table study straight each qid in @table. historical table has column called mgrqid. there way can count in original recursive query?

i recommend first @ approach you're taking. historical table you're dealing need select greatest meta_logdate given employee, in construction you've set here, you'll never select more 1 record matching attritiondirectors, top 1 in anchor query. such, i'd recommend lightweight function on base of operations query:

create function dbo.employeesasof(@date datetime) returns table homecoming select mgrqid, qid, ntid, firstname, lastname, meta_logdate dbo.employeetable_historical meta_logdate = (select max(meta_logdate) dbo.employeetable_historical b a.qid = b.qid , meta_logdate <= @date)

this allow recent record anyone, , long employeetable_historical has index on (qid, meta_logdate), view perform well.

having said that, looking @ recursive query, you'll want tweak recursive query somewhat:

create function emplist(@thisdate datetime) returns @emptbl table ( mgrqid varchar(10) , qid varchar(10) , ntid varchar(10) , name varchar(21) , meta_logdate datetime , directsthismany int ) begin ;with emplist ( select e.mgrqid, e.qid, e.ntid, e.firstname + ' ' + e.lastname name, e.meta_logdate dbo.employeesasof(@thisdate) e inner bring together dbo.attritiondirectors d on e.qid = d.qid union select e.mgrqid, e.qid, e.ntid, e.firstname + ' ' + e.lastname name, e.meta_logdate dbo.employeesasof(@thisdate) e inner bring together emplist d on e.mgrqid = d.qid ) insert @emptbl select a.mgrqid, a.qid, a.ntid, a.name, a.meta_logdate, count(b.qid) directsthismany emplist left bring together emplist b on a.qid = b.mgrqid grouping a.mgrqid, a.qid, a.ntid, a.name, a.meta_logdate homecoming end

in way, you'll able feed in date , read of tables, including counts history of date. self-join of cte enables current count of directs, 1 can't utilize aggregates in cte. function easy use, , indexing strategy should become apparent looking @ query plan in ssms. simple select * emplist(getdate()) give current situation.

sql tsql stored-procedures recursion