Click here to Skip to main content
15,354,956 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have 3 table

customer:
cid(PK),custid,company,product,productdate,total,note

paymentData:
pid(PK),pcid(FK),custid,cleint,total,receiveamt,restamt,
PaymentMode,BankName,BranchName,ChequeNo,ReceiveDate,ReceiveAmt,
DueDate,DueAmt

Payment:
id(PK),pid(FK),custid,product,receiveamt,restamt


1)Create Maintenance in Customer table
2)they every transaction store in paymentdata table
paymentdata can have many record of 1 product.
3) finally computed record store in payment table one time of every customers,
payment have only 1 record of each one product.

i want record only one time ,
ClientID,Company,Product,ProducttDate,PaymentMode,BankName,BranchName,ChequeNo,ReceiveDate,ReceiveAmt,DueDate,DueAmt,

i try...
SQL
SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total ,SUM(PM.reciveamt) as reciveamt,SUM(PM.restamt) as restamt,
PD.paymentmode,PD.bankname,PD.branchname,PD.chequeno,PD.rcvdate,PD.rcvamt,PD.nxtdate,PD.dueamt,PD.productname
FROM
    customermaintenance AS CM
    LEFT OUTER JOIN paymentdata AS PD ON PD.pcid=CM.cid
    LEFT OUTER JOIN payment AS PM ON PD.pid=PM.pid
GROUP BY CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total
        ,PD.paymentmode,PD.bankname,PD.branchname,PD.chequeno,PD.rcvdate,PD.rcvamt,PD.nxtdate,PD.dueamt,PD.productname


But it retrieve more then one data of product(one from customer one of paymentdata), i hope u understand.
Posted
Updated 27-Feb-15 0:35am
v2
Comments
Magic Wonder 27-Feb-15 1:06am
   
I think its again a re-post.
CHill60 27-Feb-15 6:30am
   
Not quite but very very close!
CHill60 27-Feb-15 6:32am
   
Please take care with the titles of your questions as I and others immediately thought this was a repost of your earlier questions.
I might help if you referenced your earlier questions and explained that you have moved on from the original problem.
As it is, this one is incredibly similar to your earlier problem which has already been answered. If you have a problem with someone's solution then you can use the "Have a Question or Comment" link next to their post to challenge the solution and/or get them to fix any issues with it.
CHill60 27-Feb-15 6:37am
   
Can you give us 3 or 4 rows of sample data for each table for one client and display your expected results for that data.
Karmesh_Madhavi 28-Feb-15 3:35am
   
ok here is Table,its same as above
Collection:
id(PK) ,clientid ,company ,product ,total
1 2001 Company1 Product1 50000
2 2002 Company2 Product2 60000
3 2003 Company3 Product3 70000

PaymentData:
wid(PK) wcid(FK) clientid product rcvamt restamt
1 1 2001 Product1 500 49500
2 1 2001 Product1 800 48700
3 2 2002 Product2 1500 58500

Payment
id(PK) wid(FK) clientid product rcvamt restamt
1 1 2001 Product1 1300 48700
2 3 2002 Product2 1500 58500


i want to shaw report like
clientid company procudt total rcvamt restamt
2001 Company1 Product1 50000 1300 48700
2002 Company2 Product2 60000 1500 58500
2003 Company3 Product3 70000

1 solution

I am guessing that you have multiple paymentData's per customer. If that is the case, you will need a subquery to select which paymentData line should be used for a particular customer.

Without seeing your data, it is hard to know.
   
Comments
Karmesh_Madhavi 28-Feb-15 3:38am
   
can u help.
ok here is Table,its same as above
>payment table is computed table and its have only one product data
>rcvamt and restamt get from payment table
Collection:
id(PK) ,clientid ,company ,product ,total
1 2001 Company1 Product1 50000
2 2002 Company2 Product2 60000
3 2003 Company3 Product3 70000

PaymentData:
wid(PK) wcid(FK) clientid product rcvamt restamt
1 1 2001 Product1 500 49500
2 1 2001 Product1 800 48700
3 2 2002 Product2 1500 58500

Payment
id(PK) wid(FK) clientid product rcvamt restamt
1 1 2001 Product1 1300 48700
2 3 2002 Product2 1500 58500


i want to shaw report like
clientid company procudt total rcvamt restamt
2001 Company1 Product1 50000 1300 48700
2002 Company2 Product2 60000 1500 58500
2003 Company3 Product3 70000
dbrenth 2-Mar-15 13:57pm
   
Given this data you can do this:

select c.clientid, company, c.product, total, rcvamt, restamt
from DBHTestCustomer c left outer join
DBHTestPayment p on c.clientid = p.clientid

or, if you want to calculated it in the SQL, this:

select c.clientid, company, c.product, total, sum(rcvamt), min(restamt)
from Customer c left outer join
PaymentData p on c.clientid = p.clientid
group by c.clientid, company, c.product, total

Either one turns the above given data into your report.

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