Link to home
Start Free TrialLog in
Avatar of ernie_shah
ernie_shahFlag for Trinidad and Tobago

asked on

RESTRICT DATA

Good Afternoon

I would like to know how do I restrict data that falls outside a specific data range

for example
If a date starts from OCT 2015, NOV 2015, DEC 2015 etc. then return all data on or before this range because there is no data before OCT 2015
If a date starts from SEP 2015, OCT 2015, NOV 2015, DEC 2015 then return no data at all because that is data before OCT 2015

I am  using SQL in Toad.  Hope that this is clear.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please provide sample data and expected results.
Avatar of ernie_shah

ASKER

I am trying to retrieve data that starts from OCT 2015 using a parameter (:end_date (31-Oct-2015) where the parameter returns all data from 31-Oct-2015:

DATE                 BBLS         WATER_CUT       WELL_NAME
31-Oct-2015        14                 .25                       WW1
30-Nov-2015        6                   .3                         WW1
31-Dec-2015         7                   .4                        WW1

As such, in the database the first record starts at 31 Oct 2015 there is no previous records.   The problem is, if there is any previous records, for example, 30-Sep-2015, 31-Oct-2015 etc.  because there is a record before 31-Oct-2015 the query should not return any records.  For example:

DATE                 BBLS         WATER_CUT       WELL_NAME
30-Sep-2015        2                   .05                       SS1
31-Oct-2015        14                 .25                       SS1
30-Nov-2015        6                   .3                         SS1
31-Dec-2015         7                   .4                        SS1

When I use the parameter :end_date 31-Oct-2015 I don't want any records returned for Well SS1 because there are records with a date prior to Oct-31-2015.  The only records that should be displayed is for Well WW1.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much!  This solution was excellent, I tried it and it works.  I just have one more question related to this same topic.  I have other date  parameters in the code that uses the same parameter date as such the code looks like this

select mydate, bbls, water_cut, well_name from (
    select mydate, bbls, water_cut, well_name,
    count(case when mydate < to_date(:start_date, 'MON YYYY') then 1 end) over(partition by well_name) has_old_data
    from tab1
)
where has_old_data = 0
and prod_date =  :start_date
The prod_date uses DD-MON-YYYY and this date to_date(:start_date, 'MON YYYY') uses 'MON YYYY'  how can I adjust this code to use DD-MON-YYYY
UPDATE

Once I include the day it returns all the data.

count(case when PROD_DATE < to_date(:startdate_var,'YYYY-MON-DD') then 1 end)
over(partition by  fac) has_old_data,
UPDATE

I figured it out.  Thanks again for your assistance.
This solution was excellent.  I am really happy.  Thanks again!
Sorry for not responding, I had a meeting.

Glad you figured it out but I wanted to comment on this:
how can I adjust this code to use DD-MON-YYYY

Dates in Oracle don't have a format per say.  It is converted to a string when displayed so the displayed format doesn't matter.  You just need to make sure you do explicit data type conversions and compare dates with dates and strings with strings.  NEVER use implicit conversions and let Oracle try to convert a string to a date and a date to a string for you.
Thanks for the tip,