QAD BI v3 - Tech Level 2 Certification - Part 4 > Performance Issues – Class Excercise > stage_inv_bal_mth_date1
  PPT
stage_inv_bal_mth_date1
 
Here’s the query in question –
SELECT
fact_inv_transaction.source_system_code,
fact_inv_transaction.domain_code,
ISNULL(dim_effective_date.dim_effective_date_key,0) dim_effective_date_key,
MAX(ded2.effective_date),
ISNULL(dim_effective_date.effective_fin_month,0) effective_fin_month,
ISNULL(dim_effective_date.effective_fin_day_in_month,0) effective_fin_day_in_month,
@v_dss_update_time
FROM fact_inv_transaction,
dim_effective_date,
dim_effective_date ded2
WHERE dim_effective_date.effective_date = DATEADD(dd,DATEDIFF(dd,0,@v_INV_LAST_IN_MSTR_EXTRACT),0)
AND dim_effective_date.source_system_code = fact_inv_transaction.source_system_code
AND dim_effective_date.domain_code = fact_inv_transaction.domain_code
AND dim_effective_date.source_system_code = ded2.source_system_code
AND dim_effective_date.domain_code = ded2.domain_code
AND dim_effective_date.effective_fin_month = ded2.effective_fin_month
GROUP BY fact_inv_transaction.source_system_code
, fact_inv_transaction.domain_code
, dim_effective_date.dim_effective_date_key
, dim_effective_date.effective_fin_month
, dim_effective_date.effective_fin_day_in_month
UNION
SELECT
fact_inv_transaction.source_system_code,
fact_inv_transaction.domain_code,
ISNULL(MAX(ded2.dim_effective_date_key),0) dim_effective_date_key,
MAX(ded2.effective_date),
ISNULL(ded2.effective_fin_month,0) effective_fin_month,
ISNULL(max(ded2.effective_fin_day_in_month),0) effective_fin_day_in_month,
@v_dss_update_time
FROM fact_inv_transaction,
(SELECT source_system_code, domain_code, MAX(fiscal_period) max_fin_month
FROM fact_inv_mth_balance
GROUP BY source_system_code, domain_code) max_fm,
dim_effective_date,
dim_effective_date ded2
WHERE dim_effective_date.effective_date = DATEADD(dd,DATEDIFF(dd,0,@v_INV_LAST_IN_MSTR_EXTRACT),0)
AND dim_effective_date.source_system_code = fact_inv_transaction.source_system_code
AND dim_effective_date.domain_code = fact_inv_transaction.domain_code
AND dim_effective_date.source_system_code = max_fm.source_system_code
AND dim_effective_date.domain_code = max_fm.domain_code
AND max_fm.source_system_code = ded2.source_system_code
AND max_fm.domain_code = ded2.domain_code
AND max_fm.max_fin_month <= ded2.effective_fin_month
AND ded2.effective_fin_month < dim_effective_date.effective_fin_month
GROUP BY fact_inv_transaction.source_system_code
, fact_inv_transaction.domain_code
, dim_effective_date.dim_effective_date_key
, ded2.effective_fin_month
, dim_effective_date.effective_fin_day_in_month