3 Replies Latest reply: Dec 28, 2012 8:28 AM by 901346 RSS

    sql query help

    901346
      I have a log table-

      from that we can find last data loaded into table(IS_LAST_12_MONTHS='Yes' order by ID desc[1539740])
      1)last loaded into table is nov 2012.

      2)if IS_LAST_12_MONTHS=YES can track the last 12mm data has been loaded.(here nov-2012 to dec-2011)

      *3)i want to track <previous last 12mm from the current last 12mm(nov-2012 to dec-2011)>*
      *[it should nov-2011 dec-2010) so if in the query is it possible to change IS_LAST_12_MONTHS=y*
      *then we can track it from the year respect.*
      or you may add other column

      CREATE TABLE TEMP_TIME
      (
        YEAR               VARCHAR2(80 CHAR)          NOT NULL,
        QUARTER            VARCHAR2(93 CHAR),
        ID            NUMBER(8)                  NOT NULL,
        MONTH              VARCHAR2(30 CHAR),
        IS_LAST_12_MONTHS  VARCHAR2(3 CHAR)
      )
      ;

      SET DEFINE OFF;
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q4', 1539741, '2012.M12 (Incomplete)', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q4', 1539740, '2012.M11', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q4', 1539739, '2012.M10', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q3', 1539738, '2012.M09', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q3', 1539737, '2012.M08', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q3', 1539736, '2012.M07', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q2', 1539735, '2012.M06', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q2', 1539734, '2012.M05', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q2', 1539733, '2012.M04', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q1', 1539732, '2012.M03', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q1', 1539731, '2012.M02', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2012', '2012.Q1', 1539730, '2012.M01', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q4', 1539728, '2011.M12', 'Yes');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q4', 1539727, '2011.M11', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q4', 1539726, '2011.M10', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q3', 1539725, '2011.M09', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q3', 1539724, '2011.M08', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q3', 1539723, '2011.M07', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q2', 1539722, '2011.M06', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q2', 1539721, '2011.M05', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q2', 1539720, '2011.M04', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q1', 1539719, '2011.M03', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q1', 1539718, '2011.M02', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2011', '2011.Q1', 1539717, '2011.M01', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2010', '2010.Q4', 1539715, '2010.M12', 'No');
      Insert into TEMP_TIME
         (YEAR, QUARTER, ID, MONTH, IS_LAST_12_MONTHS)
      Values
         ('2010', '2010.Q4', 1539714, '2010.M11', 'No');
      COMMIT;

      Edited by: 898343 on Dec 28, 2012 5:55 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
        • 1. Re: sql query help
          John Spencer
          If I understand the requirement correctly, here is one way.
          SQL> with min_dt as (
            2     select min(to_date(substr(month, 1, 8), 'yyyy."M"mm')) month_as_dt
            3     from temp_time
            4     where is_last_12_months = 'Yes')
            5  select year, quarter, id, month
            6  from (select year, quarter, id, month,
            7               to_date(substr(month, 1, 8), 'yyyy."M"mm') month_as_dt,
            8               is_last_12_months
            9        from temp_time) a
           10     cross join min_dt b
           11  where a.month_as_dt between add_months(b.month_as_dt, - 12) and
           12                              add_months(b.month_as_dt, - 1)
           13  order by 4;
          
          YEAR       QUARTER                 ID MONTH
          ---------- --------------- ---------- -------------------------
          2010       2010.Q4            1539715 2010.M12
          2011       2011.Q1            1539717 2011.M01
          2011       2011.Q1            1539718 2011.M02
          2011       2011.Q1            1539719 2011.M03
          2011       2011.Q2            1539720 2011.M04
          2011       2011.Q2            1539721 2011.M05
          2011       2011.Q2            1539722 2011.M06
          2011       2011.Q3            1539723 2011.M07
          2011       2011.Q3            1539724 2011.M08
          2011       2011.Q3            1539725 2011.M09
          2011       2011.Q4            1539726 2011.M10
          2011       2011.Q4            1539727 2011.M11
          John
          • 2. Re: sql query help
            Frank Kulash
            Hi,
            >
            Thanks for posting the sample data. Don't forget to post the results you want from that data.
            CREATE TABLE TEMP_TIME
            (
            YEAR VARCHAR2(80 CHAR) NOT NULL,
            QUARTER VARCHAR2(93 CHAR),
            ID NUMBER(8) NOT NULL,
            MONTH VARCHAR2(30 CHAR),
            IS_LAST_12_MONTHS VARCHAR2(3 CHAR)
            )
            ;
            Storing date information in anything except a DATE (or TIMESTAMP) column is a really bad idea. You should change your table to use a DATE column. If you want to have redundant columns like the current year, quarter and month, then you can. Starting in Oracle 11, these can be virtual columns, derived automatically from the DATE column.

            Given that you really want strings, why are they so long? Why isn't year 4 characters long, and quarter and month 7 characters each?

            It's unclear what you want. I think you're saying that, if the first row (in order by month) that has is_last_12_months='Yes' is '2011.M12', then you want to return the rows from the previous 12 months , that is '2010.M12' through '2011.M11'.
            If that's what you want, here's one way to do it:
            WITH     got_earliest_yes     AS
            (
                 SELECT     t.*
                 ,     MIN (month)     AS earliest_string
                 ,     TO_DATE ( MIN (month)
                           , 'YYYY."M"MM'
                           )     AS earliest_date
                 FROM    temp_time  t
                 WHERE     is_last_12_months     = 'Yes'
            )
            SELECT       t.*
            FROM          temp_tim       t
            CROSS JOIN     got_earliest_yes  e
            WHERE       t.month >= TO_CHAR ( ADD_MONTHS (e.earliest_date, -12)
                                     , 'YYYY."M"MM'
                                )
            AND       t.month <  earliest_string
            ;
            Edited by: Frank Kulash on Dec 28, 2012 9:30 AM

            I just saw John's solution.
            One of the many reasons why you should never store dates in anything except DATE columns is that you'll get bad data. John's solution will raise a run-time error if any row has an invalid date. It also has to call TO_DATE fior every row, which takes time. The solution I posted avoids that. It will only raise an error if the earliest 'Yes' row is invalid, and it only calls TO_DATE once, even if you have millions of rows in the table.

            If you want to include the rows where is_last_12_months='Yes', then
            WITH     got_earliest_yes     AS
            (
                 SELECT     MIN (month)     AS earliest_string
                 ,     MAX (month)     AS latest_string
                 ,     TO_DATE ( MIN (month)
                           , 'YYYY."M"MM'
                           )     AS earliest_date
                 FROM    temp_time
                 WHERE     is_last_12_months     = 'Yes'
            )
            SELECT       t.*
            FROM          temp_time       t
            CROSS JOIN     got_earliest_yes  e
            WHERE       t.month >= TO_CHAR ( ADD_MONTHS (e.earliest_date, -12)
                                     , 'YYYY."M"MM'
                                )
            AND       t.month <= latest_string
            ORDER BY  t.id       DESC
            ;
            • 3. Re: sql query help
              901346
              thanks for the reply.i will definitely mark it correct.
              i want those records(as per your query as well as last 12 mm data).(24 rows should come)