1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
with t1 (id, date1, fld2, fld3) as (
select 1, to_date('10.01.2020', 'DD.MM.YYYY'), 10, '11' from dual
union all select 2, to_date('11.01.2020', 'DD.MM.YYYY'), 15, '22' from dual
union all select 3, to_date('12.01.2020', 'DD.MM.YYYY'), 30, '43' from dual
--
union all select 4, to_date('01.05.2020', 'DD.MM.YYYY'), 10, '17' from dual
union all select 5, to_date('21.05.2020', 'DD.MM.YYYY'), 15, '27' from dual
union all select 6, to_date('30.05.2020', 'DD.MM.YYYY'), 30, '07' from dual
--
union all select 7, to_date('13.08.2020', 'DD.MM.YYYY'), 10, '14' from dual
union all select 8, to_date('17.08.2020', 'DD.MM.YYYY'), 15, '24' from dual
union all select 9, to_date('29.08.2020', 'DD.MM.YYYY'), 30, '02' from dual
--
union all select 10, to_date('03.09.2020', 'DD.MM.YYYY'), 10, '14' from dual
union all select 11, to_date('27.09.2020', 'DD.MM.YYYY'), 15, '24' from dual
union all select 12, to_date('29.09.2020', 'DD.MM.YYYY'), 30, '04' from dual
--
union all select 10, to_date('07.10.2020', 'DD.MM.YYYY'), 10, '12' from dual
union all select 11, to_date('27.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
union all select 12, to_date('31.10.2020', 'DD.MM.YYYY'), 30, '42' from dual
--
union all select 10, to_date('01.11.2020', 'DD.MM.YYYY'), 10, '15' from dual
union all select 11, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '25' from dual
union all select 12, to_date('20.11.2020', 'DD.MM.YYYY'), 30, '05' from dual
)
,wdate (dat,wd) as(
select to_date('31.10.2019', 'DD.MM.YYYY'), 0 from dual
union all select to_date('31.10.2020', 'DD.MM.YYYY'), 0 from dual
union all select to_date('13.08.2020', 'DD.MM.YYYY'), 0 from dual
union all select to_date('29.09.2020', 'DD.MM.YYYY'), 0 from dual
union all select to_date('01.11.2020', 'DD.MM.YYYY'), 0 from dual
union all select to_date('01.07.2020', 'DD.MM.YYYY'), 0 from dual
)
,w as ( -- +календар выходных
select
t1.*
,nvl(wd,1) wd
,decode(nvl(wd,1),1,date1) date_work
from t1 t1,wdate w where date1=dat(+)
-- order by 2
)
, l as ( --последняя строка в месяце /*не выходной*/
select
trunc(date1,'mm') date1
,max(fld3) KEEP (DENSE_RANK last ORDER BY date1) fld3
-- ,max(wd) KEEP (DENSE_RANK last ORDER BY date1) wd
from w
group by trunc(date1,'mm')
--having max(wd) KEEP (DENSE_RANK last ORDER BY date1)=1
)
select w.*
,case
when lead(cast(null as number),1,wd) over (partition by trunc(date1,'mm') order by date1)=1 --последний день тек месяца рабочий
then (select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<trunc(w.date1,'mm'))
when lag(null,1,wd) over (partition by trunc(date_work,'mm') order by date1)=1 --первый рабочий день тек месяца
and last_value(wd) over (order by date1 range between --последний день "предыдущего" месяца
UNBOUNDED PRECEDING AND date1-trunc(date1,'mm') PRECEDING)=0
then (select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<add_months(trunc(w.date1,'mm'),-1))
end lv
from w order by 2
SQL> /
ID DATE1 FLD2 FL WD DATE_WORK LV
---------- ---------- ---------- -- ---------- ---------- --
1 10.01.2020 10 11 1 10.01.2020
2 11.01.2020 15 22 1 11.01.2020
3 12.01.2020 30 43 1 12.01.2020
4 01.05.2020 10 17 1 01.05.2020
5 21.05.2020 15 27 1 21.05.2020
6 30.05.2020 30 07 1 30.05.2020 43
7 13.08.2020 10 14 0
8 17.08.2020 15 24 1 17.08.2020
9 29.08.2020 30 02 1 29.08.2020 07
10 03.09.2020 10 14 1 03.09.2020
11 27.09.2020 15 24 1 27.09.2020
12 29.09.2020 30 04 0
10 07.10.2020 10 12 1 07.10.2020 02
11 27.10.2020 15 22 1 27.10.2020
12 31.10.2020 30 42 0
10 01.11.2020 10 15 0
11 02.11.2020 15 25 1 02.11.2020 04
12 20.11.2020 30 05 1 20.11.2020 42
