| Address: Chennai, Tamil Nadu |
Derived Table: Reliability_FDEG_Intl_Std_Days
select
@Prompt('Fiscal Month', 'N', , mono, free) as fiscal_mth_nbr,
@Prompt('Fiscal Year', 'N', , mono, free) as fiscal_yr_nbr,
cmnth.true_std_delv_days as true_std_delv_days,
-- Mod
cmnth.adf as cmnth_adf,
cmnth.adt as cmnth_adt,
case
when sum(cmnth.adt)0
then sum(cast(cmnth.adf as decimal(18, 6)))/sum(cast(cmnth.adt as decimal(18, 6)))
else 0
end as cmnth_failure_rate,
case when sum(smpr.b_adf) is null
then 0
when fiscal_yr_nbr = 2012
then 0
else sum(smpr.b_adf)-cmnth.adf
end as cmnth_yoy_adf_diff,
-- mod
cmnth.ytd_adf as cmnth_ytd_adf,
cmnth.ytd_adt as cmnth_ytd_adt,
case
when sum(cmnth.ytd_adt)0
then sum(cast(cmnth.ytd_adf as decimal(18, 6)))/sum(cast(cmnth.ytd_adt as decimal(18, 6)))
else 0 end as cmnth_ytd_failure_rate,
case when sum(smpr.b_ytd_adf) is null
then 0
when fiscal_yr_nbr = 2012
then 0
else sum(smpr.b_ytd_adf)-cmnth.ytd_adf
end as cmnth_ytd_yoy_adf_diff
from
(
select
case
when true_std_delv_days 5
then 5
-- when true_std_delv_days is null then 'NA'
else true_std_delv_days end as true_std_delv_days,
sum(case
when dates.bus_days0 then cast(failures as decimal(18, 6))/cast(dates.bus_days as decimal(18, 6)) else 0 end) as adf,
sum(case when dates.bus_days0 then cast(base as decimal(18, 6))/cast(dates.bus_days as decimal(18, 6)) else 0 end )as adt,
sum(case when ytd_dates.bus_days0 then cast(ytd_failures as decimal(18, 6))/cast(ytd_dates.bus_days as decimal(18, 6)) else 0 end) as ytd_adf,
sum(case when ytd_dates.bus_days0 then cast(ytd_base as decimal(18, 6))/cast(ytd_dates.bus_days as decimal(18, 6)) else 0 end) as ytd_adt
group by 1
from
(
select
true_std_delv_days,
sum(case when period_nbr=@Prompt('Fiscal Month', 'N', , mono, free) and failure_flg=1 then shpmt_qty else 0 end) as failures,
sum(case when period_nbr=@Prompt('Fiscal Month', 'N', , mono, free) and base_flg=1 then shpmt_qty else 0 end) as base,
sum(case when failure_flg=1 then shpmt_qty else 0 end) as ytd_failures,
sum(case when base_flg=1 then shpmt_qty else 0 end) as ytd_base
from fxgnd_il_ots_sdl_dly_summ
where period_nbr5
then 5
--when true_std_delv_days is null then 'NA'
else b.true_std_delv_days end as b_true_std_delv_days,
case when bdates.bus_days0 then cast(b.ab_failures as decimal(18, 6))/cast(bdates.bus_days as decimal(18, 6)) else 0 end as b_adf,
case when bdates.bus_days0 then cast(b.ab_base as decimal(18, 6))/cast(bdates.bus_days as decimal(18, 6)) else 0 end as b_adt,
case when bytd_dates.bus_days0 then cast(b.ab_ytd_failures as decimal(18, 6))/cast(bytd_dates.bus_days as decimal(18, 6)) else 0 end as b_ytd_adf,
case when bytd_dates.bus_days0 then cast(b.ab_ytd_base as decimal(18, 6))/cast(bytd_dates.bus_days as decimal(18, 6)) else 0 end as b_ytd_adt
from
(
select
s.true_std_delv_days,
sum(case when s.period_nbr=@Prompt('Fiscal Month', 'N', , mono, free) and s.failure_flg=1 then s.shpmt_qty else 0 end) as ab_failures,
sum(case when s.period_nbr=@Prompt('Fiscal Month', 'N', , mono, free) and s.base_flg=1 then s.shpmt_qty else 0 end) as ab_base,
sum(case when s.failure_flg=1 then s.shpmt_qty else 0 end) as ab_ytd_failures,
sum(case when s.base_flg=1 then s.shpmt_qty else 0 end) as ab_ytd_base
from fxgnd_il_ots_sdl_dly_summ s
where period_nbr
