Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query help (Oracle 12c, Linux)
SQL Query help [message #687617] |
Sun, 16 April 2023 20:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rajivn786
Messages: 161 Registered: January 2010
|
Senior Member |
|
|
Hi,
Need help in writing a query to find first date and first date after 90 days that have value of less than 15 continuosly.
Input and output are :
Create table a(mm varchar2(100), val number, dt date);
Insert into a values (1, 10, to_date('04/01/2010','mm/dd/yyyy'));
Insert into a values (1, 11, to_date('04/02/2010','mm/dd/yyyy'));
Insert into a values (1, 17, to_date('04/07/2010','mm/dd/yyyy'));
Insert into a values (1, 10, to_date('05/01/2010','mm/dd/yyyy'));
Insert into a values (1, 11, to_date('05/02/2010','mm/dd/yyyy'));
Insert into a values (1, 11, to_date('06/01/2010','mm/dd/yyyy'));
Insert into a values (1, 12, to_date('07/01/2010','mm/dd/yyyy'));
Insert into a values (1, 13, to_date('08/01/2010','mm/dd/yyyy'));
Insert into a values (1, 14, to_date('09/01/2010','mm/dd/yyyy'));
Insert into a values (1, 14, to_date('09/09/2010','mm/dd/yyyy'));
I need to find id's and first occurence of first date and last date that have value less than 15 for atleast 90 days minimun.
O/p would be :
1, 10, 05/01/2010
1, 12, 08/01/2010
|
|
|
Re: SQL Query help [message #687618 is a reply to message #687617] |
Mon, 17 April 2023 00:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> WITH
2 a2 AS
3 ( SELECT mm, val
4 , LEAD (dt) OVER (PARTITION BY mm ORDER BY dt) AS lead_dt
5 FROM a)
6 , a4 AS
7 ( SELECT a.mm AS a_mm, a.val AS a_val, a.dt
8 , a2.mm AS a2_mm, a2.val AS a2_val, a2.lead_dt
9 , ROW_NUMBER () OVER (PARTITION BY a.mm ORDER BY a.dt, a2.lead_dt) rn
10 FROM a, a2
11 WHERE a.mm = a2.mm
12 AND a2.lead_dt >= a.dt + 90
13 AND NOT EXISTS
14 ( SELECT a3.*
15 FROM a a3
16 WHERE a3.mm = a.mm
17 AND a3.dt BETWEEN a.dt AND a2.lead_dt
18 AND a3.val >= 15
19 )
20 )
21 SELECT a4.a_mm AS mm, a4.a_val AS val, dt
22 FROM a4
23 WHERE rn = 1
24 UNION ALL
25 SELECT a4.a2_mm AS mm, a4.a2_val AS val, lead_dt dt
26 FROM a4
27 WHERE rn = 1
28 ORDER BY mm, dt
29 /
MM VAL DT
---------- ---------- ----------
1 10 05/01/2010
1 12 08/01/2010
2 rows selected.
|
|
|
Re: SQL Query help [message #687619 is a reply to message #687618] |
Mon, 17 April 2023 15:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with t1 as (
select a.*,
count(case when a.val >= 15 then 1 end) over(partition by a.mm order by a.dt) grp,
lead(a.dt) over(partition by mm order by a.dt) end_dt
from a
),
t2 as (
select t1.*,
case
when t1.dt = min(t1.dt) over(partition by t1.grp)
and
max(t1.end_dt) over(partition by t1.grp) - t1.dt > 90
then 1
when t1.dt - min(t1.dt) over(partition by t1.grp) < 90
and
t1.end_dt - min(t1.dt) over(partition by t1.grp) >= 90
then 2
end flag
from t1
where t1.val < 15
)
select t2.mm,
t2.val,
case t2.flag
when 1 then t2.dt
else t2.end_dt
end dt
from t2
where t2.flag > 0
order by t2.mm,
t2.dt
/
MM VAL DT
---------- ---------- ----------
1 10 05/01/2010
1 12 08/01/2010
SQL>
SY,.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:19:36 CDT 2024
|