Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,866 Comments

Discussions

Active Period query

548617
548617 Member Posts: 48
edited Nov 26, 2010 7:41AM in SQL & PL/SQL
Hi All,

I need a little help in figuring out this logic. There is table called status with 3 columns id,status,status_date and here is the sample data

100 A 01-Jan-2010
100 A 02-Jan-2010
100 I 03-Jan-2010
100 I 01-Feb-2010
100 A 01-Apr-2010
100 A 02-Apr-2010
100 A 03-Apr-2010
100 I 01-May-2010
100 A 01-jun-2100
100 I 01-Sep-2010

An active period is defined as the period between first status of A and first status of I.

so the output for active periods of A should be
01-Jan-2010 to 03-Jan-2010
01-Apr-2010 to 01-May-2010
01-Jun-2010 to 01-Sep-2010

The logic I wrote using lead analytical function is failing because i am looking at current status of 'A' and next status of 'I' and the output I get is
02-Jan-2010 to 03-Jan-2010
03-Apr-2010 to 01-May-2010
01-Jun-2010 to 01-Sep-2010

Any help is figuring out the logic is greatly appreciated.

Thanks and regards
Hari

Answers

  • Nicosa-Oracle
    Nicosa-Oracle Member Posts: 1,319 Employee
    Hi,

    It looks like the kind of problem that Aketi Jyuuzou would solve using Uekisan.
    I guess he won't be long to answer this one (^_^).
  • BSM
    BSM Member Posts: 207
    edited Nov 22, 2010 9:04AM
    Hi,

    I can see that instead of taking the first value of the status "A", it is taking the recent value of "A" before the status "I" according to the output you got,

    02-Jan-2010 to 03-Jan-2010
    03-Apr-2010 to 01-May-2010
    01-Jun-2010 to 01-Sep-2010

    See below,

    100 A 01-Jan-2010
    100 A 02-Jan-2010 >>>>>>>>>>>>>>>Takes this instead of above.
    100 I 03-Jan-2010 >>>>>>>>>>>>>>> This comes correctly.
    100 I 01-Feb-2010
    100 A 01-Apr-2010
    100 A 02-Apr-2010
    100 A 03-Apr-2010 >>>>>>>>>>>>>>>Takes this instead of above.
    100 I 01-May-2010 >>>>>>>>>>>>>>> This comes correctly.
    100 A 01-jun-2010 >>>>>>>>>>>>>>>This came correctly because this is the recent value before the status "I" value.
    100 I 01-Sep-2010 >>>>>>>>>>>>>>> This comes correctly.

    Can you post us the logic you have used?

    Guys correct me if im wrong.
  • Nicosa-Oracle
    Nicosa-Oracle Member Posts: 1,319 Employee
    edited Nov 22, 2010 9:01AM
    Nicosa wrote:
    It looks like the kind of problem that Aketi Jyuuzou would solve using Uekisan.
    I guess he won't be long to answer this one (^_^).
    Well, maybe not, as the "model" seems to allow for gaps...
  • 189821
    189821 Member Posts: 656
    Hi, I changed 1-JUN-2100 to 1-JUN 2010.
    Here we go:
    SQL>WITH t AS (
      2          SELECT 'A' AS status, TO_DATE('01-Jan-2010', 'DD-Mon-YYYY') AS dt FROM DUAL UNION ALL
      3          SELECT 'A', TO_DATE('02-Jan-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      4          SELECT 'I', TO_DATE('03-Jan-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      5          SELECT 'I', TO_DATE('01-Feb-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      6          SELECT 'A', TO_DATE('01-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      7          SELECT 'A', TO_DATE('02-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      8          SELECT 'A', TO_DATE('03-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      9          SELECT 'I', TO_DATE('01-May-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
     10          SELECT 'A', TO_DATE('01-jun-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
     11          SELECT 'I', TO_DATE('01-Sep-2010', 'DD-Mon-YYYY') FROM DUAL)
     12  SELECT   grp, MIN(DECODE(status, 'A', dt)) AS first_a, MIN(DECODE(status, 'I', dt)) AS first_i
     13      FROM (SELECT status, dt, stst, SUM(stst) OVER(ORDER BY dt) AS grp
     14              FROM (SELECT   status, dt,
     15                             CASE
     16                                WHEN status = 'A' AND NVL(LAG(status) OVER(ORDER BY dt), 'I') != 'A' THEN 1
     17                                ELSE 0
     18                             END AS stst
     19                        FROM t
     20                    ORDER BY dt))
     21  GROUP BY grp;
    
           GRP FIRST_A  FIRST_I
    ---------- -------- --------
             1 01.01.10 03.01.10
             2 01.04.10 01.05.10
             3 01.06.10 01.09.10
    Urs
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,031 Red Diamond
    edited Nov 22, 2010 9:20AM
    Hi, Hari,

    Here's one way:
    WITH	got_analytics	AS
    (
    	SELECT	id, status, status_date
    	,	CASE
    		    WHEN  STATUS = 'A'
    		    AND	  LAG ( status
    			      , 1
    			      , 'I'
    			      ) OVER ( PARTITION BY  id
    				       ORDER BY	     status_date
    				     ) = 'I'
    		    THEN  'Yes'
    		END	AS first_a
    	,	LAST_VALUE ( CASE
    				 WHEN  status = 'I'
    				 THEN  status_date
    			     END
    			     IGNORE NULLS
    			   ) OVER ( PARTITION BY  id
    				    ORDER BY	  status_date	DESC
    				  )	AS next_i_date
    	FROM	status
    	WHERE	status	IN ('A', 'I')	-- If needed
    )
    SELECT	  id
    ,	  status_date
    ,	  next_i_date
    FROM	  got_analytics
    WHERE	  first_a	= 'Yes'
    ORDER BY  id
    ,	  status_date
    ;
    If you'd like to post CREATE TABLE and INSERT statements for the sample data, then I could test this.
    I'm guessing at what role (if any) id plays in this problem.
    I assume you meant all the dates to be in the year 2010, none in 2100.
    Frank Kulash
  • 548617
    548617 Member Posts: 48
    Thank you very much... the solution works like a charm...

    regards
    Hari
  • Eight Six
    Eight Six Member Posts: 411 Bronze Badge
    may be like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  WITH t AS (
      2              SELECT 'A' AS status, TO_DATE('01-Jan-2010', 'DD-Mon-YYYY') AS dt FROM DUAL UNION ALL
      3              SELECT 'A', TO_DATE('02-Jan-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      4              SELECT 'I', TO_DATE('03-Jan-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      5              SELECT 'I', TO_DATE('01-Feb-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      6              SELECT 'A', TO_DATE('01-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      7              SELECT 'A', TO_DATE('02-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      8              SELECT 'A', TO_DATE('03-Apr-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
      9              SELECT 'I', TO_DATE('01-May-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
     10             SELECT 'A', TO_DATE('01-jun-2010', 'DD-Mon-YYYY') FROM DUAL UNION ALL
     11             SELECT 'I', TO_DATE('01-Sep-2010', 'DD-Mon-YYYY') FROM DUAL)
     12  select min(dt), max(dt)
     13  from
     14  (
     15  select x.*, sum(rno)over(order by dt desc) as grp
     16  from
     17  (
     18     select t.*, case when status='I' then row_number() over(order by dt desc) end as rno
     19     from t
     20  )x
     21  )X
     22  group by grp
     23  having max(dt)>min(dt)
     24* order by 1
    SQL> /
    
    MIN(DT)   MAX(DT)
    --------- ---------
    01-JAN-10 03-JAN-10
    01-APR-10 01-MAY-10
    01-JUN-10 01-SEP-10
    
    SQL> 
    Thanks....
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 26, 2010 7:41AM
    I like ignore nulls of Lead function B-)
    WITH t(status,dt)  AS (
    SELECT 'A',date '2010-01-01' FROM DUAL UNION ALL
    SELECT 'A',date '2010-01-02' FROM DUAL UNION ALL
    SELECT 'I',date '2010-01-03' FROM DUAL UNION ALL
    SELECT 'I',date '2010-02-01' FROM DUAL UNION ALL
    SELECT 'A',date '2010-04-01' FROM DUAL UNION ALL
    SELECT 'A',date '2010-04-02' FROM DUAL UNION ALL
    SELECT 'A',date '2010-04-03' FROM DUAL UNION ALL
    SELECT 'I',date '2010-05-01' FROM DUAL UNION ALL
    SELECT 'A',date '2010-06-01' FROM DUAL UNION ALL
    SELECT 'I',date '2010-09-01' FROM DUAL)
    select dt as FIRST_A,FIRST_I
    from (select status,dt,
          Lag(status) over(order by dt) as LagStatus,
          Lead(decode(status,'I',dt))
          ignore nulls over(order by dt) as FIRST_I
          from t)
    where status = 'A'
      and (LagStatus = 'I'
        or LagStatus is null);
    
    FIRST_A   FIRST_I
    --------  --------
    10-01-01  10-01-03
    10-04-01  10-05-01
    10-06-01  10-09-01
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 26, 2010 7:41AM
    First_Value can ignore nulls.
    But is this case,Lead function is more useful :8}
    select dt as FIRST_A,FIRST_I
    from (select status,dt,
          Lag(status) over(order by dt) as LagStatus,
          First_Value(case when status = 'I'
                           then dt end ignore nulls)
          over(order by dt rows
               between 1 following
                   and unbounded following) as FIRST_I
          from t)
    where status = 'A'
      and (LagStatus = 'I'
        or LagStatus is null);
This discussion has been closed.