Case - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Case

Author

Topic

Murray_67
Starting Member

11 Posts

Posted - 2004-11-11 : 13:29:29

The red section fails.

ChargeHeadersLog
ChgHdrLogID | ChgHdrID| CustomerID| CompanyID| SalesPID | HasPayments
358 | 208 | 200 | 1 | 1 | 1
363 | 213 | 300 | 1 | 1 | 1

ChargeDetailsLog
ChgDetLogID | ChgHdrLogID | ChgDetID | ChgHdrID | CodeID | CarrierID | CarrierOrder | TotalFee | TranOrPosted
1700 | 358 | 353 |208 |P456 | JC | Primary | 90.00 | 1
1702 | 358 | 353 | 208 | P456 | CBF | Secondary |90.00 | 1
1703 | 358 | 353 | 208 | P456 | NULL |Tertiary | 90.00 | 1
1709 | 363 | 356 | 213 | P752 | HNP | Primary | 125.00 | 1
1711 | 363 | 356 | 213 | P752 | NULL |Secondary | 125.00 | 1

PaymentDetailsLog
PayDetLogID | PayHdrLogID | ChgDetLogID | PayDetID | PayHdrID | ChgDetID | CarrierID | PaymentAmt | BillableAmt | AdjustedAmt | TranOrPosted
16 | 108 | 1700 |13 |105 | 353 | JC | 40.00 | 65.00 | 25.00 | 1
17 | 109 | 1702 |14 |106 | 353 | CBF | 15.00 | NULL | NULL | 1
19 | 111 | 1709 |16 |108 | 356 | HNP | 42.00 | 72.00 | 53.00 | 1

SELECT

Billed_Amount = CASE
WHEN CHL.HasPayments = 0 THEN SUM(CDL.TotalFee)
WHEN CHL.HasPayments = 1 AND SUM (PDL.PaymentAmt) IS NOT NULL AND SUM(PDL.BillableAmt) IS NULL
THEN SUM(PDL.PaymentAmt) + ISNULL (SUM(PDL.AdjustedAmt),0)
WHEN CHL.HasPayments = 1 AND SUM (PDL.PaymentAmt) IS NOT NULL AND SUM(PDL.BillableAmt) IS NOT NULL
THEN SUM(PDL.PaymentAmt) + SUM(PDL.AdjustedAmt)
WHEN CHL.HasPayments = 1 AND SUM (PDL.PaymentAmt) IS NULL AND SUM(PDL.AdjustedAmt)IS NULL
THEN (SELECT SUM(PDL.BillableAmt) - SUM(PDL.PaymentAmt)
FROM PaymentDetailsLog PDL)
END

FROM ChargeDetailsLog CDL

INNER JOIN ChargeHeadersLog CHL ON CDL.ChgHdrLogID = CHL.ChgHdrLogID
LEFT OUTER JOIN PaymentDetailsLog PDL ON CDL.ChgDetLogID = PDL.ChgDetLogID
LEFT OUTER JOIN PaymentHeadersLog PHL ON PDL.PayHdrLogID = PHL.PayHdrLogID
GROUP BY CHL.CompanyID, CHL.SalesPID, CDL.CodeID, CDL.CarrierID, CHL.HasPayments

Whe this query is run I get the following:
40.00
15.00
65.00
40.00
95.00

I would like to get this:
Billed_Amount
10.00
15.00
65.00
30.00
95.00

Murray

Murray_67
Starting Member

11 Posts

Posted - 2004-11-11 : 13:30:30

Also if I use a group by
WHEN CHL.HasPayments = 1 AND SUM (PDL.PaymentAmt) IS NULL AND SUM(PDL.AdjustedAmt)IS NULL
THEN (SELECT SUM(PDL.BillableAmt) - SUM(PDL.PaymentAmt)
FROM PaymentDetailsLog PDL
GROUP BY PDL.ChgDetID)
I get an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation

Murray

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 13:34:59

you have no relation in your subquery -- it is just returnign the total from all rows. i assume you want to get the totals just for that row's ChgDetLogID, not the entire contents of the table?

if so, you need to add a WHERE clause to your subquery:

WHERE CDL.ChgDetLogID = PDL.ChgDetLogID

Though aliasing two tables in your select as PDL will probably cause a problem -- you might need to change this in your subquery to PDL2 or something like that:

(SELECT SUM(PDL.BillableAmt) - SUM(PDL.PaymentAmt)
FROM PaymentDetailsLog PDL2 WHERE PDL2.ChgDetLogID = DCL.ChgDetLogID)

makes sense?

- Jeff

Murray_67
Starting Member

11 Posts

Posted - 2004-11-11 : 14:15:44

I think I am having a brain overload. I still do not understand. The ChargeDetailsLog rows that do not have any PaymentDetailsLog against them are the ones that I need to calculate that Billed_Amount for using the following: WHEN CHL.HasPayments = 1 AND SUM (PDL.PaymentAmt) IS NULL AND SUM(PDL.AdjustedAmt)IS NULL
THEN (SELECT SUM(PDL.BillableAmt) - SUM(PDL.PaymentAmt)
FROM PaymentDetailsLog PDL)
You are right in your assumption that I need the totals for the ChgDetLogID rows.
Example for ChgDetlogID 1703 there is no paymetdetailslog row so I would then use the sum of the Billableamt for that ChgDetID 65.00 -sum of Paymentamt for the particular ChgDetID 40.00+15.00 = 10.00

Can you clarify what you telling me please.

Murray

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 14:26:26

forget the SQL -- what calculation are you trying to make? how are you trying to calculate the results of your query. I can't follow your logic, or why you have that subquery there or what you are trying to do with all of the IS NULL checks ....

- Jeff

Murray_67
Starting Member

11 Posts

Posted - 2004-11-11 : 14:54:59

Ok
When there are no paymentdetlog row against a ChgDetID CHL.HasPayments will be 0 therefore the billed amount will just be the totalfee of that row.
When there is a PaymentDetLog row against a ChgDetID CHL.HasPayments will be 1 therefore the billed amount depends on if there is a PaymentAmt or a BillableAmt if there is a PaymentAmt and the the BillableAmt is null then the billed amount is will be the sum of PaymentAmt + sum of AdjustmentAmt or
lets say the their is PaymentAmt and there is BillableAmt the billed amount is then sum of PaymentAmt + sum of AdjustmentAmt
The last case is where I have a problem. There is no PaymentDetailsLog for the the current (last row in ChargeDetailsLog for that ChgDetID) I need the billed amount to equal the sum of payment amounts for that ChgDetID - sum of paymentamt for that ChgDetID.

Also disregard the is null checks they are not needed.

Murray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources