5 Replies Latest reply: Jan 28, 2013 11:28 PM by V prasad RSS

    query..

    V prasad
                           
       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
          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
            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
              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
                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
                  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