Query Help - 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)

Query Help

Author

Topic

Murray_67
Starting Member

11 Posts

Posted - 2005-11-03 : 16:12:38

I need some help creating a view. I have got two tables
ChargeDetails
ChgDetID|ChgHdrID|InvoiceNum|Code|TotalFee|Carrier
353|198|26193|L251|200.00|NULL
354|198|26193|W995|30.00|FedEX
1308|200|29229|M125|160.00|Avalon
1309|200|29229|C525|30.00|Avalon
1310|200|29229|D559|20.00|Avalon
1311|200|29229|W12Q|30.00|Avalon
1322|212|29289|W827|130.00|SNV
1352|344|31522|L727|450.00|NULL

Payments
PaymentID|ChgDetID|InvoiceNum|EntryDate|PayAmt
321|353|26193|04/27/2005|10.00
322|353|26193|05/05/2005|125.00
323|354|26193|05/05/2005|30.00
1006|1308|29229|09/27/2005|10.00

I need a view that shows the balance for an invoice like below

InvoiceNum|CarrierBalance|NoCarrierBalance
26193|0|65.00
29229|230.00|0
29289|130.00|0
31522|0|450.00

I tried using the query below:
SELECT IsNull(IP.CarrierBalance,0) as CarrierBalance, IsNull(SP.NoCarrierBalance,0) as NoCarrierBalance
FROM ChargeDetails CD
LEFT OUTER JOIN ( SELECT CD.InvoiceNum, SUM(CD.TotalFee)-(ISNULL(SUM(P.PaymentAmt),0)) AS NoCarrierBalance
FROM ChargeDetails CD
INNER JOIN Payments P ON CD.ChgDetID = P.ChgDetID
WHERE CD.CarrierID IS NULL
GROUP BY CD.InvoiceNum) SP ON SP.InvoiceNum = CD.InvoiceNum
LEFT OUTER JOIN (
SELECT CD.InvoiceNum,
SUM(CD.TotalFee)-(ISNULL(SUM(P.PaymentAmt),0)) AS CarrierBalance
FROM ChargeDetails CD
INNER JOIN Payments P ON CD.ChgDetID = P.ChgDetID
WHERE CD.CarrierID IS NOT NULL
GROUP BY CD.InvoiceNum) IP ON IP.InvoiceNum = CD.InvoiceNum

Thanks for your help

Murray

anuj164
Starting Member

49 Posts

Posted - 2005-11-03 : 17:16:41

Murray,

Can you explain your output; how are you getting 65

InvoiceNum|CarrierBalance|NoCarrierBalance
26193|0|65.00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 01:07:08

Yes Explain the logic and that would be helpful to give you solution

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources