OraFAQ Forum: SQL & PL/SQL » Query to split date count into multiple rows and level that over weeks .

Home » SQL & PL/SQL » SQL & PL/SQL » Query to split date count into multiple rows and level that over weeks .

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Query to split date count into multiple rows and level that over weeks . [message #687807 is a reply to message #687806]

Wed, 14 June 2023 22:11

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

This appears to be the same problem as the following thread:

https://forums.oracle.com/ords/apexds/post/sql-query-to-project-the-future-volume-based-on-historical-2534

Did any of the solutions provided solve your problem?

Report message to a moderator

Re: Query to split date count into multiple rows and level that over weeks . [message #687810 is a reply to message #687807]

Thu, 15 June 2023 09:45

itzkashi
Messages: 2
Registered: June 2023

Junior Member

Yes I got the solution here !
https://forums.oracle.com/ords/apexds/post/sql-query-to-project-the-future-volume-based-on-historical-2534

Thanks for replying
itzkashi

Report message to a moderator

Previous Topic:	What is the difference between MINUS and LEFT outer join
Next Topic:	Letter and TIMESTAMP CONCAT (INSERT error)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 10:44:27 CDT 2024