Clever Geek Handbook

Find only individual days between two dates

I have an Oracle table with data as shown below:

 1. ID           DATE 
 2. 12           02/11/2013
 3. 12           02/12/2013
 4. 13           02/11/2013
 5. 13           02/12/2013
 6. 13           02/13/2013
 7. 13           02/14/2013
 8. 14           02/11/2013
 9. 14           02/12/2013
10. 14           02/13/2013

I only need to find IDs that only have Monday, Tuesday and Wednesday dates, so only ID = 14 needs to be returned here. I am using Oracle and the dates are in MM / DD / YYYY format. Please advice.

Regards, Nitin

+3


source to share


3 answers


If the date column is DATE datatype you can

select id
from your_table
group by id
having sum(case 
           when to_char(date_col,'fmday') 
                in ('monday','tuesday','wednesday') then 1
           else 99
           end) = 3;

EDIT : Fixed the above code when observing igr

But that's ok only if you don't have the same day twice for the same ID.



If the column is varchar2 then the condition becomes to_char(to_date(your_col,'mm/dd/yyyy'),'fmday') in ...

More robust code:

select id 
from(
    select id, date_col
    from your_table
    group by id, date_col
)
group by id
having sum(case 
           when to_char(date_col,'fmday', 'NLS_DATE_LANGUAGE=ENGLISH') 
                    in ('monday','tuesday','wednesday') then 1
           else 99
           end) = 3;
+4


source


do something like

SELECT * FROM your_table t 
      where to_char(t.DATE, 'DY') in ('whatever_day_abbreviation_day_you_use');

alternatively if you prefer to use day numbers like:



SELECT * FROM your_table t 
     where  to_number(to_char(d.ts, 'D')) in (1,2,3);

if you want to avoid repeating ID add DISTINCTION

SELECT DISTINCT ID FROM your_table t 
     where  to_number(to_char(d.ts, 'D')) in (1,2,3);
+1


source 


select id 
from (
  select 
     id, 
     sum (case when to_char(dt, 'D', 'nls_territory=AMERICA') between 1 and 3 then 1 else -1 end) AS cnt
  from t
  group by id
)
where cnt=3

NOTE. I assumed (id, dt) is unique - there are no two strings with the same id and date.

+1


source






More articles:


All Articles