Hey, all.

I need some help figuring out the syntax for this SQL statement in a Stored Procedure.

Currently, I have this & it works fine returning a recordset to the ASP page:
CREATE PROCEDURE sp_LoadRequestData @CountyID Numeric (9), @MuniID Numeric (9) AS

SELECT Collectable.EntityOID, Collectable.EntityName, Collectable.CollectableOID, Collectable.CountyOID, Collectable.LocaleOID, Collectable.PayeeOID, Collectable.PayPayee, CollectableType.Collectable,
Payee.PayeeName, Payee.CertFees
FROM Collectable
INNER JOIN CollectableType ON Collectable.CollectableOID = CollectableType.CollectableOID
INNER JOIN Payee ON Collectable.PayeeOID = Payee.PayeeOID
WHERE Collectable.CountyOID = @CountyID OR Collectable.LocaleOID = @MuniID
GO

What I need to do is add a total field for the Payee.CertFees, but it needs to return one value per PayeeOID. That is, it is possible for a Payee to handle more than one job, but if they do, they only collect a fee once. So if there are two rows for the same Payee returned, I only want to count the CertFee for that Payee once.

I have tried to put SUM(Payee.CertFees) AS 'TotalCosts' in the statement, but I haven't been able to figure out how to tell it that the Payee needs to be DISTINCT.

Any ideas?

Thanks,

Dave

You can do that using Compute Clause. But I think you wont be able to interpret that in you ASP record set. I assume you would want a result something like this.

COL1 COL2 COL3
COL1 COL2 COL3
COL1 COL2 COL3
          SUM
COL1 COL2 COL3
COL1 COL2 COL3
          SUM

This can be done using something like
COMPUTE sum(Payee.CertFees) by Payee.PayeeOID

AFAIK the ASP recordset would require all the rows to have equal columns.

You cannot process it with your recordset, if it is going to be irregular.

So I would suggest you to do this in your ASP per PayeeOID, when you loop through your recordset. You can sum up the Payee.CertFees untill the PayeeOID changes, and reset it to ZERO for every PayeeOID.

Hope that helps.

Cheers!


-Vijay G