drop table id_jobs;
create table id_jobs
(
id int ,
job int ,
dat_job date
);
alter table id_jobs add constraint PK_id_hist primary key (id, job);
insert into id_jobs(id, job, dat_job) values (1, 10, '01/07/2021');
insert into id_jobs(id, job, dat_job) values (2, 20, '30/04/2020');
insert into id_jobs(id, job, dat_job) values (3, 30, '01/08/2020');
insert into id_jobs(id, job, dat_job) values (4, 40, '01/08/2020');
-- ==================================
drop table job_val;
create table job_val
(
job int ,
val int not null ,
dat_val date
);
alter table job_val add constraint PK_job_val primary key (job, dat_val);
insert into job_val(job, val, dat_val) values (10, 100, '01/06/2020');
insert into job_val(job, val, dat_val) values (10, 200, '28/07/2021');
insert into job_val(job, val, dat_val) values (20, 100, '01/08/2021');
insert into job_val(job, val, dat_val) values (30, 100, '01/07/2020');
Here is the problem. We need to get the job value of each id according to dat_job.
For instance, id 1 get job 10 on 01/07/2021. At that time, the value of job 10 was 100.
On 28/07/2021 it has changed to 200. So before 01/07/2021 we have 100 and after that we have 200.
Same thing for id 2. He get the job on 30/04/2020. At that time, the were no value for job 20.
But now the value is 100.
for id 3. He get the job on 01/08/2020. At that time, the value was 100 and it has not changed.
for id 4. He get the job on 01/08/2020. And the job was not valued till now.
The question is how to get this information in one query ?
Here is my start (And I am sure, Gurus will optimize it) :
select
id
, id_jobs.job
, dat_job
--, old_val.val old_val
, new_val.val new_val
from id_jobs
--, job_val old_val
, job_val new_val
where 1 = 1
-- and id_jobs.job = old_val.job(+)
and id_jobs.job = new_val.job(+)
and (new_val.dat_val in (select max(dat_val) from job_val where job = id_jobs.job)
or new_val.dat_val is null)
order by 1
;
So I want to uncomment the comments to get this result :
John Watson Messages: 8805 Registered: January 2010 Location: Global Village
Senior Member
This is not solution that covers all possibilities by any means, and it is certainly not very efficient, but it might give you a start on one approach:
orclz> select id_jobs.*,
2 (select val from job_val where job=id_jobs.job and dat_val < id_jobs.dat_job) old_val,
3 (select val from job_val where job=id_jobs.job and dat_val > id_jobs.dat_job) new_val
4 from id_jobs;
ID JOB DAT_JOB OLD_VAL NEW_VAL
--------------- --------------- ------------------- --------------- ---------------
1 10 2021-07-01:00:00:00 100 200
2 20 2020-04-30:00:00:00 100
3 30 2020-08-01:00:00:00 100
4 40 2020-08-01:00:00:00
orclz>
--update: a (slightly) more robust solution:
select id_jobs.*,
(select max(val) from job_val where job=id_jobs.job and dat_val < id_jobs.dat_job) old_val,
(select min(val) from job_val where job=id_jobs.job and dat_val > id_jobs.dat_job) new_val
from id_jobs
/
Michel Cadot Messages: 68421 Registered: March 2007 Location: Nanterre, France, http://...
Senior Member Account Moderator
If we add the following rows, what should be the result?
insert into id_jobs(id, job, dat_job) values (1, 20, to_date('15/07/2021', 'dd/mm/yyyy'));
insert into job_val(job, val, dat_val) values (20, 200, to_date('01/09/2021', 'dd/mm/yyyy'));
Or, in words, what is the semantics of OLD_VAL and NEW_VAL?
OK, for OLD_VAL it is clear: it is the value of the job when "id" joins it.
But for NEW_VAL, it is less clear.
What's the problem John ?
What's the problem when being demanding with myself, looking for the best, the most readable and the most beautiful SQL query ?
I did thank your proposition isn't it ? So why is your answer disproportional ?
And I did thank all the community of Gurus that helped me, through this post.
When I started coming here, I've understood that mastering SQL in the key point.
And This video demonstrates it again.
I hope you will come to your senses John.
Back to business.
Responding to Michel Cadot :
About he semantics of OLD_VAL and NAW_VAL :
OLD_VAL is the last value of the job when id joins it.
and dat_val in (select max(dat_val) from job_val where job = id_jobs.job
and dat_val <= dat_job)
NEW_VAL is the actual value of the job (doesn't depend on when id get the job).
and dat_val in (select max(dat_val) from job_val where job = id_jobs.job)
So according to the rows added, the result should be like this :
id 1 joined job 20 on 15/07/2021. At that time, this job was not valued (actually it started to be
valued on 01/08/2021). So the OLD_VAL is null.
Now the NEW_VAL is the latest value of the job. So it is 200.
Michel Cadot Messages: 68421 Registered: March 2007 Location: Nanterre, France, http://...
Senior Member Account Moderator
Just for fun I customized it as:
SQL> col old_val format a21
SQL> col new_val format a21
SQL> with
2 jobval as (
3 select job, val, dat_val,
4 row_number() over (partition by job order by dat_val desc) rn
5 from job_val
6 ),
7 idjobs as (
8 select i.id, i.job, i.dat_job, v.val, v.dat_val,
9 row_number() over (partition by i.id, i.job order by v.dat_val desc) rn
10 from id_jobs i left outer join jobval v on v.job = i.job and v.dat_val <= i.dat_job
11 )
12 select i.id, i.job, i.dat_job,
13 i.val||decode(i.val, null,'', ' since '||to_char(i.dat_val)) old_val,
14 c.val||decode(c.val, null,'', ' since '||to_char(c.dat_val)) new_val
15 from idjobs i left outer join jobval c on c.job = i.job and c.rn = 1
16 where i.rn = 1
17 order by i.id, i.job
18 /
ID JOB DAT_JOB OLD_VAL NEW_VAL
---------- ---------- ----------- --------------------- ---------------------
1 10 01-JUL-2021 100 since 01-JUN-2020 200 since 28-JUL-2021
1 20 15-JUL-2021 200 since 01-SEP-2021
2 20 30-APR-2020 200 since 01-SEP-2021
3 30 01-AUG-2020 100 since 01-JUL-2020 100 since 01-JUL-2020
4 40 01-AUG-2020
Solomon Yakobson Messages: 3213 Registered: January 2010 Location: Connecticut, USA
Senior Member
select i.*,
(
select max(val) keep(dense_rank last order by v.dat_val)
from job_val v
where v.job = i.job
and v.dat_val <= i.dat_job
) old_val,
(
select max(val)
from job_val v
where v.job = i.job
) new_val
from id_jobs i
order by i.id
/
ID JOB DAT_JOB OLD_VAL NEW_VAL
------------- ------------- ---------- ------------- -------------
1 10 01/07/2021 100 200
2 20 30/04/2020 100
3 30 01/08/2020 100 100
4 40 01/08/2020
SQL>
Michel Cadot Messages: 68421 Registered: March 2007 Location: Nanterre, France, http://...
Senior Member Account Moderator
I think you misunderstand the expression, "max" here is not "max of all val" but, in case there are several "val" for "dat_val"="last order by v.dat_val" then take "max(val)". If there can't be several "val" for this last date then max(val)=val, of course.
insert into job_val values (10,50, to_date('01/08/2021', 'dd/mm/yyyy'));
SY solution gives us this result :
SQL> select i.*
2 ,
3 (
4 select max(val) keep(dense_rank last order by v.dat_val)
5 from job_val v
6 where v.job = i.job
7 and v.dat_val <= i.dat_job
8 ) old_val,
9 (
10 select max(val)
11 from job_val v
12 where v.job = i.job
13 ) new_val
14 from id_jobs i
15 order by i.id;
ID JOB DAT_JOB OLD_VAL NEW_VAL
--------- --------- ---------- --------- ---------
1 10 01/07/2021 100 200
2 20 30/04/2020 100
3 30 01/08/2020 100 100
4 40 01/08/2020
SQL>
And Michel Cadot solution gives this result :
SQL> select *
2 from
3 (
4 with
5 jobval as (
6 select job, val, dat_val,
7 row_number() over (partition by job order by dat_val desc) rn
8 from job_val
9 ),
10 idjobs as (
11 select i.id, i.job, v.val,
12 row_number() over (partition by i.id, i.job order by v.dat_val desc) rn
13 from id_jobs i left outer join jobval v on v.job = i.job and v.dat_val <= i.dat_job
14 )
15 select i.id, i.job, i.val old_val, c.val new_val
16 from idjobs i left outer join jobval c on c.job = i.job and c.rn = 1
17 where i.rn = 1
18 order by i.id, i.job
19 )
20 /
ID JOB OLD_VAL NEW_VAL
--------- --------- --------- ---------
1 10 100 50
2 20 100
3 30 100 100
4 40
SQL>
Solomon Yakobson Messages: 3213 Registered: January 2010 Location: Connecticut, USA
Senior Member
Ah, cut & paste error - second subquery is missing keep clause:
select i.*,
(
select max(val) keep(dense_rank last order by v.dat_val)
from job_val v
where v.job = i.job
and v.dat_val <= i.dat_job
) old_val,
(
select max(val) keep(dense_rank last order by v.dat_val)
from job_val v
where v.job = i.job
) new_val
from id_jobs i
order by i.id
/
ID JOB DAT_JOB OLD_VAL NEW_VAL
---------- ---------- --------- ---------- ----------
1 10 01-JUL-21 100 50
2 20 30-APR-20 100
3 30 01-AUG-20 100 100
4 40 01-AUG-20
SQL>
Michel Cadot Messages: 68421 Registered: March 2007 Location: Nanterre, France, http://...
Senior Member Account Moderator
You can combine Solomon's and my queries to:
SQL> with
2 jobval as (
3 select job,
4 max(val) keep (dense_rank last order by dat_val) val
5 from job_val
6 group by job
7 ),
8 idjobs as (
9 select i.id, i.job,
10 max(v.val) keep (dense_rank last order by v.dat_val) val
11 from id_jobs i left outer join job_val v on v.job = i.job and v.dat_val <= i.dat_job
12 group by i.id, i.job
13 )
14 select i.id, i.job, i.val old_val, c.val new_val
15 from idjobs i left outer join jobval c on c.job = i.job
16 order by i.id, i.job
17 /
ID JOB OLD_VAL NEW_VAL
---------- ---------- ---------- ----------
1 10 100 200
1 20 200
2 20 200
3 30 100 100
4 40
5 rows selected.
The fastest one will depend on your needs and data.
Solomon's subqueries in SELECT clause will be executed for each row, mine only once.
If you want or have a small number of ids or jobs and have good indexes, Solomon's query will be faster.
If you want a report for overall data, mine will be faster.
] 
Search
Help
Members
Register
Login
Home
