2

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

Charles
  • 50,943
  • 13
  • 104
  • 142
user2089954
  • 61
  • 2
  • 1
    Are you dates real dates (by datatype) or are they strings in the format MM/DD/YYYY? – Marc Fischer Feb 20 '13 at 06:30
  • When dealing with SQL it usually helps if you give us some code to create the table you're having instead of an ASCII table dump. – Joey Feb 20 '13 at 08:10

3 Answers3

4

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;
APC
  • 144,005
  • 19
  • 170
  • 281
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
1

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);
APC
  • 144,005
  • 19
  • 170
  • 281
i100
  • 4,529
  • 1
  • 22
  • 20
  • these will return also the id: 12, 13 – Florin Ghita Feb 20 '13 at 06:46
  • your condition is "... has only Monday, Tuesday and Wednesday dates"; well 2/11 and 2/12 ARE Monday and Tuesday... – i100 Feb 20 '13 at 07:07
  • 1
    Is the OP condition, not mine. Read his requirement again. He wants the ids wich have those three days and only three. Not just two and not anonther different day. – Florin Ghita Feb 20 '13 at 07:16
  • Also, the value of D is dependent on NLS_TERRITORY setting. In many places 1 is not Monday, it is Sunday or Saturday. – APC Feb 20 '13 at 10:21
1
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.

igr
  • 3,409
  • 1
  • 20
  • 25