10 Replies Latest reply on Mar 24, 2009 2:20 AM by 152933

    query help

    152933
      Hi
      I have table with 7 months data ,I want to rollback 3 months data dynamically(If its maximum data is july ,i need july,june,mayata) .Please any help on this

      ex:

      1/01/2008 200
      1/02/2008 200
      1/03/2008 200
      1/04/2008 200
      1/05/2008 200
      1/06/2008 200
      1/07/2008 200

      Expecting

      1/05/2008 200
      1/06/2008 200
      1/07/2008 200

      Thanks
      mr
        • 1. Re: query help
          Kamran Agayev A.
          Can you explain your problem more briefly please?
          Do you have a table and you want to recover it? Or you have database you want to recover it?
          Did you delete it? Or...?

          - - - - - - - - - - - - - - - - - - - - -
          Kamran Agayev A. (10g OCP)
          http://kamranagayev.wordpress.com
          • 2. Re: query help
            152933
            Hi
            I have table with 7 months data ,when loading into my target ,i need pickup 3 months data (If max date is August in my source ,i need to pick August.july,june) dynamically and loaded into target.

            Thanks,
            MR
            • 3. Re: query help
              Kamran Agayev A.
              Do you mean
              SELECT * FROM your_table WHERE ddate between to_date('01062008','ddmmyyyy') and lastday(to_date('092008','mmyyyy'))
              - - - - - - - - - - - - - - - - - - - - -
              Kamran Agayev A. (10g OCP)
              http://kamranagayev.wordpress.com
              • 4. Re: query help
                Christian Balz
                Hello,

                try this,
                WITH TAB AS(SELECT TRUNC(TO_DATE('01/01/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/02/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/03/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/04/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/05/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/06/2008'),'MM') MON FROM DUAL UNION ALL
                            SELECT TRUNC(TO_DATE('01/07/2008'),'MM') MON FROM DUAL)
                SELECT * FROM TAB WHERE MON > (SELECT ADD_MONTHS(MAX(MON),-3) FROM TAB)
                output
                MON
                -----------
                1/5/2008
                1/6/2008
                1/7/2008
                Regards,

                Christian Balz

                Edited by: Christian Balz on 23/03/2009 04:50 - Add output
                • 5. Re: query help
                  Pradeep Dewani
                  Suppose that in ur query table name is EMP and column name is hiredate and sal so you can apply following query for ur requirment


                  SELECT *
                  FROM emp
                  WHERE hiredate > (SELECT hiredate
                  FROM emp
                  WHERE sal = (SELECT MAX (sal)
                  FROM emp))




                  Regards,
                  Pradeep Dewani
                  • 6. Re: query help
                    Naveenkumar
                    INSERT INTO targettable
                    (c,d)
                    SELECT a,b
                    FROM soue_table WHERE to_char( date_m,'MM') in('6','7','8') ;
                    • 7. Re: query help
                      Aketi Jyuuzou
                      We can use add_months :D
                      select ddate
                      from (select ddate,
                            max(add_months(ddate,3)) over() as since
                             from t)
                       where since <= ddate
                      • 8. Re: query help
                        Sven W.
                        Pradeep Dewani wrote:
                        Suppose that in ur query table name is EMP and column name is hiredate and sal so you can apply following query for ur requirment


                        SELECT *
                        FROM emp
                        WHERE hiredate > (SELECT hiredate
                        FROM emp
                        WHERE sal = (SELECT MAX (sal)
                        FROM emp))




                        Regards,
                        Pradeep Dewani
                        Please be careful with selects like that. They tend get become quite slow when working with real tables. Why? Because you select three times from the same (possibly large) table. If some of those table acesses will be a full table scan then the query will take ages. It is almost always possible to get the same result with one single access to the table using analytic functions and only one full table scan. I think some examples where already in this thread.
                        • 9. Re: query help
                          Christian Balz
                          >
                          Aketi Jyuuzou wrote

                          We can use add_months
                          select ddate
                          from (select ddate,
                                max(add_months(ddate,3)) over() as since
                                 from t)
                           where since <= ddate
                          is a good solution, however requires a few adjustments ;)
                          select ddate
                            from ( select ddate
                                        , max(add_months(ddate,-3)) over() as since
                                    from t)
                           where since < ddate
                          Regards,

                          Christian Balz
                          • 10. Re: query help
                            152933
                            Hi
                            Thanks to all for your valiable suggestions

                            Thanks ,
                            MR