First Time Donor In SQL 2005

[sap1958]

I have the following stored procedure:
ALTER PROCEDURE [dbo].[Cash_
-- Add the parameters for the stored procedure here
@BeginDate datetime,
@EndDate datetime ,
@Division varchar(50)
AS
SET NOCOUNT ON;

SELECT
giftid,giftacctno,giftacctdv,
giftkey,gifttype,gifteffdat,giftjntamt,giftamount
from gifts
WHERE (gifttype ='b' oR gifttype ='g' OR gifttype ='y')
and (gifteffdat>=@BeginDate AND gifteffdat<=@EndDate)
and giftacctdv = @Division

This allows me to obtain a report based on a date range (ie 7/1/09 through 7/31/09). Now I want an indicator if it is the first time the person has given based on the date being less than the begin date and the division. For example if a person did not give anything to Division #41 prior to 7/1/09 there should be an indicator of 'Y'. How could I accomplish this. I am thinking there can be a max(case statement or similar)

 

[mrdenny]

Something like this should do the trick. (This isn't tested and may need some tweaking.)

ALTER PROCEDURE [dbo].[Cash_
    -- Add the parameters for the stored procedure here
    @BeginDate datetime,
    @EndDate datetime ,
    @Division varchar(50)
AS
SET NOCOUNT ON;

SELECT gifts.giftid, gifts.giftacctno, gifts.giftacctdv,
 gifts.giftkey, gifts.gifttype, gifts.gifteffdat, gifts.giftjntamt, gifts.giftamount, CASE WHEN a.GiftEffDat > @BeginDate THEN 'N' ELSE 'Y' END
FROM gifts
JOIN (SELECT GiftAcctNo, giftacctdv, MIN(GiftEffDat) GiftEffDat
     FROM Gifts
     GROUP BY GiftAcctNo, giftacctdv) a ON a.GiftAcctNo = gifts.GiftAcctNo AND a.GiftAcctDv= gift.GiftAcctDv
WHERE  (gifttype ='b' oR gifttype ='g' OR gifttype ='y')
   and (gifteffdat>=@BeginDate AND gifteffdat<=@EndDate)
   and giftacctdv = @Division

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog