|
23.01.2022, 00:08
#40128746
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Ukraine,Lviv
Сообщения: 4 370
Рейтинг:
0
/ 0
|
|
|
|
joskii,
набрал
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.
with doc (id_doc,id_client,dt_doc) as (
select 1,10,date '2022-01-05' from dual union all
select 2,10,date '2022-01-15' from dual union all
select 3,20,date '2022-01-20' from dual union all
select 8,20,date '2021-01-15' from dual
)
,first (id_client,open_dt,code ) as(
select 10,date '2022-01-01',111 from dual union all
select 10,date '2022-01-02',112 from dual union all
select 10,date '2022-01-03',113 from dual union all
select 10,date '2022-01-13',114 from dual union all
select 10,date '2022-01-31',115 from dual union all
--
select 20,date '2022-01-13',211 from dual union all
select 20,date '2022-01-24',212 from dual union all
select 20,date '2022-01-27',213 from dual
)
,second (id_client,open_dt,code ) as(
select 10,date '2022-01-07',2111 from dual union all
select 10,date '2022-01-08',2112 from dual union all
select 10,date '2022-01-31',2115 from dual
)
,third (id_client,open_dt,code ) as(
select 10,date '2022-01-11',3111 from dual union all
select 10,date '2022-01-12',3112 from dual union all
select 10,date '2022-01-13',3113 from dual union all
select 10,date '2022-01-14',3114 from dual union all
select 10,date '2022-01-15',3115 from dual union all
--
select 20,date '2022-01-13',3211 from dual union all
select 20,date '2022-01-14',3212 from dual union all
select 20,date '2022-01-15',3213 from dual
)
,d1 as (
select * from (
select d.id_doc,d.id_client,d.dt_doc,open_dt o1,code c1
,lead(open_dt) over (partition by d.id_doc,d.id_client order by open_dt) l
from doc d,first f
where d.id_client=f.id_client(+) and d.dt_doc >= f.open_dt(+)
) where l is null
)
,d2 as (
select * from (
select d.id_doc,d.id_client,d.dt_doc,o1,c1,open_dt o2,code c2
,lead(open_dt) over (partition by d.id_doc,d.id_client order by open_dt) l
from d1 d,second f
where d.id_client=f.id_client(+) and d.dt_doc >= f.open_dt(+)
) where l is null
)
,d3 as (
select * from (
select d.id_doc,d.id_client,d.dt_doc,o1,c1,02,c2,open_dt o3,code c3
,lead(open_dt) over (partition by d.id_doc,d.id_client order by open_dt) l
from d2 d,third f
where d.id_client=f.id_client(+) and d.dt_doc >= f.open_dt(+)
) where l is null
)
select * from d3
order by 1,2
......
stax |
|
|
