Home » SQL & PL/SQL » SQL & PL/SQL » Update values "in groups" (Oracle 12c, 12.1.0.2.0, MS Windows 7)
Update values "in groups" [message #663582] |
Fri, 09 June 2017 03:13 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Hello!
I need assistance. Here's what I have:
create table test
( rid number,
sta varchar2(3),
date_from date,
date_to date
);
insert into test (rid, sta, date_from, date_to)
select 3468, 'RVT', date '2015-08-19', date '2015-10-01' from dual union
select 3468, 'SMN', null , date '2015-10-01' from dual union
select 3468, 'RVT', date '2015-10-01', date '2016-01-01' from dual union
select 3468, 'SMN', null, date '2016-01-01' from dual union
select 3468, 'RVT', date '2016-01-01', date '2016-02-19' from dual union
select 3468, 'SMN', null, date '2016-02-19' from dual union
--
select 5931, 'RVT', date '2016-09-01', date '2017-01-01' from dual union
select 5931, 'SMN', null, date '2017-01-01' from dual union
select 5931, 'RVT', date '2017-01-01', date '2017-01-10' from dual union
select 5931, 'RVT', date '2017-01-10', date '2017-02-23' from dual union
select 5931, 'SMN', null, date '2017-02-23' from dual;
SQL> select * from test
2 order by rid, date_to, sta;
RID STA DATE_FROM DATE_TO
---------- --- ---------- ----------
3468 RVT 19.08.2015 01.10.2015
3468 SMN 01.10.2015
3468 RVT 01.10.2015 01.01.2016
3468 SMN 01.01.2016
3468 RVT 01.01.2016 19.02.2016
3468 SMN 19.02.2016
5931 RVT 01.09.2016 01.01.2017
5931 SMN 01.01.2017
5931 RVT 01.01.2017 10.01.2017
5931 RVT 10.01.2017 23.02.2017
5931 SMN 23.02.2017
I have to fill DATE_FROM values for STA = SMN (they are all NULL).
Rows are grouped by RID, and - within that RID - by "pairs" of (one or more) RVT + one SMN. SMN's DATE_FROM should be set to the MIN(DATE_FROM) that belongs to its group's RVT. Unfortulately, there's no "group" indicator in the table (that says which RVTs and SMNs go together); these groups are identified by "ORDER BY" clause (order by rid, date_to, sta).
For example:
RID STA DATE_FROM DATE_TO
---------- --- ---------- ----------
3468 RVT 19.08.2015 01.10.2015 --> MIN(DATE_FROM) for RVT in this group is 19.08.2015
3468 SMN 01.10.2015 --> DATE_FROM should be 19.08.2015
or
RID STA DATE_FROM DATE_TO
---------- --- ---------- ----------
5931 RVT 01.01.2017 10.01.2017 --> MIN(DATE_FROM) of all RVT's in this group is 01.01.2017
5931 RVT 10.01.2017 23.02.2017
5931 SMN 23.02.2017 --> DATE_FROM should be 01.01.2017
Queries I managed to write don't work; it is the second group (in the above example) that makes problems. Here's one of my attempts; close, but not enough:
SQL> update test sr
2 set sr.date_from =
3 (select sr1.date_from
4 from test sr1
5 where sr1.rid = sr.rid
6 and sr1.sta = 'RVT'
7 and sr1.date_from <> sr1.date_to
8 and sr1.date_to = sr.date_to)
9 where sr.sta = 'SMN' and sr.date_from is null;
5 rows updated.
SQL> select * from test
2 order by rid, date_to, sta;
RID STA DATE_FROM DATE_TO
---------- --- ---------- ----------
3468 RVT 19.08.2015 01.10.2015
3468 SMN 19.08.2015 01.10.2015
3468 RVT 01.10.2015 01.01.2016
3468 SMN 01.10.2015 01.01.2016
3468 RVT 01.01.2016 19.02.2016
3468 SMN 01.01.2016 19.02.2016
5931 RVT 01.09.2016 01.01.2017
5931 SMN 01.09.2016 01.01.2017
5931 RVT 01.01.2017 10.01.2017
5931 RVT 10.01.2017 23.02.2017
5931 SMN 10.01.2017 23.02.2017 --> this is wrong; DATE_FROM should be 01.01.2017, not 10.01.2017
PL/SQL option I wrote (I can post it, if necessary) works OK, but it is dead slow as there are millions of rows I have to update so I'd much rather use SQL instead. Unfortunately, I'm incapable of writing it. I *feel* that the solution is here, somewhere, slightly out of reach, but I just can't see it.
Could someone assist, please?
|
|
|
Re: Update values "in groups" [message #663588 is a reply to message #663582] |
Fri, 09 June 2017 06:50 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Assuming date_to is same within group:
SELECT RID,
STA,
NVL(
DATE_FROM,
MIN(DATE_FROM) OVER(PARTITION BY RID,DATE_TO ORDER BY STA)
) DATE_FROM,
DATE_TO
FROM TEST T
/
RID STA DATE_FROM DATE_TO
---------- --- --------- ---------
3468 RVT 19-AUG-15 01-OCT-15
3468 SMN 19-AUG-15 01-OCT-15
3468 RVT 01-OCT-15 01-JAN-16
3468 SMN 01-OCT-15 01-JAN-16
3468 RVT 01-JAN-16 19-FEB-16
3468 SMN 01-JAN-16 19-FEB-16
5931 RVT 01-SEP-16 01-JAN-17
5931 SMN 01-SEP-16 01-JAN-17
5931 RVT 01-JAN-17 10-JAN-17
5931 RVT 10-JAN-17 23-FEB-17
5931 SMN 10-JAN-17 23-FEB-17
11 rows selected.
SQL>
SY.
|
|
|
|
Re: Update values "in groups" [message #663590 is a reply to message #663588] |
Fri, 09 June 2017 07:08 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ah, my assumption was wrong:
WITH T AS (
SELECT TEST.*,
CASE LAG(STA,1,'SMN') OVER(PARTITION BY RID ORDER BY DATE_TO,STA)
WHEN 'SMN' THEN DATE_FROM
END FIRST_RVT_DATE_FROM
FROM TEST
)
SELECT RID,
STA,
NVL(
DATE_FROM,
MAX(FIRST_RVT_DATE_FROM) OVER(PARTITION BY RID ORDER BY DATE_TO,STA)
) DATE_FROM,
DATE_TO
FROM T
/
RID STA DATE_FROM DATE_TO
---------- --- --------- ---------
3468 RVT 19-AUG-15 01-OCT-15
3468 SMN 19-AUG-15 01-OCT-15
3468 RVT 01-OCT-15 01-JAN-16
3468 SMN 01-OCT-15 01-JAN-16
3468 RVT 01-JAN-16 19-FEB-16
3468 SMN 01-JAN-16 19-FEB-16
5931 RVT 01-SEP-16 01-JAN-17
5931 SMN 01-SEP-16 01-JAN-17
5931 RVT 01-JAN-17 10-JAN-17
5931 RVT 10-JAN-17 23-FEB-17
5931 SMN 01-JAN-17 23-FEB-17
11 rows selected.
SQL>
SY.
|
|
|
|
Re: Update values "in groups" [message #663592 is a reply to message #663591] |
Fri, 09 June 2017 07:33 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Littlefoot wrote on Fri, 09 June 2017 08:18
(Looking at your query, it appears that it was a little bit further than just slightly out of reach.)
Not really. It is, if you think about it, just a simplified variation of same start-of-group method. You can find several posts on using this method.
SY.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 13:50:37 CDT 2024
|