ernie_shah
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.
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.
Please provide sample data and expected results.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
UPDATE
Once I include the day it returns all the data.
count(case when PROD_DATE < to_date(:startdate_var,'YY YY-MON-DD' ) then 1 end)
over(partition by fac) has_old_data,
Once I include the day it returns all the data.
count(case when PROD_DATE < to_date(:startdate_var,'YY
over(partition by fac) has_old_data,
ASKER
UPDATE
I figured it out. Thanks again for your assistance.
I figured it out. Thanks again for your assistance.
ASKER
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.
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.
ASKER
Thanks for the tip,