I've got a SQL statement that has me stumped. Consider the following
scenario:

A donor gives the following gifts:
in 2006 they give $250
in 2007 they give $550
in 2008 they give $50

My report needs to select this individual because any one of their
gifts exceeded $500 and then it needs to print all of their gifts in
every year since 2006. Even though the other 2 gifts don't meet the
over $500 criteria, they need to appear on the report since the
individual has one gift that exceeds $500.

I've got the following so far:
select people.people_code_id, givingsummary.giving_amount,
givingsummary.fiscal_year from
people, givingsummary
where people.people_code_id = givingsummary.people_org_code_id and
givingsummary.giving_amount in (select giving_amount from
givingsummary where giving_amount>=500 and
fiscal_year<>'')

My problem is that the query is excluding gifts less than $500, so
they aren't printing on my report for people who have given one gift
that was over $500. How do I tell SQL to print all gifts within a
specific timeframe if one of the gifts exceeds $500?

Thanks in advance!

Here is one way:

SELECT P.people_code_id, G.giving_amount, G.fiscal_year
FROM People AS P
JOIN GivingSummary AS G
ON P.people_code_id = G.people_org_code_id
WHERE EXISTS (SELECT *
FROM GivingSummary AS G2
WHERE G2.people_org_code_id = G.people_org_code_id
AND G2.giving_amount >= 500
AND G2.fiscal_year <> '');


HTH,

Plamen Ratchev
http://www.SQLStudio.com

On May 8, 1:20*pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> Here is one way:
>
> SELECT P.people_code_id, G.giving_amount, G.fiscal_year
> FROM People AS P
> JOIN GivingSummary AS G
> * ON P.people_code_id = G.people_org_code_id
> WHERE EXISTS (SELECT *
> * * * * * * * * * * *FROM GivingSummary AS G2
> * * * * * * * * * * *WHERE G2.people_org_code_id =G.people_org_code_id
> * * * * * * * * * * * * *AND G2.giving_amount >= 500
> * * * * * * * * * * * * *AND G2.fiscal_year <> '');
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Hi,

Thanks for the response. That is perfect. This leads to one more
question. Now that I am selecting the right group of people and
gifts, I need the select statement to adjust based on the number of
years the users want to report on. I've created a prompt where the
user enters a starting fiscal year. I then need to pull individuals
who have given a gift of $500 or more in any year starting with the
fiscal year the user entered. So, somehow I need to tell the query to
look to the user entered fiscal year and then run the exists statement
for years greater than or equal to that year.

For example, in the example I gave above the donor gives the following
gifts:
in 2006 they give $250
in 2007 they give $550
in 2008 they give $50

If at the prompt the user enters 2006, then all 3 gifts above would
appear. If at the prompt the user enters 2008, then the person would
not appear on the list because they do not have a gift above $500 in
the time period requested.

I tried adding in something along the lines of below, but it doesn't
seem to be working. Any suggestions?

WHERE EXISTS (SELECT *
FROM GivingSummary AS G2
WHERE G2.people_org_code_id =
GIVINGSUMMARY.people_org_code_id
AND G2.giving_amount >= 500
AND G2.fiscal_year <> ''
AND G2.FISCAL_YEAR >= @FiscalYear)

Add one more condition to the main WHERE clause to return only the correct
years:

WHERE G.fiscal_year >= @FiscalYear
AND EXISTS(...

HTH,

Plamen Ratchev
http://www.SQLStudio.com