Home » SQL & PL/SQL » SQL & PL/SQL » Query to split date count into multiple rows and level that over weeks .
Query to split date count into multiple rows and level that over weeks . [message #687806] |
Wed, 14 June 2023 06:38 |
|
itzkashi
Messages: 2 Registered: June 2023
|
Junior Member |
|
|
Hi ,
I have written a query that requires two pieces of logic to be implemented.
first logic is to get the crtd_dt having higher counts greater than 10 which in this case is ‘04-MAR-23’ and then split the date into multiple buckets or rows which in this case i am splitting into two buckets or rows only by using the logic as (select level from dual connect by level <= (select approx_cnt/6 from data_set).
second logic is once we get the crtd_dt having maximum counts I need to apply the logic to substract 7 days from first row of crtd_dt which comes as ‘25-FEB-23’ then substract 14 days from second row of crtd_dt which comes as ‘18-FEB-23’ . both 25 and 18 dates are already populated in a table ,
I am able to achieve the first logic but unable to proceed further to achieve the second logic. Please advise
Quote:SAMPLE DATA
CREATE TABLE T_DAT ( "CRT_DT" DATE );
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR')); commit;
query to be modified to get the expected output below
with data_set as
( select d.crt_dt as crt_dt,
trunc(d.crt_dt) + 180 as future_dt,
count(*) approx_cnt
from t_dat d
where d.crt_dt<=sysdate and
d.crt_dt >= (trunc(sysdate) - 180)
group by d.crt_dt
having trunc(d.crt_dt) + 180 >= trunc(sysdate)-9 and count(*)>10),
vol as (select level from dual connect by level <= (select approx_cnt/6 from data_set))
select crt_dt, future_dt , approx_cnt
from data_set d ,vol;
**OUTPUT**
CRT_DT FUTURE_DT APPROX_CNT
04-MAR-23 31-AUG-23 15
04-MAR-23 31-AUG-23 15
EXPECTED OUTPUT
CRT_DT FUTURE_DT APPROX_CNT
25-FEB-23 24-AUG-23 7
18-FEB-23 17-AUG-23 10
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:21:31 CDT 2024
|