Kedar Pawgi

Kedar Pawgi

  • 1.6k
  • 136
  • 63.1k

cannot guess where is the problem, either in sql statement or crystal report

Mar 1 2012 8:54 AM
i have question regarding SQL, i am using Crystal report and c#2008.
I am building a report which has two table one is order and other is payment. What i want is :--its a small software for a building contractor, he has couple of building site where few supplier give material, what i want is a Ledger report left side should be the supplier order for perticular supplier 'A'  for that site with in the perticular date range and one right side i want is the payment made by my client with in that perticular date range for that site for that perticular supplier 'A' and for that perticular site.
the query i made is as follows :--

 SELECT `order`.`orderBillNo`, `order`.`siteName`, `order`.`orderDt`, `order`.`vendorName`, `order`.`totalAmt`, `vendorpayments`.`InvoiceNo`, `vendorpayments`.`Date`, `vendorpayments`.`Paymode`, `vendorpayments`.`amt`, `vendorpayments`.`Chqddno`, `vendorpayments`.`bankname`
 FROM  `order` ,  `vendorpayments`
where `order`.`vendorName`='A'
AND `vendorpayments`.`vendor` ='A'
AND `order`.`sitename`='B'
AND `vendorpayments`.`sitename`='B'
AND (`order`.`orderDt`>="2012/02/01" and `order`.`orderDt`<="2012/02/25")
AND (`vendorpayments`.`date`>="2012/02/01" and `vendorpayments`.`date`<="2012/02/25")

i am using ADODB recordset, and using crviewer.setdatasource(adoRS) for showing report to CR. i m using CR8

If i run using this query then report is showing proper result but when i do the sum of the order.totalamout & vendorpayments.amt then it shows sum with suppressed rows as well this is perticular query the order.totalamount should be 7,500 but its showing 30,000. and for vendorpayment it should show two rows with pay of 1,500 and 500 and total should be 2000, but its showing 4 rows and total of 4000,it its taking order.totamt also 4 times so i m getting 30000. I have used the suppressed option but still its not showing correct amout. I want help about how to remove this extra sum. Does there is problem with my query , does my query is giving more more iterations so 4 rows are coming. If any can correct my query iwill be thankful.Its very urgent
thanks in advance.

Answers (2)