5 Replies Latest reply: Dec 31, 2012 5:37 AM by Solomon Yakobson RSS

    complex query help

    852469
      CREATE TABLE QUARTER
      (YR NUMBER,MM_NAME VARCHAR2(6),DESCRIPT VARCHAR2(20),
      PRIMARY KEY (YR,MM_NAME));



      INSERT INTO QUARTER VALUES(2012,'dec','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2012,'nov','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2012,'oct','LAST QUARTER');

      INSERT INTO QUARTER VALUES(2011,'dec','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2011,'nov','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2011,'oct','LAST QUARTER');

      INSERT INTO QUARTER VALUES(2010,'dec','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2010,'nov','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2010,'oct','LAST QUARTER');

      INSERT INTO QUARTER VALUES(2009,'dec','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2009,'nov','LAST QUARTER');
      INSERT INTO QUARTER VALUES(2009,'oct','LAST QUARTER');

      from this table we can get information like year and last month loaded:

      create table last_check(YR NUMBER,MM_NAME VARCHAR2(6)PRIMARY KEY (YR,MM_NAME));
      INSERT INTO QUARTER VALUES(2012,'nov');

      We have to consider 1 year span data loaded (dynamically)


      output should be like :


      2012,dec,LAST QUARTER,null
      2012,nov,LAST QUARTER,CY
      2012,oct,LAST QUARTER,CY

      2011,dec,LAST QUARTER,CY
      2011,nov,LAST QUARTER,LY
      2011,oct,LAST QUARTER,LY

      2010,dec,LAST QUARTER,LY
      2010,nov,LAST QUARTER,C2Y
      2010,oct,LAST QUARTER,C2Y

      2009,dec,LAST QUARTER,C2Y
      2009,nov,LAST QUARTER,null
      2009,oct,LAST QUARTER,null
        • 1. Re: complex query help
          user639304
          Hi,

          You don't expalin the logic used to obtain your output. Especially, what is the criteria leading to null, or CY or C2Y or LY in the last column of your output?
          • 2. Re: complex query help
            852469
            as per my example from last_check table we can get completion of the last data load.

            from dec2011 to nov2012 1 year so CY will appear
            dec2010 to nov2011 1 year so LY will appear

            same way for the remaining parts
            • 3. Re: complex query help
              jeneesh
              Not clear the logic..How are you getting the output?

              based on Year wise and availability of data?
              with t as
              (
                select q.yr,q.mm_name,q.descript,
                       months_between(
                            to_date(l.yr||l.mm_name,'yyyymon'),
                            to_date(q.yr||q.mm_name,'yyyymon')
                                     ) mths
                from quarter q,last_check l
              )
              select yr,mm_name,descript,
                     case when mths between 0 and 11 then 'CY'
                          when mths between 12 and 23 then 'LY'
                          when mths between 24 and 35 then 'C2Y'
                          else null
                     end output
              from t;
              YR MM_NAME DESCRIPT             OUTPUT
              -- ------- -------------------- ------
              2012 dec     LAST QUARTER                
              2012 nov     LAST QUARTER         CY     
              2012 oct     LAST QUARTER         CY     
              2011 dec     LAST QUARTER         CY     
              2011 nov     LAST QUARTER         LY     
              2011 oct     LAST QUARTER         LY     
              2010 dec     LAST QUARTER         LY     
              2010 nov     LAST QUARTER         C2Y    
              2010 oct     LAST QUARTER         C2Y    
              2009 dec     LAST QUARTER         C2Y    
              2009 nov     LAST QUARTER                
              2009 oct     LAST QUARTER                
              
               12 rows selected 
              • 4. Re: complex query help
                Solomon Yakobson
                select  q.yr,
                        q.mm_name,
                        q.descript,
                        case l.yr
                          when q.yr then case
                                           when to_date(l.mm_name,'mm') < to_date(q.mm_name,'mm') then null
                                           else 'CY'
                                         end
                          when q.yr + 1 then case
                                               when to_date(l.mm_name,'mm') < to_date(q.mm_name,'mm') then 'CY'
                                               else 'LY'
                                              end
                          when q.yr + 2 then case
                                               when to_date(l.mm_name,'mm') < to_date(q.mm_name,'mm') then 'LY'
                                               else 'C2Y'
                                              end
                          when q.yr + 3 then case
                                               when to_date(l.mm_name,'mm') < to_date(q.mm_name,'mm') then 'C2Y'
                                              end
                        end x
                  from  quarter q,
                        last_check l
                /
                
                        YR MM_NAM DESCRIPT             X
                ---------- ------ -------------------- ---
                      2012 dec    LAST QUARTER
                      2012 nov    LAST QUARTER         CY
                      2012 oct    LAST QUARTER         CY
                      2011 dec    LAST QUARTER         CY
                      2011 nov    LAST QUARTER         LY
                      2011 oct    LAST QUARTER         LY
                      2010 dec    LAST QUARTER         LY
                      2010 nov    LAST QUARTER         C2Y
                      2010 oct    LAST QUARTER         C2Y
                      2009 dec    LAST QUARTER         C2Y
                      2009 nov    LAST QUARTER
                
                        YR MM_NAM DESCRIPT             X
                ---------- ------ -------------------- ---
                      2009 oct    LAST QUARTER
                
                12 rows selected.
                
                SQL> 
                SY.
                • 5. Re: complex query help
                  Frank Kulash
                  Hi,
                  849466 wrote:
                  CREATE TABLE QUARTER
                  (YR NUMBER,MM_NAME VARCHAR2(6),DESCRIPT VARCHAR2(20),
                  PRIMARY KEY (YR,MM_NAME));
                  Information about dates should be stored in DATE columns, not NUMBER and VARCHAR2 columns. You're only making things more complicated (and more error prone, and slower) by storing the date in 2 separate columns, neither of which is a DATE.
                  Somtimes it may be handy to have separate year and month columns, like the ones above, but these columns should be in addition to, not instead of, a DATE column. Starting in Oracle 11, you could make yr and mm_name virtual columns, automatically derived from a DATE column.
                  ... from this table we can get information like year and last month loaded:

                  create table last_check(YR NUMBER,MM_NAME VARCHAR2(6)PRIMARY KEY (YR,MM_NAME));
                  INSERT INTO QUARTER VALUES(2012,'nov');
                  Did you mean "INSERT INTO *LAST_CHECK* ..."?
                  We have to consider 1 year span data loaded (dynamically)


                  output should be like :


                  2012,dec,LAST QUARTER,null
                  2012,nov,LAST QUARTER,CY
                  2012,oct,LAST QUARTER,CY

                  2011,dec,LAST QUARTER,CY
                  2011,nov,LAST QUARTER,LY
                  2011,oct,LAST QUARTER,LY

                  2010,dec,LAST QUARTER,LY
                  2010,nov,LAST QUARTER,C2Y
                  2010,oct,LAST QUARTER,C2Y

                  2009,dec,LAST QUARTER,C2Y
                  2009,nov,LAST QUARTER,null
                  2009,oct,LAST QUARTER,null
                  That looks like you need to condsider 3 different 1 year spans.
                  Here's one way to do that:
                  WITH   got_q_dt         AS
                  (
                       SELECT     yr, mm_name, descript
                       ,     TO_DATE ( yr || mm_name
                                 , 'YYYYmon'
                                 )     AS q_dt
                       FROM    quarter
                  )
                  ,     got_l_dt     AS
                  (
                       SELECT     TO_DATE ( yr || mm_name
                                 , 'YYYYmm'
                                 )     AS l_dt
                       FROM    last_check
                  )
                  SELECT     qd.yr, qd.mm_name, qd.descript
                  ,     CASE  
                           WHEN  qd.q_dt > ld.l_dt
                           THEN  NULL
                           WHEN  MONTHS_BETWEEN (ld.l_dt, qd.q_dt) < 12
                           THEN  'CY'
                           WHEN  MONTHS_BETWEEN (ld.l_dt, qd.q_dt) < 24
                           THEN  'LY'
                           WHEN  MONTHS_BETWEEN (ld.l_dt, qd.q_dt) < 36
                           THEN  'C2Y'
                       END          AS label
                  FROM          got_q_dt  qd
                  CROSS JOIN     got_l_dt  ld
                  ORDER BY  qd.q_dt       DESC
                  ;
                  If you stored the dates in DATE column, then you wouldn't need the sub-queries above.