This discussion is archived
5 Replies Latest reply: Jan 28, 2013 9:28 PM by V prasad RSS

query..

V prasad Pro
Currently Being Moderated
                     
 WITH  T AS 
(
SELECT 100 AS STR, 'A' STATUS_FLG, 'SA' AS FEE_CODE, 'QRT' YEAR_FLG, 1 YEAR_NO, 
TO_DATE('01-JAN-2012','DD-MON-YYYY') START_DT,  TO_DATE('31-MAR-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'R' STATUS_FLG, 'MG' AS FEE_CODE, 'QRT' YEAR_FLG, 1 YEAR_NO, 
TO_DATE('01-JAN-2012','DD-MON-YYYY') START_DT,  TO_DATE('31-MAR-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'A' STATUS_FLG, 'SA' AS FEE_CODE, 'QRT' YEAR_FLG, 2 YEAR_NO, 
TO_DATE('01-APR-2012','DD-MON-YYYY') START_DT,  TO_DATE('30-JUN-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'R' STATUS_FLG, 'MG' AS FEE_CODE, 'QRT' YEAR_FLG, 2 YEAR_NO, 
TO_DATE('01-APR-2012','DD-MON-YYYY') START_DT,  TO_DATE('30-JUN-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'A' STATUS_FLG, 'SA' AS FEE_CODE, 'QRT' YEAR_FLG, 3 YEAR_NO, 
TO_DATE('01-JUL-2012','DD-MON-YYYY') START_DT,  TO_DATE('30-SEP-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'R' STATUS_FLG, 'MG' AS FEE_CODE, 'QRT' YEAR_FLG, 3 YEAR_NO, 
TO_DATE('01-JUL-2012','DD-MON-YYYY') START_DT,  TO_DATE('30-SEP-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'A' STATUS_FLG, 'SA' AS FEE_CODE, 'QRT' YEAR_FLG, 4 YEAR_NO, 
TO_DATE('01-OCT-2012','DD-MON-YYYY') START_DT,  TO_DATE('31-DEC-2012','DD-MON-YYYY') END_DT FROM DUAL
UNION
SELECT 100 AS STR, 'R' STATUS_FLG, 'MG' AS FEE_CODE, 'QRT' YEAR_FLG, 4 YEAR_NO, 
TO_DATE('01-OCT-2012','DD-MON-YYYY') START_DT,  TO_DATE('31-DEC-2012','DD-MON-YYYY') END_DT FROM DUAL
)
SELECT * FROM T    ORDER BY      START_DT


Required data to sort

if i am running first quarter.

STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
-----------------------------------------------------------------------------------------------------------
100      A          SA       QRT      1                 01-JAN-12            31-MAR-12 

if i am running second quarter.

STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
-----------------------------------------------------------------------------------------------------------
100    A          SA       QRT      1                      01-JAN-12                 31-MAR-12 
100    A          SA       QRT      2                      01-APR-12                 30-JUN-12                 
100    R          MG       QRT      2                      01-APR-12                 30-JUN-12 

if i am running second quarter.
STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
-----------------------------------------------------------------------------------------------------------
100    A          SA       QRT      1                      01-JAN-12                 31-MAR-12 
100    A          SA       QRT      2                      01-APR-12                 30-JUN-12                 
100    R          MG       QRT      2                      01-APR-12                 30-JUN-12 
100    R          MG       QRT      3                      01-JUL-12                 30-SEP-12                 
100    A          SA       QRT      3                      01-JUL-12                 30-SEP-12
                  
  • 1. Re: query..
    damorgan Oracle ACE Director
    Currently Being Moderated
    And the point of this posting is ... ?

    Seems to me this belongs in your blog not in Oracle's forum.
  • 2. Re: query..
    Karthick_Arp Guru
    Currently Being Moderated
    I appreciate that you have taken the time to build sample data, at the same time it would be of much help if you can give some text description of your expected output.

    And always you can read {message:id=9360002} to know how to post questions in this forum.
  • 3. Re: query..
    V prasad Pro
    Currently Being Moderated
    if my rundate is fall on first quarter.
    example:  01-feb-2012.
    
    Required output is:
     
    select * from t where TO_DATE('01-feb-2012','DD-MON-YYYY') >= START_DT;  and (only STATUS = 'a')
    
    STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
    -----------------------------------------------------------------------------------------------------------
    100      A          SA       QRT      1                 01-JAN-12            31-MAR-12 
     
    
    
    
    if my rundate is fall on secondquarter.
    example:  01-apr-2012.
    
    Required output is:
     
    select * from t where TO_DATE('01-apr-2012','DD-MON-YYYY') >= START_DT; 
    1 record from first quarter with status = 'r' 
    2 record from second quarter status = 'a' and 'r'
     
    STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
    -----------------------------------------------------------------------------------------------------------
    100    R          SA       QRT      1                      01-JAN-12                 31-MAR-12 
    100    A          SA       QRT      2                      01-APR-12                 30-JUN-12                 
    100    R          MG       QRT      2                      01-APR-12                 30-JUN-12 
     
    
    if my rundate is fall on secondquarter.
    example:  01-aug-2012.
    
    Required output is:
     
    select * from t where TO_DATE('01-aug-2012','DD-MON-YYYY') >= START_DT; 
    1 record from first quarter with status = 'r' 
    2 record fromsecond quarter with satus '= 'r'
    3 record from thired quarter status = 'a' and 'r'
    
    STR    STATUS_FLG FEE_CODE YEAR_FLG YEAR_NO             START_DT              END_DT     
    -----------------------------------------------------------------------------------------------------------
    100    A          SA       QRT      1                      01-JAN-12                 31-MAR-12 
    100    A          SA       QRT      2                      01-APR-12                 30-JUN-12                 
    100    R          MG       QRT      2                      01-APR-12                 30-JUN-12 
    100    R          MG       QRT      3                      01-JUL-12                 30-SEP-12                 
    100    A          SA       QRT      3                      01-JUL-12                 30-SEP-12
  • 4. Re: query..
    Karthick_Arp Guru
    Currently Being Moderated
    Like this
    SQL> select * from t order by start_dt
      2  /
     
           STR S FE YEA    YEAR_NO START_DT  END_DT
    ---------- - -- --- ---------- --------- ---------
           100 A SA QRT          1 01-JAN-12 31-MAR-12
           100 R MG QRT          1 01-JAN-12 31-MAR-12
           100 A SA QRT          2 01-APR-12 30-JUN-12
           100 R MG QRT          2 01-APR-12 30-JUN-12
           100 R MG QRT          3 01-JUL-12 30-SEP-12
           100 A SA QRT          3 01-JUL-12 30-SEP-12
           100 R MG QRT          4 01-OCT-12 31-DEC-12
           100 A SA QRT          4 01-OCT-12 31-DEC-12
     
    8 rows selected.
     
    SQL> var dt varchar2(12)
    
    SQL> exec :dt := '01-feb-2012'
     
    PL/SQL procedure successfully completed.
    
    SQL> select *
      2    from (
      3            select t.*, case when to_date(:dt, 'dd-mon-yyyy') between start_dt and end_dt then 1
      4                             when to_date(:dt, 'dd-mon-yyyy') > end_dt then 1
      5                             else 0 end dt_flag
      6              from t
      7         )
      8   where dt_flag = 1
      9     and case when year_no = 1 then 'A' else status_flg end = status_flg
     10   order
     11     by start_dt
     12  /
     
           STR S FE YEA    YEAR_NO START_DT  END_DT       DT_FLAG
    ---------- - -- --- ---------- --------- --------- ----------
           100 A SA QRT          1 01-JAN-12 31-MAR-12          1
    
    SQL> exec :dt := '01-apr-2012'            
     
    PL/SQL procedure successfully completed.
     
    SQL> /
     
           STR S FE YEA    YEAR_NO START_DT  END_DT       DT_FLAG
    ---------- - -- --- ---------- --------- --------- ----------
           100 A SA QRT          1 01-JAN-12 31-MAR-12          1
           100 R MG QRT          2 01-APR-12 30-JUN-12          1
           100 A SA QRT          2 01-APR-12 30-JUN-12          1
     
    SQL> exec :dt := '01-aug-2012'  
     
    PL/SQL procedure successfully completed.
     
    SQL> /
     
           STR S FE YEA    YEAR_NO START_DT  END_DT       DT_FLAG
    ---------- - -- --- ---------- --------- --------- ----------
           100 A SA QRT          1 01-JAN-12 31-MAR-12          1
           100 R MG QRT          2 01-APR-12 30-JUN-12          1
           100 A SA QRT          2 01-APR-12 30-JUN-12          1
           100 R MG QRT          3 01-JUL-12 30-SEP-12          1
           100 A SA QRT          3 01-JUL-12 30-SEP-12          1
     
    SQL> 
  • 5. Re: query..
    V prasad Pro
    Currently Being Moderated
    Hi
    
    
    SQL> select * from t order by start_dt
      2  / 
     
           STR S FE YEA    YEAR_NO START_DT  END_DT
    ---------- - -- --- ---------- --------- ---------
           100 A SA QRT          2 01-APR-12 30-JUN-12
           100 R MG QRT          2 01-APR-12 30-JUN-12
           100 R MG QRT          3 01-JUL-12 30-SEP-12
           100 A SA QRT          3 01-JUL-12 30-SEP-12
           100 R MG QRT          4 01-OCT-12 31-DEC-12
           100 A SA QRT          4 01-OCT-12 31-DEC-12
     
    
    If i donot have first quarter means start date is 01-APR-12 .
    then if my run date is second quarter i want ouput as
    
    SQL> exec :dt := '01-apr-2012'            
     
    PL/SQL procedure successfully completed.
     
    SQL> / 
     
           STR S FE YEA    YEAR_NO START_DT  END_DT       DT_FLAG
    ---------- - -- --- ---------- --------- --------- ----------
           100 A SA QRT          2 01-APR-12 30-JUN-12          1

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points