Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more: , +
I want to make the data at one plae from two different table in one query i have to make the use of pivot and in the other query i want to sum up the total of the bill of thr same customer name which is coming from table 1

table 1 Query

SQL
select Cname,Advance,Bill,Refund from 
(select cname,r.Amount,R.PaymentType from ReceiptDetails  r 
)as sourcetable 
 Pivot(sum(Amount)for PaymentType in([Advance],[Bill],[Refund])) as PivotTable 


Table 2 Query

SQL
select Cname,sum(cb.totalandvat) from Customer_Bill



Cname is common field in both the table

Basially i want to generate the dues list of the customers so from table one(Recipt Details) Name,(Sum of) Advance and other payment they have made and from the second table (Bill Details) The sum of Total amount from the multiple bills
Posted
Updated 6-Jan-16 17:29pm
v2
Comments
dan!sh 6-Jan-16 23:33pm    
From what I understand, you need to know if there is any pending payment for any customer here. To calculate dues, you will be doing sum(totalandvat) - sum(advance) - sum(bill)- sum(refund) for each customer. If this is positive value, there is some payment due. Is that correct? Also, do you really need pivot?
Herman<T>.Instance 7-Jan-16 4:37am    
Is it about having a Total Row added (SUM()) ?
In that case: Look here

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900