Code:
select asidn,
asbz1,
asbz2,
coalesce(stlj,0) as stlj,
coalesce(stvj,0) as stvj,
coalesce(stvvj,0) as stvvj,
coalesce(frei,0) as stfrei,
coalesce(stlj / asumf2,0) as qmlj,
coalesce(stvj / asumf2,0) as qmvj,
coalesce(stvvj / asumf2,0) as qmvvj,
coalesce(frei / asumf2,0) as qmfrei,
coalesce((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34, 0) as stpromon,
coalesce((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34 / asumf2, 0) as qmpromon,
coalesce(((frei / stlj) / 10), 0) as rwlj,
coalesce(coalesce(frei,0) / ((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0))) / 34,0) as rw,
coalesce((((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0)),0) as stprod9,
coalesce((((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0)) / asumf2,0) as qmprod9,
asabc,
asme2
from
stdprod.afassp
left join
(
select
s3idn,
sum(s3tlmg) as stlj
from
stdprod.afs3sp
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z') and
s3rgd <= '20201031'
group by
s3idn
) ulj
on
ulj.s3idn = asidn
left join
(
select
s3idn,
sum(s3tlmg) as stvj
from
wwdta.afs3spvj
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z')
group by
s3idn
) uvj
on
uvj.s3idn = asidn
left join
(
select
s3idn,
sum(s3tlmg) as stvvj
from
wwdta.afs3spvvj
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z')
group by
s3idn
) uvvj
on
uvvj.s3idn = asidn
left join
(
select
abidn,
sum(abbst) as frei
from
stdprod.afabsp
where
substr(ablag, 3, 2) < '50'
group by
abidn
) bst
on
abidn = asidn
where
substr(asidn, 11, 1) = '1' and
substr(asidn, 13, 2) < '40' and
(
coalesce(stlj,0) <> 0 or
coalesce(stvj,0) <> 0 or
coalesce(stvvj,0) <> 0 or
coalesce(frei,0) <> 0
)
order by
((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0) desc
Bookmarks