i have create report shows supervisory relationship hierarchy @ organization. there many different ways supervisory line can set up. below of them.
vice president-associate vp-director-assistant director-supervisor-worker vice president-associate vp-director-supervisor - worker vice president-director-assistant director-supervisor-worker vice president-director-worker vice president-director-director b-worker vice president-director-director b-supervisor->worker vice president-associate vp-worker vice president-worker vice president-director-assistant director vice president-director-worker these table each role in hierarchy; so, vp table, avp table, director table , on. if hierarchy line not contain role value should comes null. wrote program start worker table , left join tables there. have tried factor in possible joins (see program below). using different join combinations using supervisor_position_nbr=position_nbr there many different ways the lines here small example:
vice president associate_vp director director b assistant_director supervisor worker ============== ============ ======== ========== ================== ========== ====== jane smith joe roberts marcy james null null james mare matt g
jane smith joe roberts marcy james null null james mare jess d jane smith joe roberts marcy james null null frank jay carol r jane smith null marty bob null sonja null sam smith jane smith null marty bob null sonja null nate lowe jane smith null null null null null ralph cole
my 2 issues are:
- when have supervisory line includes vp , director , worker both director , worker report directly vp, when query run director drops off , worker shows. this:
vice president associate_vp director director b assistant director supervisor worker
============== =========== ======= ========== ================= ========= ======= jane smith null null null null null marcyjames
when should bring this:
vice president associate_vp director director b assistant director supervisor worker
============= =========== ======== ========== ======== ======= ========= ===== jane smith null null null null null marcy james
jane smith null joe roberts null null null null
- is there cleaner more efficient way write query?
any assistance appreciated.
select vp.vice_president, avp.associate_vp, d.director, db.director_b, ad.assistant_director, s.supervisor, w.worker gw_ppp.dbo.vw_worker w left join gw_ppp.dbo.vw_manager_sup_role s on w.supervisor_position_nbr=s.position_nbr left join gw_ppp.dbo.vw_adir_role ad on w.supervisor_position_nbr=ad.position_nbr or s.supervisor_position_nbr=ad.position_nbr left join gw_ppp.dbo.vw_dir_role_b db on w.supervisor_position_nbr=db.position_nbr or s.supervisor_position_nbr=db.position_nbr or ad.supervisor_position_nbr=db.position_nbr left join gw_ppp.dbo.vw_dir_role d on w.supervisor_position_nbr=d.position_nbr or s.supervisor_position_nbr=d.position_nbr or ad.supervisor_position_nbr=d.position_nbr or db.supervisor_position_nbr=d.position_nbr left join gw_ppp.dbo.vw_avp_role avp on w.supervisor_position_nbr=avp.position_nbr or s.supervisor_position_nbr=avp.position_nbr or ad.supervisor_position_nbr=avp.position_nbr or d.supervisor_position_nbr=avp.position_nbr left join gw_ppp.dbo.vw_vp_role vp on w.supervisor_position_nbr=vp.position_nbr or s.supervisor_position_nbr=vp.position_nbr or ad.supervisor_position_nbr=vp.position_nbr or d.supervisor_position_nbr=vp.position_nbr or avp.supervisor_position_nbr=vp.position_nbr order w.worker here programs use create role views
create view [vw_vp_role] select file_nbr, job_title, (first_name + ' ' + last_name) vice_president, position_nbr , supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) vp_manager [new_ees].[dbo].[adpfile] job_title in ('vice president','sr vp & chief financial officer','sr. vice president'); create view [vw_avp_role] select file_nbr, job_title, (first_name + ' ' + last_name) associate_vp, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) avp_manager [new_ees].[dbo].[adpfile] job_title in ('associate vice president','senior performance officer'); create view [vw_dir_role] select file_nbr, job_title, (first_name + ' ' + last_name) director, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) dir_manager [new_ees].[dbo].[adpfile] job_title in ('director','chief information officer','deputy controller','director of operations & staff dev') , not (first_name + ' ' + last_name) in ('michelle james','edward lachterman', 'nafissa hannat') create view [vw_dir_role_b] select file_nbr, job_title, (first_name + ' ' + last_name) director_b, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) dir_manager [new_ees].[dbo].[vw_adpfile] (first_name + ' ' + last_name) in ('michelle james','edward lachterman', 'nafissa hannat') create view [vw_adir_role] select file_nbr, job_title, (first_name + ' ' + last_name) assistant_director, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) ad_manager [new_ees].[dbo].[adpfile] job_title= 'assistant director' create view [vw_manager_sup_role] select file_nbr, job_title, (first_name + ' ' + last_name) supervisor, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) sup_manager [new_ees].[dbo].[vw_adpfile] job_title in ('supervisor','campus administration manager','compensation & benefits manager', 'cottage manager', 'manager','office manager','operations manager','recruiting manager','special projects/rep & compliance manager', 'talent manager','youth development coordinator') or (first_name + ' ' + last_name)='rosa nunez pena'; create view [vw_worker] select file_nbr, job_title, (first_name + ' ' + last_name) worker, position_nbr, supervisor_position_nbr, (mngr_fname + ' ' + mngr_lname ) worker_manager [new_ees].[dbo].[adpfile] job_title not in ('associate vice president','vice president','sr vp & chief financial officer','sr. vice president', 'director','chief information officer','deputy controller','director of operations & staff dev', 'assistant director','supervisor','campus administration manager','compensation & benefits manager', 'cottage manager', 'manager','office manager','operations manager','recruiting manager','special projects/rep & compliance manager', 'talent manager','youth development coordinator','senior performance officer') , not (first_name + ' ' + last_name)='rosa nunez pena';
consider common table expression following...
with org ( select ee.position_nbr, ee.empname, ee.title employee ee union select ee.position_nbr, o.empname, o.title employee ee join org o on (o.position_nbr = ee.supervisorid) ) select * org (position_nbr in (select position_nbr employee title = 'worker'))
for each worker recursive cte retrieves of employees in worker's management chain.
you can take , pivot it, format want. missing levels of management automatically have null value in them.
look @ this sql fiddle full example. brevity, included first 3 levels of management should able follow pattern add others.
Comments
Post a Comment