Finding Maximum Amount specified for Maximum date specified in SQL Server Database
===

Alright, so my goal with this procedure is to find the Maximum amount payment promise that matches up with the maximum date of the payment promise.  I am going to include the procedure where this pulls from, so you will see 2 procedures here.

This first procedure does pretty close to what I want it to do, but there are 2 issues.  If there are 2 values in the PaymentPromise table with the same date, and that date is the maximum, both values will be returned.  If there are no values in PaymentPromise but the balance is still outstanding, this should be returned with null values under PaymentPromise, but it does not do so.

SQL Server 2008, just so you know.

    ALTER PROCEDURE dbo.GetPaymentsDue
	(
	@Username varchar(20)
	)
    AS
	DECLARE @BondAmounts TABLE
	(
	BondID bigint,
	BondAmount money,
	BondTotal money,
	BondBalance money
	)

	INSERT INTO @BondAmounts EXEC dbo.GetBondAmounts @Username = @Username

	SELECT Promise.ID, bonds.BondID, Bond.Agency, Bond.Agent, Bond.Defendant, Promise.ExpectedDate,
	Promise.ExpectedAmount, bonds.BondTotal, bonds.BondBalance
	FROM @BondAmounts bonds
	INNER JOIN Bond ON bonds.BondID = Bond.ID
	LEFT OUTER JOIN
	(
		SELECT ID, BondID, ExpectedAmount, ExpectedDate, MAX(ExpectedDate) OVER (PARTITION BY BondID) MaxDate
		FROM PaymentPromise
	) Promise ON bonds.BondID = Promise.BondID
	WHERE bonds.BondBalance > 0
	AND (Promise.ExpectedDate = Promise.MaxDate OR Promise.ExpectedDate = NULL)

So this first procedure calls the second procedure to get the initial data set to work with.

    ALTER PROCEDURE dbo.GetBondAmounts
	(
	@Username varchar(20)
	)
    AS
	SELECT Bond.ID BondID, (ISNULL(Powers.Amount,0) + ISNULL(Charges.Amount,0)) BondAmount,
	(ISNULL(BondFee.Amount,0) + ISNULL(Powers.Premium,0) + ISNULL(Charges.Premium,0)
	+ ISNULL(Forfeiture.CostOfApprehension,0) + ISNULL(Forfeiture.AmountPaid,0) + Bond.StateTax) BondTotal,
	(ISNULL(BondFee.Amount,0) + ISNULL(Powers.Premium,0) + ISNULL(Charges.Premium,0)
	+ ISNULL(Forfeiture.CostOfApprehension,0) + ISNULL(Forfeiture.AmountPaid,0) + Bond.StateTax
	- ISNULL(BalanceForgiveness.Amount,0) - ISNULL(Payment.Amount,0)) BondBalance
	FROM Bond
	LEFT OUTER JOIN
	(
		SELECT Agency, Username FROM UserAgency
		WHERE Username = @Username
	) AS UserAgency ON Bond.Agency = UserAgency.Agency
	LEFT OUTER JOIN
	(
		SELECT BondID, SUM(AmountForgiven) Amount
		FROM BalanceForgiveness
		GROUP BY BondID
	) AS BalanceForgiveness ON Bond.ID = BalanceForgiveness.BondID
	LEFT OUTER JOIN
	(
		SELECT Bond, SUM(Amount) Amount
		FROM BondFee
		GROUP BY Bond
	) AS BondFee ON Bond.ID = BondFee.Bond
	LEFT OUTER JOIN
	(
		SELECT Powers.Bond, SUM(Charge.BondAmount) Amount,
		ISNULL(SUM(Charge.BondPremium), 0) Premium
		FROM Powers INNER JOIN Charge ON Powers.Surety = Charge.PowerSurety
		AND Powers.PowerPrefix = Charge.PowerPrefix AND Powers.PowerNumber = Charge.PowerNumber
		GROUP BY Bond
	) AS Powers ON Bond.ID = Powers.Bond
	LEFT OUTER JOIN
	(
		SELECT BondID, SUM(BondAmount) Amount, SUM(BondPremium) Premium
		FROM ChargeWithoutPower
		GROUP BY BondID
	) AS Charges ON Bond.ID = Charges.BondID
	LEFT OUTER JOIN
	(
		SELECT Bond, SUM(CostOfApprehension) CostOfApprehension, SUM(AmountPaid) AmountPaid
		FROM Forfeiture
		GROUP BY Bond
	) AS Forfeiture ON Bond.ID = Forfeiture.Bond
	LEFT OUTER JOIN
	(
		SELECT Bond, SUM(Amount) Amount
		FROM Payment
		GROUP BY Bond
	) AS Payment ON Bond.ID = Payment.Bond
	WHERE UserAgency.Username = @Username
	OR EXISTS (SELECT * FROM Users WHERE Username = @Username AND Admin = 1)