Hello,
We're importing tables from a SQL server and are running some SQL before it's imported from the "SQL Server Database" window and using the "Advanced Options">"SQL Statement" box into Power BI.
SELECT
count(distinct EMP_PERSON.PERSON_ID),
rtrim(EMP_PERSON.FORENAME1)+ ' ' + rtrim(EMP_PERSON.SURNAME),
EMP_REF.REFERENCE_NO,
REP_DEPT_NM.PARTY_NM,
HOL_ENT.ENTMT_REMNG_VL,
HOL_ENT.SCH_NM,
HOL_ENT.POSITION_NM,
HOL_ENT.REP_QUEUE_D,
TUER_CON_CAT_V.contract_category,
HOL_ENT.HOL_YR_ED,
HOL_ENT.ENTMT_UNIT,
HOL_ENT.ENTMT_TOTAL_VL,
HOL_ENT.ENTMT_TAKEN_VL,
HOL_ENT.ENTMT_SCHED_VL,
HOL_ENT.HOL_BOUGHT,
HOL_ENT.HOL_SOLD,
HOL_ENT.REP_QUEUE_T,
HOL_ENT.REPORT_ID,
HOL_ENT.POSITION_ID
FROM
TUER_CON_CAT_V RIGHT OUTER JOIN tparty POSITION_NAME ON (POSITION_NAME.party_id=TUER_CON_CAT_V.party_id)
INNER JOIN tparty_lnk ON (POSITION_NAME.party_id=tparty_lnk.party_id2)
INNER JOIN tparty CONTRACT_NM ON (CONTRACT_NM.party_id=tparty_lnk.party_id)
INNER JOIN tper_party ON (tper_party.party_id=CONTRACT_NM.party_id)
INNER JOIN tperson EMP_PERSON ON (tper_party.person_id=EMP_PERSON.person_id)
LEFT OUTER JOIN tper_reference EMP_REF ON (EMP_REF.person_id=EMP_PERSON.person_id)
INNER JOIN tleaver ON (tleaver.person_id=EMP_PERSON.person_id)
LEFT OUTER JOIN (
select
REP_OUT.OUT_HOL_ID,
REP_OUT.PERSON_ID,
REP_OUT.POSITION_ID,
REP_OUT.POSITION_NM,
REP_OUT.SCH_NM,
REP_OUT.HOL_YR_ED,
REP_OUT.ENTMT_UNIT,
REP_OUT.ENTMT_TOTAL_VL,
REP_OUT.ENTMT_TAKEN_VL,
REP_OUT.ENTMT_SCHED_VL,
REP_OUT.ENTMT_REMNG_VL,
coalesce(REP_OUT.HOL_BOUGHT,0) as HOL_BOUGHT,
coalesce(REP_OUT.HOL_SOLD,0) as HOL_SOLD,
REP_OUT.REPORT_ID,
REP_OUT.REP_QUEUE_D,
REP_OUT.REP_QUEUE_T
from
(
select
TNRG_OUT_HOL.OUT_HOL_ID,
TNRG_OUT_HOL.PERSON_ID,
TNRG_OUT_HOL.POSITION_ID,
TNRG_OUT_HOL.POSITION_NM,
TNRG_OUT_HOL.SCH_NM,
TNRG_OUT_HOL.HOL_YR_ED,
TNRG_OUT_HOL.ENTMT_UNIT,
TNRG_OUT_HOL.ENTMT_TOTAL_VL,
TNRG_OUT_HOL.ENTMT_TAKEN_VL,
TNRG_OUT_HOL.ENTMT_SCHED_VL,
TNRG_OUT_HOL.ENTMT_REMNG_VL,
TNRG_OUT_HOL.HOL_BOUGHT,
TNRG_OUT_HOL.HOL_SOLD,
TREP_QUEUE.REPORT_ID,
TREP_QUEUE.REP_QUEUE_D,
TREP_QUEUE.REP_QUEUE_T,
rank() over(
partition by
TNRG_OUT_HOL.PERSON_ID,
TNRG_OUT_HOL.POSITION_ID,
TNRG_OUT_HOL.HOL_YR_ED
order by
TREP_QUEUE.REP_QUEUE_D desc,
TREP_QUEUE.REP_QUEUE_T desc,
TNRG_OUT_HOL.OUT_HOL_ID
) as SEQ_NO
from
TNRG_OUT_HOL
inner join TREP_QUEUE on TNRG_OUT_HOL.REPORT_ID = TREP_QUEUE.REPORT_ID
and TREP_QUEUE.PROCESS_CD = 'C'
) REP_OUT
where
REP_OUT.SEQ_NO = 1
) HOL_ENT ON (EMP_PERSON.person_id=HOL_ENT.PERSON_ID)
LEFT OUTER JOIN tparty_lnk MGR_POS_LNK ON (POSITION_NAME.party_id=MGR_POS_LNK.party_id)
LEFT OUTER JOIN tparty MGR_POS ON (MGR_POS_LNK.party_id2=MGR_POS.party_id)
LEFT OUTER JOIN TUER_PARTY_LNK_V MGR_CON_LNK ON (MGR_POS.party_id=MGR_CON_LNK.PARTY_ID2)
RIGHT OUTER JOIN tparty_lnk REP_DEPT_LNK ON (POSITION_NAME.party_id=REP_DEPT_LNK.party_id)
RIGHT OUTER JOIN tparty REP_DEPT_NM ON (REP_DEPT_LNK.party_id2=REP_DEPT_NM.party_id)
WHERE
( (REP_DEPT_LNK.link_type_id='PN_RP_UNIT' or REP_DEPT_LNK.link_type_id IS NULL) )
AND ( REP_DEPT_NM.object_type='UNIT' )
AND ( CONTRACT_NM.object_type='CONT' )
AND ( (MGR_POS.object_type='POSITION' OR MGR_POS.object_type IS NULL) )
AND ( tparty_lnk.link_type_id = 'CONT' )
AND ( tparty_lnk.team_party_id IS NULL )
AND ( (MGR_POS_LNK.link_type_id='POSN_POSN' OR MGR_POS_LNK.link_type_id IS NULL) )
AND ( EMP_PERSON.non_emp_s='0' )
AND ( POSITION_NAME.object_type='POSITION' )
AND
(
( ( tleaver.starting_d ) <= '08/14/2019 09:34:51' AND (( tleaver.leaving_d ) >= '08/14/2019 09:34:51' OR ( tleaver.leaving_d ) is null) )
AND
( (( tparty_lnk.PARTY_LNK_D ) <= '08/14/2019 09:34:51' OR ( tparty_lnk.PARTY_LNK_D ) is null) AND (( tparty_lnk.PARTY_LNK_ED ) >= '08/14/2019 09:34:51' OR ( tparty_lnk.PARTY_LNK_ED ) is null) )
AND
( (( MGR_CON_LNK.PARTY_LNK_D )<='08/14/2019 09:34:51' OR ( MGR_CON_LNK.PARTY_LNK_D ) IS NULL)
AND (( MGR_CON_LNK.PARTY_LNK_ED )>='08/14/2019 09:34:51' OR ( MGR_CON_LNK.PARTY_LNK_ED ) IS NULL)
AND (( MGR_POS_LNK.party_lnk_d )<='08/14/2019 09:34:51' OR ( MGR_POS_LNK.party_lnk_d ) IS NULL)
AND (( MGR_POS_LNK.party_lnk_ed )>='08/14/2019 09:34:51' OR ( MGR_POS_LNK.party_lnk_ed ) IS NULL) )
AND
( (( REP_DEPT_LNK.PARTY_LNK_D ) <= '08/14/2019 09:34:51' OR ( REP_DEPT_LNK.PARTY_LNK_D ) IS NULL) AND
(( REP_DEPT_LNK.PARTY_LNK_ED ) >= '08/14/2019 09:34:51' OR
( REP_DEPT_LNK.PARTY_LNK_ED ) IS NULL) )
AND
HOL_ENT.REP_QUEUE_D >= dateadd("d",-1,( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ))
AND
( (( TUER_CON_CAT_V.inherited_from_date )<='08/14/2019 09:34:51' OR ( TUER_CON_CAT_V.inherited_from_date ) IS NULL) AND (( TUER_CON_CAT_V.inherited_to_date )>='08/14/2019 09:34:51' OR ( TUER_CON_CAT_V.inherited_to_date ) IS NULL) AND (( TUER_CON_CAT_V.contract_category_d )<='08/14/2019 09:34:51' OR ( TUER_CON_CAT_V.contract_category_d ) IS NULL) AND (( TUER_CON_CAT_V.contract_category_ed )>='08/14/2019 09:34:51' OR ( TUER_CON_CAT_V.contract_category_ed ) IS NULL) )
AND
TUER_CON_CAT_V.contract_category IN ( 'Employee' )
)
GROUP BY
rtrim(EMP_PERSON.FORENAME1)+ ' ' + rtrim(EMP_PERSON.SURNAME),
EMP_REF.REFERENCE_NO,
REP_DEPT_NM.PARTY_NM,
HOL_ENT.ENTMT_REMNG_VL,
HOL_ENT.SCH_NM,
HOL_ENT.POSITION_NM,
HOL_ENT.REP_QUEUE_D,
TUER_CON_CAT_V.contract_category,
HOL_ENT.HOL_YR_ED,
HOL_ENT.ENTMT_UNIT,
HOL_ENT.ENTMT_TOTAL_VL,
HOL_ENT.ENTMT_TAKEN_VL,
HOL_ENT.ENTMT_SCHED_VL,
HOL_ENT.HOL_BOUGHT,
HOL_ENT.HOL_SOLD,
HOL_ENT.REP_QUEUE_T,
HOL_ENT.REPORT_ID,
HOL_ENT.POSITION_IDTowards the end you will notice date time stamps.
Can these be automated in power BI without manually changing them each time the report is run?
Otherwise can it be accomplished elsewhere?
I'm running Version: 2.71.5523.941 32-bit (July 2019)
Thanks
Stuart
