Need the number of times the date is incremented in Oracle SQL
create table zzenp_so_multi_rs
(
so_number varchar2(30),
rev_number number,
line_number number,
rs_date date,
status varchar2(30)
)
;
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 1, 1, '15-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 2, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 3, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 4, 1, '14-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 5, 1, '14-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 6, 1, '17-MAY-2022', 'Open');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 1, 1, '17-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 2, 1, '15-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 3, 1, '12-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 4, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 5, 1, '13-MAY-2022', 'Open');
Here, we have Sales Order Number, Revision Number, Line Number, RS Date & Status. Each time a line is modified, another record is inserted and the status is modified to Reference for old record & Open for new record.
I need the calculation function / subquery for no_of_push_outs. This is only for no of time the date is pushed out (Date incremented) not pulled in.
SELECT SO_NUMBER, REV_NUMBER, LINE_NUMBER, RS_DATE, <> no_of_push_outs
FROM zzenp_so_multi_rs
WHERE status = 'Open'
CodePudding user response：
This is how I understood it.
The temp CTE calculates previous rs_date, and it is then used to sum push-outs (as you can't have analytic function within the sum aggregation).
SQL> WITH
2 temp
3 AS
4 (SELECT so_number,
5 rev_number,
6 line_number,
7 rs_date,
8 status,
9 LAG (rs_date) OVER (PARTITION BY so_number ORDER BY rev_number) prev_rs_date
10 FROM zzenp_so_multi_rs)
11 SELECT z.so_number,
12 z.line_number,
13 (SELECT MAX (b.rev_number)
14 FROM zzenp_so_multi_rs b
15 WHERE b.so_number = z.so_number
16 AND b.status = 'Open') rev_number,
17 (SELECT MAX (b.rs_date)
18 FROM zzenp_so_multi_rs b
19 WHERE b.so_number = z.so_number
20 AND b.status = 'Open') rs_date,
21 SUM (CASE WHEN z.rs_date > z.prev_rs_date THEN 1 ELSE 0 END) l_sum
22 FROM temp z
23 GROUP BY z.so_number, z.line_number
24 ORDER BY z.so_number;
SO_NUMBER LINE_NUMBER REV_NUMBER RS_DATE L_SUM
------------------------------ ----------- ---------- ---------- ----------
10000 1 6 17.05.2022 2
10001 1 5 13.05.2022 1
SQL>
CodePudding user response：
For Oracle version 12.1 and later, you can use match_recognize for a clean, elegant solution.
select so_number, rev_number, line_number, rs_date, no_of_push_outs
from zzenp_so_multi_rs
match_recognize (
partition by so_number, line_number
order by rev_number
measures last(rev_number) as rev_number,
count(up.*) as no_of_push_outs,
last(rs_date) as rs_date
pattern ( (up|other)* )
define up as rs_date > prev(rs_date)
);
CodePudding user response：
Also using LAG() to get number of date increases, but hopefully simpler join
with pout as (
select so_number, count(*) no_of_push_outs from (
select z.so_number, rs_date,
lag(rs_date) over (partition by so_number order by rev_number) p_rs_date
from zzenp_so_multi_rs z
)
where rs_date > p_rs_date
group by so_number
)
select z.*, pout.no_of_push_outs
from zzenp_so_multi_rs z, pout
where z.so_number=pout.so_number
and z.status='Open';
CodePudding user response：
You can do it with LAG and the COUNT analytic functions (without any self-joins):
SELECT *
FROM (
SELECT so_number,
rev_number,
line_number,
rs_date,
status,
COUNT(is_push_out) OVER (PARTITION BY so_number) AS no_of_push_outs
FROM (
SELECT z.*,
CASE
WHEN LAG(rs_date) OVER (PARTITION BY so_number ORDER BY rev_number)
< rs_date
THEN 1
END AS is_push_out
FROM zzenp_so_multi_rs z
)
)
WHERE status = 'Open';
Or, you can use the LAG analytic function and then use GROUP BY and KEEP (DENSE_RANK LAST ORDER BY rev_number) to get the data for the last revision in an aggregation function:
SELECT so_number,
MAX(rev_number) AS rev_number,
MAX(line_number) KEEP (DENSE_RANK LAST ORDER BY rev_number)
AS line_number,
MAX(rs_date) KEEP (DENSE_RANK LAST ORDER BY rev_number)
AS rs_date,
MAX(status) KEEP (DENSE_RANK LAST ORDER BY rev_number)
AS status,
COUNT(is_push_out) AS no_of_push_outs
FROM (
SELECT z.*,
CASE
WHEN LAG(rs_date) OVER (PARTITION BY so_number ORDER BY rev_number)
< rs_date
THEN 1
END AS is_push_out
FROM zzenp_so_multi_rs z
)
GROUP BY so_number;
Which, for the sample data, both output:
SO_NUMBER REV_NUMBER LINE_NUMBER RS_DATE STATUS NO_OF_PUSH_OUTS 10000 6 1 17-MAY-22 Open 2 10001 5 1 13-MAY-22 Open 1
db<>fiddle here