This discussion is archived
13 Replies Latest reply: Apr 1, 2013 3:13 AM by S10390 RSS

Regarding query

sunitha2010 Newbie
Currently Being Moderated
Dear All,

I am trying to fetch the both null values and not null values based on the date condition.It is not giving the correct data count.can you please help me on this. I have to do this operation through SQL query only.

I have below query..
SELECT * FROM view1 where  end_date>=add_months(sysdate, -6);
Here, in the above code end_date having the null values and not null values.

I need to retrieve the data past 6 moths both null and not null data.

I have tried below query...
SELECT * FROM view1 where end_date
=add_months(sysdate, -6) and end_date is not null or end_date is null;
Based on the above query i am not getting the past 6 months data.Please help me on this query.

Many Thanks for your help.

Oracle Version : 11g

Please suggest.
  • 1. Re: Regarding query
    jeneesh Guru
    Currently Being Moderated
    It should be
    SELECT * 
    FROM view1
    where  ( end_date >=add_months(sysdate, -6) 
                or end_date is null)
    --Add extra condition if you have
    ;
  • 2. Re: Regarding query
    S10390 Journeyer
    Currently Being Moderated
    SELECT * FROM view1 where end_date>=add_months(trunc(sysdate), -6);
    Check this...
  • 3. Re: Regarding query
    Nimish Garg Guru
    Currently Being Moderated
    try this
    SELECT * FROM view1
    where nvl(end_date,sysdate) >= add_months(sysdate, -6) 
  • 4. Re: Regarding query
    sunitha2010 Newbie
    Currently Being Moderated
    Hi All,

    Many thanks for your Quick Repsonse..

    If i use the below query i am not getting correct data, it is giving whole data from the view.
    SELECT *
    FROM view1
    where    ( end_date >=add_months(sysdate, -6)
                or end_date is null)
    ;
    I need the data if end_date is having null values also for past 6 months. please help me.

    Many Thanks..
  • 5. Re: Regarding query
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    sunitha2010 wrote:
    Hi All,

    Many thanks for your Quick Repsonse..

    If i use the below query i am not getting correct data, it is giving whole data from the view.
    SELECT *
    FROM view1
    where    ( end_date >=add_months(sysdate, -6)
    or end_date is null)
    ;
    I need the data if end_date is having null values also for past 6 months. please help me.

    Many Thanks..
    this query also give you all records which end_date past 6 month and end_date is having null values
    SELECT *
    FROM view1
    where    ( end_date >=add_months(sysdate, -6)
    or end_date is null)
    ;
    if not, then please show sample data and result which you want to get
  • 6. Re: Regarding query
    sunitha2010 Newbie
    Currently Being Moderated
    Hi,

    Many Thanks for reply...

    I need the data for last 6 months.Let me explain in detail..

    I have view veiw1,need to display last 6 months data.While i am using below query then i am getting whole data from view.

    here,in below query i am validating the query with date column end_date. in the veiw for this end_date column contains null values for last 6 months.Even it contains nulls also i need to retrieve the data in the result set.Is it possible with below query?
    SELECT start_date,end_date
    FROM view1
    where    ( end_date >=add_months(sysdate, -6)
                or end_date is null)
    ;
    If i ran the same above query it is giving below data..

    Query data :
    start_date                        end_date
    --------------                      ------------
    6/3/2012 12:49:46 PM     
    6/3/2012 12:52:14 PM     
    6/21/2012 4:49:22 PM     
    6/21/2012 4:49:23 PM     
    8/13/2012 1:43:56 PM     
    8/17/2012 12:01:02 PM     
    8/17/2012 12:02:39 PM     
    8/17/2012 3:27:47 PM     
    8/17/2012 3:27:48 PM     
    8/21/2012 12:26:30 PM     
    8/21/2012 4:16:53 PM     
    9/24/2012 12:01:39 PM     
    9/24/2012 4:33:25 PM     
    9/24/2012 4:33:26 PM     
    9/24/2012 4:35:05 PM     
    10/24/2012 3:55:43 PM     
    11/28/2012 6:25:17 PM     
    11/28/2012 6:27:57 PM     
    11/28/2012 6:32:17 PM     
    11/28/2012 6:37:45 PM     
    11/28/2012 6:43:14 PM     
    11/28/2012 6:47:32 PM     
    11/28/2012 6:54:06 PM     
    11/28/2012 6:58:21 PM     
    11/28/2012 9:11:09 PM     
    11/28/2012 9:29:45 PM     
    1/10/2013 3:45:43 PM     
    1/10/2013 3:50:53 PM     
    1/10/2013 3:55:53 PM     
    1/10/2013 4:00:10 PM     
    1/10/2013 4:51:12 PM     
    1/15/2013 4:35:30 PM     
    1/15/2013 4:47:58 PM     
    1/15/2013 5:04:32 PM     
    1/15/2013 5:17:45 PM     
    1/17/2013 12:47:11 PM     
    1/18/2013 3:44:11 PM     
    1/18/2013 3:49:26 PM     
    2/4/2013 9:44:17 AM     
    2/27/2013 12:26:23 PM     
    2/27/2013 12:28:05 PM     
    3/10/2013 9:52:30 PM     
    3/10/2013 9:53:47 PM     
    3/10/2013 9:55:03 PM     
    3/18/2013 3:39:27 PM     
    3/19/2013 4:45:55 PM     
    Many thanks for your help.
  • 7. Re: Regarding query
    Vivek L Expert
    Currently Being Moderated
    Sorry, but your requirements are still not clear.
    Define "past 6 months". Is it based on end_date or start_date?
    If a row has start_date as 01-Apr-2012 and end_date as NULL, should this be included in your desired resultset?
  • 8. Re: Regarding query
    jeneesh Guru
    Currently Being Moderated
    sunitha2010 wrote:
    Hi,

    Many Thanks for reply...

    I need the data for last 6 months.Let me explain in detail..
    Last 6 months - Based on which field? Start_date or end_date?

    >
    I have view veiw1,need to display last 6 months data.While i am using below query then i am getting whole data from view.
    Is your view contain only 6 months data?

    >
    here,in below query i am validating the query with date column end_date. in the veiw for this end_date column contains null values for last 6 months.Even it contains nulls also i need to retrieve the data in the result set.Is it possible with below query?
    So you need NULLs, and you are getting NULLS, what is your problem then?
  • 9. Re: Regarding query
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    i think this must help :
     SELECT start_date,end_date
      FROM view1
      where    ( start_date >=add_months(sysdate, -6) or end_date is null);
  • 10. Re: Regarding query
    sunitha2010 Newbie
    Currently Being Moderated
    Hi jeenesh,


    Last 6 months - Based on end_date..

    My view contains past 2 years data...I am getting the results..There is no problem at all..

    Problem is --

    Last 6 months data means-- I need to get the data upto Nov,2012. right?
    am getting data for july,2012 also from this query.
  • 11. Re: Regarding query
    Vivek L Expert
    Currently Being Moderated
    may be
    select      * 
    from view1 
    where  nvl(end_date,start_date) >= add_months(sysdate, -6)
  • 12. Re: Regarding query
    jeneesh Guru
    Currently Being Moderated
    sunitha2010 wrote:
    Hi jeenesh,


    Last 6 months - Based on end_date..

    My view contains past 2 years data...I am getting the results..There is no problem at all..

    Problem is --

    Last 6 months data means-- I need to get the data upto Nov,2012. right?
    In your sample output you gave, there is no data with ENd date in July..
    Can you show us the output with end_date in july?
    am getting data for july,2012 also from this query.
  • 13. Re: Regarding query
    S10390 Journeyer
    Currently Being Moderated
    Whenever you are comparing date values with sysdate, use Trunc(), because sysdate will have the time portion.
    SELECT start_date,end_date FROM view1 where ( trunc(end_date )>=add_months(trunc(sysdate), -6) or end_date is null);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points