Selecting the Penultimate Record

[SQLJoe]

I have a table that tracks donations made by people to a variety of different funds. Information tracked includes a donor id, the fund id for what they donated to, the date of the donation, etc. Every day various mailings (using different templates for each fund) are sent out for all donations made in the last 24 hours. I must implement a business rule, however, that says if a donor has donated to the same fund more than once in the last 31 days, exclude them from the mailing. If they have donated to other funds in that time period, the mailing should still go out.

At first I thought I would create a subquery (select distinct donor_id, fund_id, donation_date from table where datediff(d,donation_date,getdate()) between 2 and 31) then do a compound outer join with the same base table on donor_id and fund_id, then use a null criteria on the subquery side. (I use "between 2 and 31" in the subquery instead of simply "<32" because, if I include yesterday, which I'm merging for, the outer query would never return any rows because yesterday's records would also be in the subquery.)

Isn't there an easier way to set this up? Isn't there a simple way to select the next-to-last donation for each donor/fund combination and do a datediff on it? My solution seems unnecessarily complex. If I can just compare each row with the next-to-last one having the same donor/fund in the last 31 days, I can avoid all this hoop jumping.

**Please note that this cannot be done in a sproc because the software using this data source can only use tables or views.

 

[Qik3Coder]

I was going to do this awesome cross join thing.
Then I was gonna do this row_number thing.
Then I re-read the problem...

Setup:

Declare @_tbl as table(
donor_id int,
fund_id int,
donation_date datetime)

insert into @_tbl
Select 1, 1, GetDate() UNION ALL
Select 1, 1, GetDate()-35 UNION ALL
Select 2, 1, GetDate() UNION ALL
Select 2, 1, GetDate()-14 UNION ALL
Select 3, 1, GetDate() UNION ALL
Select 3, 2, GetDate()-35 UNION ALL
Select 4, 2, GetDate() UNION ALL
Select 4, 2, GetDate()-14 UNION ALL
Select 5, 3, GetDate() UNION ALL
Select 5, 2, GetDate()-14

Work:

Select d.donor_id, d.fund_id, d.donation_date
FROM @_tbl d
LEFT JOIN 
	(SELECT DISTINCT donor_id, fund_id
	FROM @_tbl
	WHERE donation_date between GetDate()-31 and GetDate()-2
	) as pd ON
	d.donor_id = pd.donor_id
	and d.fund_id = pd.fund_id
WHERE pd.donor_id is null and d.donation_date > GetDate() -2

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD