CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Laks
Laks

Reputation: 85

SQL query to find dates where more records were active

prod StartDate   ENDdate
----------------------------
a    1789-04-01  1799-12-14
b    1797-03-04  1826-07-04
c    1801-03-04  1826-07-04
d    1809-03-04  1836-06-28
e    1817-03-04  1831-07-04

I am trying to find a solution for above scenario but not able to get the logic. could you please help me on this.

the question is to find the start-date and end-date where most number of product were active.

For example : in above sample data, between 1817-03-04 to 1826-07-04 there are almost 4 products(b,c,d,e) were active (3409 days ) and 1 product(a) was active between 1789-04-01 to 1799-12-14.

The results should be like 

prod_count StartDate       ENDdate
-----------------------------------
4          1817-03-04  1826-07-04
1          1789-04-01  1799-12-14

Upvotes: 1

Views: 77

Answers (2)

stefan
stefan

Reputation: 2252

Test table and data:

create table startend ( prod, startdate, enddate )
as
select 'a', date'1789-04-01', date'1799-12-14' from dual union all
select 'b', date'1797-03-04', date'1826-07-04' from dual union all
select 'c', date'1801-03-04', date'1826-07-04' from dual union all
select 'd', date'1809-03-04', date'1836-06-28' from dual union all
select 'e', date'1817-03-04', date'1831-07-04' from dual ; 

SQL> select * from startend;
PROD  STARTDATE  ENDDATE    
a     01-APR-89  14-DEC-99  
b     04-MAR-97  04-JUL-26  
c     04-MAR-01  04-JUL-26  
d     04-MAR-09  28-JUN-36  
e     04-MAR-17  04-JUL-31

Let's assume that we need to find/examine every possible combination of STARTDATE and ENDDATE. We could use a JOIN like the one in the inline view below. In this query, the rownum values have been renamed to: ERA (and will be used for GROUP BY at a later stage).

  select 
    to_char( startdate, 'YYYY-MM-DD') start_
  , to_char( enddate, 'YYYY-MM-DD')   end_
  , enddate - startdate as duration
  , rownum as era
  from ( 
    select distinct
      S1.startdate
    , S2.enddate
    from startend S1 
      join startend S2 on S1.startdate < S2.enddate
  ) 
;

-- result
START_     END_         DURATION        ERA
---------- ---------- ---------- ----------
1789-04-01 1836-06-28      17254          1
1789-04-01 1826-07-04      13607          2
1801-03-04 1831-07-04      11079          3
1809-03-04 1836-06-28       9978          4
1817-03-04 1836-06-28       7056          5
1817-03-04 1831-07-04       5235          6
1801-03-04 1826-07-04       9253          7
1809-03-04 1826-07-04       6331          8
1789-04-01 1831-07-04      15433          9
1797-03-04 1799-12-14       1015         10
1797-03-04 1826-07-04      10713         11
1797-03-04 1831-07-04      12539         12
1817-03-04 1826-07-04       3409         13
1789-04-01 1799-12-14       3909         14
1797-03-04 1836-06-28      14360         15
1801-03-04 1836-06-28      12900         16
1809-03-04 1831-07-04       8157         17

17 rows selected.

The conditions you need seem to be as follows (see the WHERE clause):

-- test dates: from your question
select prod
from startend
where startdate <= date'1817-03-04' and startdate < date'1826-07-04'
  and enddate   > date'1817-03-04' and enddate   >= date'1826-07-04'
;

-- result
b
c
d
e

Final step: combine the ideas behind the first 2 queries, something like (Oracle 11g):

select count(*)                        as "prod_count"
, to_char( E.startdate, 'YYYY-MM-DD' ) as "StartDate"
, to_char( E.enddate, 'YYYY-MM-DD' )   as "EndDate"
from 
(
    select startdate, enddate, rownum as era
    from 
    (
      select distinct
        S1.startdate
      , S2.enddate
      from startend S1 join startend S2 on S1.startdate < S2.enddate
    )
) E 
join 
(
    select distinct prod, startdate, enddate from startend
) P  
on    
      ( P.startdate <= E.startdate and P.startdate < E.enddate )
  and ( P.enddate   >  E.startdate and P.enddate   >= E.enddate )
--
group by era, E.startdate, E.enddate
order by 2, 3
;

Result

prod_count StartDate  EndDate   
---------- ---------- ----------
         1 1789-04-01 1799-12-14
         2 1797-03-04 1799-12-14
         1 1797-03-04 1826-07-04
         2 1801-03-04 1826-07-04
         3 1809-03-04 1826-07-04
         1 1809-03-04 1831-07-04
         1 1809-03-04 1836-06-28
         4 1817-03-04 1826-07-04
         2 1817-03-04 1831-07-04
         1 1817-03-04 1836-06-28

10 rows selected.

See also: dbfiddle here. When working with Oracle 12c (or 18c), you could use CROSS APPLY (instead of JOIN ... ON ...)

Upvotes: 2

VitezslavSimon
VitezslavSimon

Reputation: 332

1)

SELECT COUNT(*) AS prod_count, StartDate, ENDdate
FROM XXX
GROUP BY StartDate, ENDdate;

-- It works with equal StartDate and ENDdate values.

2) Another idea can be:

SELECT COUNT(*) AS prod_count, StartDate, ENDdate
(
    SELECT StartDate, ENDdate, (StartDate - ENDdate) AS TimePeriod 
    FROM XXX
) AS X2
GROUP BY TimePeriod;

-- It works with equal time period.

Sorry I don't have Oracle instance access so its from hand without previous test so there can be some inaccuracy.

Upvotes: 0

Related Questions