Notes

[DB Instance: MSTR_PDW(1)(1)]
select a01.tc_project_key tc_project_key,
a01.tc_tag_name tc_tag_name,
a01.tc_tag_value tc_tag_value
from vw_tc_project_tag_values a01
left outer join tc_project_lu a02
on (a01.tc_project_key = a02.tc_project_key)
where (a01.tc_project_key in ([?F5A94969459B909AB2704E960F732BD0[?)
and a02.tc_company_key in ([?67590E384BB7CAC987FB5DBFB8F886EF[?))

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
create table ##ZZOP00(
tc_project_key INTEGER,
tc_tag_name NVARCHAR(255),
tc_tag_value NVARCHAR(255))

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
insert into ##ZZOP00 values ([Analytical Engine Results: tc_project_key, tc_tag_name, tc_tag_value])

[DB Instance: MSTR_PDW(1)(1)]
select a01.tc_company_key tc_company_id,
a01.tc_project_key tc_project_key,
a01.tc_project_id tc_project_id,
a01.name ProjecName,
a01.createdby_tc_user_key tc_user_admin_key
from tc_project_lu a01
where (a01.tc_project_key in ([?F5A94969459B909AB2704E960F732BD0[?)
and a01.tc_company_key in ([?67590E384BB7CAC987FB5DBFB8F886EF[?))

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
create table ##ZZOP01(
tc_company_id INTEGER,
tc_project_key INTEGER,
tc_project_id INTEGER,
ProjecName VARCHAR(256),
tc_user_admin_key INTEGER)

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
insert into ##ZZOP01 values ([Analytical Engine Results: tc_company_id, tc_project_key, tc_project_id, ProjecName, tc_user_admin_key])

[DB Instance: MSTR_PDW(1)(1)]
select CONCAT(a01.firstname, a01.lastname) CustCol_4,
a01.tc_user_admin_key tc_user_admin_key
from tc_user_admin_lu a01

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
create table ##ZZOP02(
CustCol_4 NVARCHAR(255),
tc_user_admin_key INTEGER)

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
insert into ##ZZOP02 values ([Analytical Engine Results: CustCol_4, tc_user_admin_key])

[DB Instance: MSTR_PDW(1)(1)]
select a01.Day_Key Day_Key,
a01.Day_Timestamp Day_Timestamp
from time_lu a01
where a01.Day_Key between [?712A0A3A487584B77A402BB5FE6011AF[? and [?C878787B434274206B029EA74E34FCF3[?

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
create table ##ZZOP03(
Day_Key INTEGER,
Day_Timestamp DATETIME)

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
insert into ##ZZOP03 values ([Analytical Engine Results: Day_Key, Day_Timestamp])

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
select a11.email email,
a11.gender gender,
a11.overall_percentile overall_percentile,
a11.overall_rating overall_rating,
a11.overall_recommendation overall_recommendation,
a11.tc_project_key tc_project_key,
max(a13.tc_project_id) tc_project_id,
max(a13.ProjecName) ProjecName,
a11.score_tag score_tag,
max(case when a11.score_tag='ToleranceforPressure' then 'ToleranceforPressure'
when a11.score_tag='CEB_Lone Wolf' then 'Lone Wolf'
when a11.score_tag='CEB_Challenger' then 'Challenger'
when a11.score_tag='Sales Focus - 2014' then 'Sales Focus'
when a11.score_tag='salesPotentialtwo' then 'Sales Potential'
when a11.score_tag='Deductive Reasoning Ability' then 'Deductive Reasoning'
when a11.score_tag='CEB_ProblemSolver' then 'Problem Solver'
when a11.score_tag='CEB_RelationshipBuilder' then 'Relationship Builder'
when a11.score_tag='CEB_HardWorker' then 'Hard Worker'
when a11.score_tag='OPQ_HardWorker' then 'Hard Worker'
when a11.score_tag='OPQ_Challenger' then 'Challenger'
when a11.score_tag='OPQ_LoneWolf' then 'Lone Wolf'
when a11.score_tag='OPQ_ProblemSolver' then 'Problem Solver'
when a11.score_tag='OPQ_RelationshipBuilder' then 'Relationship Builder'
when a11.score_tag='VATMPNR_SCORE' then 'Numerical Reasoning'
else (a11.score_tag) end) CustCol_2,
a11.session_completedon_id session_completedon,
max(a11.session_completedon) session_completedon0,
a11.tc_candidate_id tc_candidate_id,
max(a11.first_name) first_name,
max(a11.last_name) last_name,
a11.session_startedon_id session_startedon,
max(a11.session_startedon) session_startedon0,
a11.tc_company_key tc_company_id,
max(a16.Comp_Desc) company_name,
a11.session_startedon_id Day_Key,
max(a15.Day_Timestamp) Day_Timestamp,
a11.score_value score_value,
a12.tc_tag_name tc_tag_name,
a13.tc_user_admin_key tc_user_admin_key,
a14.CustCol_4 CustCol_4,
a12.tc_tag_value tc_tag_value,
max(a11.score_value) WJXBFS1
from CH_Candidate_Score_Fact a11
left outer join ##ZZOP00 a12
on (a11.tc_project_key = a12.tc_project_key)
left outer join ##ZZOP01 a13
on (a11.tc_project_key = a13.tc_project_key)
left outer join ##ZZOP02 a14
on (a13.tc_user_admin_key = a14.tc_user_admin_key)
left outer join ##ZZOP03 a15
on (a11.session_startedon_id = a15.Day_Key)
left outer join CH_mstr_companylist a16
on (a11.tc_company_key = a16.Comp_Key)
where (a11.score_tag in (N'OPQ_PROBLEMSOLVER', N'OPQ_RELATIONSHIPBUILDER', N'OPQ_HARDWORKER', N'OPQ_LONEWOLF', N'OPQ_CHALLENGER', N'VATMPNR_SCORE', N'ToleranceforPressure', N'CEB_Lone Wolf', N'CEB_Challenger', N'Sales Focus - 2014', N'salesPotentialtwo', N'Deductive Reasoning Ability', N'CEB_ProblemSolver', N'CEB_RelationshipBuilder', N'CEB_HardWorker')
and a11.session_startedon_id between [?712A0A3A487584B77A402BB5FE6011AF[? and [?C878787B434274206B029EA74E34FCF3[?
and a11.tc_project_key in ([?F5A94969459B909AB2704E960F732BD0[?)
and a11.tc_company_key in ([?67590E384BB7CAC987FB5DBFB8F886EF[?))
group by a11.email,
a11.gender,
a11.overall_percentile,
a11.overall_rating,
a11.overall_recommendation,
a11.tc_project_key,
a11.score_tag,
a11.session_completedon_id,
a11.tc_candidate_id,
a11.session_startedon_id,
a11.tc_company_key,
a11.session_startedon_id,
a11.score_value,
a12.tc_tag_name,
a13.tc_user_admin_key,
a14.CustCol_4,
a12.tc_tag_value


[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
drop table ##ZZOP00

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
drop table ##ZZOP01

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
drop table ##ZZOP02

[DB Instance: MSTR_Talent Analytics(1)(1)(1)]
drop table ##ZZOP03