Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: I need SQL query for Crystal Report Post Reply Post New Topic
Author Message
qasimali84
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: Pakistan
Online Status: Offline
Posts: 27
Quote qasimali84 Replybullet Topic: I need SQL query for Crystal Report
     Posted: 23 Dec 2008 at 1:27am
I have Five Tables

1. Bill (BillId, CustomerId, BillAmount, BillDate)
2. BillItems (BillId, ItemId, Rate, Quantity)
3. AdvancePayment(BillId, AdvDate, AdvAmount)
4. RecievedAmount (BillId, RecievedAmount, RecievedDate)
5. Customer(CustomerId, CustomerName, Address etc)

I Need SQL Server Query to show daily income report in following format

Customer Name   BillAmount AdvancePaid Payable PaidAmount DueAmount
1. Qasim Ali              20,000       5000              15000   10000             5000
2. Zaigham Tauqeer   25000       -                    25000   20000             5000

(Due Amount is equal to Payable - Paid Amount)


I need Sql Query in this regard please
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 23 Dec 2008 at 3:26am
Hi
Where are you getting these amounts from
AdvancePaid  Payable  PaidAmount
 
You need to join the tables
 
Select coulmns
from Customer C
Inner join Bill B on  C.customerid = B.customerid
Inner Join AdvancePayment Advp on B.BillID= ADVP.Billid
 
and so on.............
 
you need to find what are the Primary keys and Foreign keys in respective tables and see if the hold same data  values which you can join on,not necesary the columns have to be PK and FK ..... to join on and not necessary Inner Joins are the only joins you need to use ,it depends on how the data is held in respective tables......
 
cheers
Rahul


Edited by rahulwalawalkar - 23 Dec 2008 at 3:27am
IP IP Logged
qasimali84
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: Pakistan
Online Status: Offline
Posts: 27
Quote qasimali84 Replybullet Posted: 23 Dec 2008 at 3:45am
Thanks rahual
 
BillAmount: is calculated from BillItem
 Sum(Rate *Quantity) Where Bill Id=1 
AdvancePaid: is calculated from table AdvancePayment sum(AdvAmount) where BillId=1
Payable: as BillAmount- AdvancePaid
PaidAmount: is calcaluted from table RecievedAmount:
Sum(RecievedAmount) where BillId=1
DueAmount: is Calculated as Payable-PaidAmount
 
Problem is that if there is no advance paid against billId or payment recieved it return no rows.
 
if there are two advance payments made in advancePayment table then it return n *2 rows. and similarly of RecievedPayments
 
Best Regards
 
Qasim Ali
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 23 Dec 2008 at 3:53am
Hi
 
Can you email be sample data for all tables in one excel sheet,And what results you are expecting......
 
 
Problem is that if there is no advance paid against billId or payment recieved it return no rows.
 
The you need a left join on these table/s
 
if there are two advance payments made in advancePayment table then it return n *2 rows. and similarly of RecievedPayments
 
Here you need to find which is the latest payment using date field or if the payment is made in parts how do you represent it in report.
 
Cheers
Rahul
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.