, sum(nvl(case when a.d0180 is not null and a.d0170 is not null then
case when to_number(a.d0180)
> to_number(a.d0170) then
(to_number(substr(a.d0180, 1, 2)) * 60 + to_number(substr(a.d0180, 3, 2)))
- (to_number(substr(a.d0170, 1, 2)) * 60 + to_number(substr(a.d0170, 3, 2)))
else
(to_number(substr(a.d0180, 1, 2) + 24) * 60 + to_number(substr(a.d0180, 3, 2)))
- (to_number(substr(a.d0170, 1, 2)) * 60 + to_number(substr(a.d0170, 3, 2)))
end
else 0 end, 0)) total
II
where 1 = (case when (a.typ = '1' and 0 = (
select count(rowid)
from t0060
where d0010 = sD0010
and d0020 = sD0020
and d0030 = a.d0030
and d0050 = a.d0110
and d0060 < last_day(add_months(to_date(a.d0040, 'yyyymm'), -1))))
or (a.typ = '2' and 0 = (select count(rowid) --
from t0040
where d0010 = sD0010
and d0020 = sD0020
and d0030 = a.d0030
and d0110 = a.d0110
and nvl(d0210, 0) > 0
and d0040 < to_char(last_day(to_date(a.d0040, 'yyyymm')),'yyyymmdd')))
then 1 else 0 end)