Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help (Oracle 10.2.0 Windows Server 2003)
SQL Help [message #305716] Tue, 11 March 2008 12:38 Go to next message
Messages: 51
Registered: August 2005
Hi Everyone

I need some help with a select statement.

the goal is to select donors that have a donation of one or more
in the years 2005, 2006 but no donations in 2004, 2007 & 2008 and then vice-versa(donations in (2004, 2007 & 2008) and none in (2005, 2006))

A donor must have at least 1 donation in all three years(or 2 years) not a combination of "either/or"

I wrote the below sql statement however it selects either/or donations in any one of the years outlined above.

thanks for any help/tips.

select m.idnumber from majordonor m 
where not exists(select pd.idnumber from paydonor pd
                 where pd.payamount>0 
                   and to_char(pd.paydate,'YYYY')in ('2004','2007','2008')
                   and pd.idnumber=m.idnumber)
and m.idnumber in(select p.idnumber from paydonor p 
                  where p.payamount>=30 and to_char(p.paydate,'YYYY') in('2005','2006'))

[Updated on: Tue, 11 March 2008 13:02] by Moderator

Report message to a moderator

Re: SQL Help [message #305734 is a reply to message #305716] Tue, 11 March 2008 14:36 Go to previous message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will approach this problem by using pivoting logic on the year grouped by donor_id. Search for the keyword pivot.


Previous Topic: Loading time dimension table
Next Topic: How to convert many row data to columns?
Goto Forum:

Current Time: Sat Jul 22 18:16:06 CDT 2017

Total time taken to generate the page: 0.21521 seconds