Problem creating a view

Question

Post reply

Problem creating a view

Den Murray

Valued Member

Points: 64
More actions
November 3, 2005 at 1:03 pm

#169349

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
Thanks for your help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

If you could post what you have already tried, I will try to help.

Michelle

Den Murray Valued Member Points: 64 More actions · Answer 2

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

I Get the following results:

InvoiceNum|CarrierBalance|NoCarrierBalance

26193|0|265.00 Incorrect

29229|150.00|0 Incorrect

29289|130.00|0

31522|0|450.00

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 3

CREATE TABLE #ChargeDetails (Col1 int IDENTITY(1,1), InvoiceNum int, TotalFee decimal(14,2))

INSERT INTO #ChargeDetails

SELECT 26193, 200 UNION ALL

SELECT 26193, 30 UNION ALL

SELECT 29229, 160 UNION ALL

SELECT 29229, 30 UNION ALL

SELECT 29229, 20 UNION ALL

SELECT 29229, 30 UNION ALL

SELECT 29289,130 UNION ALL

SELECT 31522, 450

CREATE TABLE #Payments (COl1 int IDENTITY(1,1), InvoiceNum int, PayAmt decimal(14,2))

INSERT INTO #Payments

SELECT 26193, 10 UNION ALL

SELECT 26193,125 UNION ALL

SELECT 26193,30 UNION ALL

SELECT 29229,10

SELECT a.InvoiceNum, (SELECT SUM(TotalFee) FROM #ChargeDetails b WHERE a.InvoiceNum = b.InvoiceNum GROUP BY a.InvoiceNum),

(SELECT SUM(PayAmt) FROM #Payments c WHERE c.InvoiceNum = a.InvoiceNum GROUP BY c.InvoiceNum)

FROM #ChargeDetails a

GROUP BY a.InvoiceNum

DROP TABLE #ChargeDetails

DROP TABLE #Payments

You have to take it from here - the difference of Col 2 and Col 3 should give you the result.