Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmitchell76
Starting Member
1 Post |
 Posted - 2009-12-19 : 22:27:37
|
| I have 2 tables:[dbo].[tblFees]( [FeeID] [int] IDENTITY(1,1) NOT NULL, [ClientID] [int] NULL, [FeeDate] [datetime] NULL, [DeptNo] [nvarchar](50) NULL, [ServiceType] [nvarchar](50) NULL, [Service] [nvarchar](50) NULL, [Location] [nvarchar](50) NULL, [FeeAmount] [money] NULL, [FeeAdj] [money] NULL, [TransNo] [nvarchar](50) NULL, [PostedBy] [nvarchar](100) NULL, [Comments] [nvarchar](max) NULL,[dbo].[tblPayments]( [PayID] [int] IDENTITY(1,1) NOT NULL, [FeeID] [int] NULL, [PayDate] [datetime] NULL, [PayType] [nvarchar](50) NULL, [PayAmount] [money] NULL, [ReceiptNo] [nvarchar](50) NULL, [TransNo] [nvarchar](50) NULL, [ReceivedBy] [nvarchar](100) NULLI need to create a query that sums the following columns: Fees, Payments, FeeAdj. Then I need to calculate the balance like: Fees - Payments + FeeAdj = Balance. I also Group By the Service field because thats the way I need it for the report.I do fine up until I add the Payments table to the query then it produces way too many rows and the calculations are way higher than they should be. I know it has something to do with the join but I'm not good enough at this to figure it out. Also there are records in the Fees table that do not yet have a related record in the Payments table so that has to be accounted for somehow. Any help would be grealy appreciated. |
|
|
|
|
|
|
|
Trouble with query calculation