1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Previous years Data Query

Discussion in 'SQL PL/SQL' started by soori, Mar 2, 2011.

  1. soori

    soori Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    HI everyone..
    can anyone tell me how to do this query..

    We have 10 years (2001 t0 2010) of data.
    i want to display the April 17th data from Every Year

    Like 17-April-2001, 17-April-2002, 17-April-2003 ................


    Thanks and regards
    Soori
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can be done as follows.

    Code (SQL):

    02:43:37 SQL>  WITH REC AS (SELECT TO_DATE('17APR2011','DDMONYYYY')BASE FROM DUAL)
    02:43:43   2  SELECT ADD_MONTHS(BASE,-12*(LEVEL-1)) FROM REC
    02:43:48   3  CONNECT BY LEVEL <12;

    ADD_MONTH
    ---------
    17-APR-11
    17-APR-10
    17-APR-09
    17-APR-08
    17-APR-07
    17-APR-06
    17-APR-05
    17-APR-04
    17-APR-03
    17-APR-02
    17-APR-01

    11 ROWS selected.

    Elapsed: 00:00:00.00

     
     
  3. soori

    soori Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Raj,
    Thanks for ur quick reply..
    but i want output like this

    Year Data
    2001 Cust_Name:ABC,Cust_no:1234,Cust_Addr:#3469,ABC Street,Delhi,INdia
    2002 Cust_Name:ABC,Cust_no:1234,Cust_Addr:#3469,ABC Street,Delhi,INdia
    2003 Cust_Name:ABC,Cust_no:1234,Cust_Addr:#3469,ABC Street,Delhi,INdia
    2004 Cust_Name:ABC,Cust_no:1234,Cust_Addr:#3469,ABC Street,Delhi,INdia
    .
    .
    .
    .
    .
    2011 Cust_Name:ABC11,Cust_no:1234,Cust_Addr:#3469,ABC Street,Delhi,INdia


    Thanks and Regards
    Soori
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am not getting ? What is this outout ? where is previous years date? same record is repeated ? Please explain .
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This should give you a place to start:

    Code (SQL):
     
    SELECT to_char(YEAR, 'RRRR') YEAR, DATA
    FROM mytable
    WHERE to_char(YEAR, 'Month DD') = 'April 17';