Case statments & subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case statments & subquery

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-11-02 : 11:49:10

Hope someone can help;

I have a table with a list of payment information i have three other tables that store different types of commission rates that were active at a particular time.

Payments table – holds all payments received by customers

DirectRate table – holds the Direct rate active between start and end dates
ComRate table – holds the Commission rate active between start and end dates
FieldRate table – holds the Field rate active between start and end dates

Basically I am trying to get the total value of commission on all payments for all the different rates. To give you an example one payment can be of type Direct which would have to have the correct payment rate applied from the DirectRate table for the correct date range, this also applies for payments that are of type ComRate & FieldRate.

So I have the following SQL

SELECT CASE
WHEN dp.ReceivedByID = 1
THEN

dp.Amount * ((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)))/100)

WHEN dp.ReceivedByID = 2
THEN

dp.Amount * ((Select tD.Rate From dbo.mTrackerDirectChange tD where tD.ClientID=d.ClientID and tD.ContractID=d.ContractID AND ((dp.PaymentOn >= tD.StartDate AND dp.PaymentOn <= tD.EndDate) or (dp.PaymentOn >= tD.StartDate AND tD.EndDate IS NULL)))/100)

ELSE

dp.Amount * (((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL))) + (Select tFe.Rate From dbo.mTrackerFieldChange tFe where tFe.ClientID=d.ClientID and tFe.ContractID=d.ContractID AND((dp.PaymentOn >= tFe.StartDate AND dp.PaymentOn <= tFe.EndDate) or (dp.PaymentOn >= tFe.StartDate AND tFe.EndDate IS NULL))))/100)

END

From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d
where
d.DebtID=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=2

I am using dp.ReceivedByID to assertain the payment type then depending upon that using the case statement to multiply the amount by the correct rate for the correct date range in the correct table. This sql works fine but it gives me a list of commision values, one for each payment. My problem is when I try to do a sum on this case statement I get an error

“Cannot perform an aggregate function on an expression containing an aggregate or a subquery”

any help most appriciated

p

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 12:06:47

have you tried moving the subqueries down as joins? may be bad syntax but something like...
SELECT CASE
WHEN dp.ReceivedByID = 1
THEN dp.Amount * (tF.Rate /100)
WHEN dp.ReceivedByID = 2
THEN dp.Amount * (tD.Rate /100)
ELSE dp.Amount * ((tF.Rate + tD.Rate) /100)
From
dbo.DebtPayment dp
join dbo.Debt d
on d.DebtID=dp.DebtID
join dbo.ImportBatchItem bi
on dp.DebtID=bi.ItemID

LEFT JOIN dbo.mTrackerFeeChange tF
ON tF.ClientID=d.ClientID
and tF.ContractID=d.ContractID
AND (
(dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate)
or
(dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)
)

LEFT JOIN dbo.mTrackerFeeChange tF
ON tF.ClientID=d.ClientID
and tF.ContractID=d.ContractID
AND (
(dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate)
or
(dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)
)
where bi.ImportBatchID=2

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-11-02 : 12:31:56

I did try this, but using joins gives me additional values ie. there will be a DirectRate range in the Direct table that will coincide with a FeeRate range in the Fee table, but the payment would be off only one type so would only want to go to the appropriate table.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 12:47:26

do the additional values matter if you use the case statement to specify use only the directrate even though there is a match to the feerate when receivebyid is 1?

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-11-02 : 13:26:04

If i understand you correctly, yes they do matter.

Payment table
-------------

id | Amount | ReceivedByID | PaymentOn
---------------------------------------------
1 | 100 | 1 | 10/10/2007
2 | 50 | 1 | 11/10/2007
3 | 39 | 2 | 12/10/2007
4 | 20 | 1 | 12/10/2007
5 | 36 | 0 | 13/10/2007

DirectRate(type 1) table ------FeeRate(type 2) & FieldRate(type 0) tables of similar structure
-------------

id | Rate | Start | End
---------------------------------------------
1 | 20 | 19/02/2007 | 29/03/2007
2 | 30 | 30/03/2007 | 22/05/2007
3 | 25 | 22/05/2007 | 10/10/2007
4 | 15 | 11/10/2007 |

so as can be seen payment 1 would go to the DirectTable and get the rate of 25. but payment 3 would go to the FeeRate table and get the appropriate rate for the date range in the fee table. i then want to sum all the values to give me one commision value total for this group of payments.

Joining to the three tables means a value is returned irespective of the payment type.

(scrattching head!!!!)

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:13:36

adding an additional table to get a clearer picture
if this is the FeeRate (type 2) table
id | Rate | Start | End
---------------------------------------------
1 | 2 | 19/02/2007 | 29/03/2007
2 | 4 | 30/03/2007 | 22/05/2007
3 | 6 | 22/05/2007 | 10/10/2007
4 | 8 | 11/10/2007 |

would payment (3) left join directrate left join fee rate be
rcvid paymentid amount directrate feerate
2 3 39 25 6

does this then work?
case
when rcvid = 1 then 39 * 25
when rcvid = 2 then 39 * 6
else (39 * 25) + (39 * 6)
end

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-11-03 : 19:09:50

sorry didnt quite follow that. the basic problem is if the payment is of type 1 then i ONLY want to get matches back from the DirectRate table, but if the next payment is of type 2 then i ONLY want to get matches back from the FeeRate table and then if a third payment is of type 3 then i ONLY want matches back from the FieldRate table. Using joins seems to return matches from tables that are not of the same payment type as the payment.

again scratching head.

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-11-05 : 04:21:04

Guys,

just to close this off, while typing out another explination to my problem the light bulb suddenly came on. in my left joins i was missing linking ratetyp=dp.ReceivedByID which was why i was getting rows back from table i should not have. I should also be able to put a all the rate changes into the one table as opposed to three.

thanks for the chaps!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources