Selecting from all elements in a list 2

[LaurenM]

I am trying to find all users who had transactions on three different dates and I don't want to wade through a compilation of the transactions of those dates.

The only way I know how to do it is with intersect:

select * from transactions where transaction date = date1
intersect
select * from transactions where transaction date = date2
intersect
select * from transactions where transaction date = date3

I would like to know if there is something similar to the in operator, so that I can write the SQL stmt like the following:

select * from transactions where transaction date [i]operator name[/i] (date1, date2, date3)

The in operator will give me a list of users who had transactions on any of those three dates, but I only want the ones that had transactions on all three dates.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[carp]

Maybe not an operator, but what about something like

SELECT username FROM 
(SELECT username FROM transactions WHERE TRUNC(txn_date) = date1
  INTERSECT 
  SELECT username FROM transactions WHERE TRUNC(txn_date) = date2
  INTERSECT 
  SELECT username FROM transactions WHERE TRUNC(txn_date) = date3
)

Doubtless you will get more elegant suggestions from the likes of Santa or Dagon, but this came to mind. Unfortunately, it is still going through transactions three times, so you might want to consider a function-based index on txn_date.

 

[Dagon]

Assuming there is some sort of user_id you want to identify as well as just the date, you could do something like this:

create table mytrans (trans_date date, pers_no number);

insert into mytrans values (trunc(sysdate), 1);

insert into mytrans values (trunc(sysdate), 2);

insert into mytrans values (trunc(sysdate), 3);

insert into mytrans values (trunc(sysdate)-1, 1);

insert into mytrans values (trunc(sysdate)-1, 2);

insert into mytrans values (trunc(sysdate)-2, 1);

select * from mytrans

select pers_no, trans_date
from
(
select pers_no, trans_date, count(distinct trans_date) over (partition by pers_no) as tdcount 
from mytrans
where trans_date in (trunc(sysdate), trunc(sysdate)-1, trunc(sysdate)-2))
where tdcount = 3

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

Lauren,

The code that Dagon posted is clever, tight, and excellent. (I don't know that I would have thunk of that clever method myself.)

Hava

, Dagon.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

Thanks, Santa, it's good to know I'm not putting these suggestions out into a vacuum.

For Oracle-related work, contact me through Linked-In.