Best Practice 1

[Memento]

The following SQL is my solution to a query a user had, but I'm unsure if this was a "Best Practice" SQL solution. I used a subquery to find what they were looking for.

The user wanted a list of people who made donations in 2004 but haven't yet in 2005. They didn't want to send reminders to people who have already contibuted. The user wanted to create a mailing list from this query.

I used the MAX function to find the last donation made and excluded the 2005 records.

It seems like there should be simpler solution than the one I came up with.

SQL Server 2000 Code:

SELECT Main.FirstName, Main.LastName, Main.Address1, Main.Address2, Main.City, Main.State, Main.Zip, Donor.GiftAmt, Donor.DonationPmtDate,
Donor.FundSource, Main.Deceased,
(SELECT MAX(DatePart(year, D.donationpmtdate))
FROM Donor AS D
WHERE D.MasterID = Donor.MasterID) AS DateCheck
FROM Main LEFT OUTER JOIN
Donor ON Main.MasterID = Donor.MasterID
WHERE (Donor.GiftAmt >= 0) AND (Donor.DonationPmtDate BETWEEN CONVERT(DATETIME, '2004-01-01 00:00:00', 102) AND CONVERT(DATETIME,
'2005-12-31 00:00:00', 102)) AND
(((SELECT MAX(DatePart(year, D.donationpmtdate))
FROM Donor AS D
WHERE D.MasterID = Donor.MasterID)) <> 2005)
ORDER BY Main.LastName

 

[vongrunt]

> The user wanted a list of people who made donations in 2004 but haven't yet in 2005.

Main = persons, Donor = donations (1:n), right?

Do you want one record per person (Main table) or one record per person's donation in 2004 (as query currently does)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Memento]

>>Main = persons, Donor = donations (1:n), right?

Correct

Main = Detailed info on people. PK = MainID ie...name, address, phone..

Donor = Payment dates, Payement amounts all linked by the FK = MainID

>>Do you want one record per person (Main table) or one record per person's donation in 2004 (as query currently does)?

I want one record from the Main table based on the Donations table field DonationPmtDate. The user is going to generate mailing labels based on information in the Main table (address, City, Zip). The Donor table is used to select which people need reminders for 2005.

 

[vongrunt]

> I want one record from the Main table based on the Donations table field DonationPmtDate.

In that case try something like:

SELECT M.FirstName, M.LastName, M.Address1, M.Address2, M.City, M.State, M.Zip, D.lastPmtDate as DateCheck, M.Deceased
FROM Main M INNER JOIN
(	SELECT MasterID, max(DonationPmtDate) as lastPmtDate
	FROM Donor
	WHERE GiftAmt > 0
		AND DonationPmtDate >= '2004' AND DonationPmtDate < '2006'
	GROUP BY MasterID
	HAVING max(DonationPmtDate) >= '2004' AND max(DonationPmtDate) < '2005'
) D ON D.MasterID = M.MasterID
-- WHERE M.Deceased = 0
ORDER BY M.LastName, M.FirstName

If you also need some information from last donations in 2004 other than DonationPmtDate (FundSource ?) then things are slightly more complicated.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Memento]

Thanks VonGrunt!

Very readable code. I don't know why I didn't use the HAVING clause. It would have made more sense. Again, many thanks on your help.