CODEWITHSUNDEEP
oracle-databasedatedate-math
user2089954
user2089954

Reputation: 61

Find only particular days between two dates

I have an Oracle table with data like 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 need to find only those ID who has only Monday, Tuesday and Wednesday dates, so here only ID = 14 should be returned. I am using Oracle and dates are in format MM/DD/YYYY. Please advice.

Regards, Nitin

Upvotes: 2

Views: 630

Answers (3)

Florin Ghita
Florin Ghita

Reputation: 17643

If date column is DATE datatype, then 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: Corected the above code at the igr's observation

But this is ok only if you don't have a 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 ...

A more robust code would be:

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;

Upvotes: 4

i100
i100

Reputation: 4666

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 you could use day numbers like:

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

if you'd like to avoid ID repetition add DISTINCTION

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

Upvotes: 1

igr
igr

Reputation: 3499

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 - no two lines with same id and date.

Upvotes: 1

Related Questions