validate data discripencies in final_preiction_latest

updated on 8th May, 2023-

 

priority 1–> 8th May

priority 2–> 8th May

FC% have 396340 different rows 

SP% have 364545 different rows

AC% have 396340 different rows

DC% have 396340 different rows

SC% have 396340 different rows

DemandPower have 253848 different rows

SupplyPower have 364545 different rows

FCError_On_Lag3_Forecast have 2450 different rows

AMPCommentCount have 1646 different rows --2

MSA_PCT have 534401 different rows  --Ignore

SALES_AGG_DAILY have 591177 different rows

COUNTRY have 16059 different rows  --2

SEGMENT have 104967 different rows  --2

AMPPLAMMER have 365790 different rows  --2

Manufacturing location have 105722 different rows --2

SOURCE have 15581 different rows --2

ForecastClass have 8222 different rows --2

Region have 84683 different rows --2 (may have mismatches with source, we may ignore)

PPS have 243349 different rows --2

STD_PROD_LTA_1_MNTH have 19460 different rows  --3

STD_PROD_LTA_2_MNTH have 34646 different rows  --3

STD_PROD_LTA_3_MNTH have 49315 different rows  --3

STD_PROD_LTA_4_MNTH have 51990 different rows  --3

STD_PROD_LTA_5_MNTH have 51783 different rows  --3

AVG_PROD_LTA_1_MNTH have 19775 different rows  --3

AVG_PROD_LTA_2_MNTH have 35598 different rows  --3

AVG_PROD_LTA_3_MNTH have 50964 different rows  --3

AVG_PROD_LTA_4_MNTH have 53854 different rows  --3

AVG_PROD_LTA_5_MNTH have 53716 different rows  --3

STD_MKT_QA_1_MNTH have 8458 different rows  --3

STD_MKT_QA_2_MNTH have 10798 different rows  --3

STD_MKT_QA_3_MNTH have 11431 different rows  --3

STD_MKT_QA_4_MNTH have 11517 different rows  --3

STD_MKT_QA_5_MNTH have 11187 different rows  --3

AVG_MKT_QA_1_MNTH have 8570 different rows  --3

AVG_MKT_QA_2_MNTH have 10979 different rows  --3

AVG_MKT_QA_3_MNTH have 11597 different rows  --3

AVG_MKT_QA_4_MNTH have 11650 different rows  --3

AVG_MKT_QA_5_MNTH have 11309 different rows  --3

REAL_SR have 12041 different rows   {}{}1

REAL_SO_SCM have 849 different rows  {}{}1

REAL_SR_SCM have 34701 different rows  {}{}1

 

-----------{}{}check manu loc with source-------

select count (1)
from
oscar.web.sr_alert (nolock)old inner join FINAL_PREDICTION_LATEST(nolock) new on old.material=new.material
and old.location=new.location
and old.version=new.week
where isnull(old.[Manufacturing location],'')<>isnull(new.[Manufacturing location],'')
and old.horizon=1

 

-----101694 differences

 

------  9th May --

Rafal updated priority 1 and items are done 2 

Rafal updated started on priority 3

sales_agg daily not matching with source and final{_}prediction, Rafal to check -

select top 100  new.SALES_AGG_DAILY,old.SALES_AGG_DAILY,new.material,new.location,old.week
from
final_prediction (nolock)old inner join FINAL_PREDICTION_LATEST(nolock) new on old.material=new.material
and old.location=new.location
and old.week=new.week
where isnull(old.SALES_AGG_DAILY,0)<>isnull(new.SALES_AGG_DAILY,0)
and new.week='2022-07-04' and old.week='2022-07-04'
and old.material='F000043102'  and new.material='F000043102' 
--order by old.week desc

select  actual_delivery_quantity,* from imp_HANA_SalesDelivery_int where material='F000043102' and plantcountry='brazil' and CAST(goods_issue_date AS DATE)>='2022-07-04'
AND CAST(goods_issue_date AS DATE)<DATEADD(WEEK,1,'2022-07-04') AND  delivery_type = 'ZDL'