troubleshooting Question

Running Balance with the view

Avatar of Starquest321
Starquest321 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
21 Comments1 Solution385 ViewsLast Modified:
I have the following structure:

Charge|Payment|Balance

All coming from the database. But I need to create a running balance. . . now what I have is a line item balance:

Charge: 100| Payment: 0 | Balance: 100
Charge: 0 | Payment: 10| Balance: -10
Charge: 50 | Payment: 0| Balance: -50
Charge: 0 | Payment: 10| Balance: -10

I need the running balance for these entries to appear. So:
Charge: 100| Payment: 0 | Balance: 100 | Running: -100
Charge: 0 | Payment: 10| Balance: -10 | Running: 90
Charge: 50 | Payment: 0| Balance: -50| Running: 140
Charge: 0 | Payment: 10| Balance: -10 | Running: 130



I need to create a running balance for the following view:

SELECT     dbo.LeaseDetail.Tenant_ID, dbo.Tenant.ID AS [Tenant ID1], dbo.Tenant.FirstName + ' ' + dbo.Tenant.LastName AS [Tenant Name], 
                      dbo.GLDetail.Lease_ID, dbo.GLHeader.TransactionDate AS Date, dbo.ChartOfAccounts.AccountNumber AS [Account Number], 
                      (CASE WHEN GLHeader.TransactionType_ID = 2 AND GLHeader.SubLedgerSource = 'MI' THEN GLHeader.Description ELSE GLDetail.Description END) 
                      AS Description, (CASE WHEN GLHeader.TransactionType_ID <> 1 THEN GLHeader.SubLedgerReference END) AS [Check Number], 
                      (CASE WHEN GLHeader.TransactionType_ID <> 2 AND 
                      GLHeader.TransactionType_ID = 15 THEN amount ELSE (CASE WHEN GLHeader.TransactionType_ID <> 2 AND 
                      GLHeader.TransactionType_ID <> 15 THEN - [amount] ELSE 0 END) END) AS Charge, (CASE WHEN GLHeader.TransactionType_ID = 2 OR
                      GLHeader.TransactionType_ID = 15 THEN SUM([amount]) ELSE 0 END) AS Payment, (CASE WHEN GLHeader.TransactionType_ID <> 2 AND 
                      GLHeader.TransactionType_ID = 15 THEN amount ELSE (CASE WHEN GLHeader.TransactionType_ID <> 2 AND 
                      GLHeader.TransactionType_ID <> 15 THEN - [amount] ELSE 0 END) END) - (CASE WHEN GLHeader.TransactionType_ID = 2 OR
                      GLHeader.TransactionType_ID = 15 THEN SUM([amount]) ELSE 0 END) AS Balance, dbo.GLHeader.GLHeader_ID, 
                      (CASE WHEN TransactionType_ID = 2 THEN 0 ELSE GLDetail.GLDetail_ID END) AS GLDetail_ID, dbo.GLHeader.TransactionType_ID
FROM         dbo.Tenant INNER JOIN
                      dbo.Property INNER JOIN
                      dbo.GLHeader INNER JOIN
                      dbo.ChartOfAccounts INNER JOIN
                      dbo.GLDetail INNER JOIN
                      dbo.LeaseDetail ON dbo.GLDetail.Lease_ID = dbo.LeaseDetail.Lease_ID ON 
                      dbo.ChartOfAccounts.ChartOfAccounts_ID = dbo.GLDetail.ChartOfAccounts_ID ON dbo.GLHeader.GLHeader_ID = dbo.GLDetail.GLHeader_ID ON 
                      dbo.Property.Property_ID = dbo.GLDetail.Property_ID ON dbo.Tenant.Tenant_ID = dbo.LeaseDetail.Tenant_ID
WHERE     (dbo.GLHeader.TransactionType_ID IN (1, 2, 33)) AND (dbo.GLDetail.ApplyType = 1) AND (dbo.GLHeader.Unposted = 0) AND 
                      ((CASE WHEN ((glheader.TransactionDate >= leasedetail.moveindate) OR
                      leasedetail.responsibilityassumed != 0) THEN 1 ELSE 0 END) = 1) AND ((CASE WHEN ((glheader.TransactionDate <= leasedetail.moveoutdate) OR
                      leasedetail.moveoutdate IS NULL OR
                      (leasedetail.responsibilityreleased = 0)) THEN 1 ELSE 0 END) = 1) OR
                      (dbo.GLHeader.TransactionType_ID = 15) AND (dbo.GLDetail.ApplyType = 0) AND (dbo.GLHeader.Unposted = 0) AND 
                      ((CASE WHEN ((glheader.TransactionDate >= leasedetail.moveindate) OR
                      leasedetail.responsibilityassumed != 0) THEN 1 ELSE 0 END) = 1) AND ((CASE WHEN ((glheader.TransactionDate <= leasedetail.moveoutdate) OR
                      leasedetail.moveoutdate IS NULL) THEN 1 ELSE 0 END) = 1)
GROUP BY dbo.LeaseDetail.Tenant_ID, dbo.Tenant.ID, dbo.Tenant.FirstName + ' ' + dbo.Tenant.LastName, dbo.GLDetail.Lease_ID, 
                      dbo.GLHeader.TransactionDate, dbo.ChartOfAccounts.AccountNumber, (CASE WHEN GLHeader.TransactionType_ID = 2 AND 
                      GLHeader.SubLedgerSource = 'MI' THEN GLHeader.Description ELSE GLDetail.Description END), (CASE WHEN GLHeader.TransactionType_ID <> 2 AND
                       GLHeader.TransactionType_ID = 15 THEN amount ELSE (CASE WHEN GLHeader.TransactionType_ID <> 2 AND 
                      GLHeader.TransactionType_ID <> 15 THEN - [amount] ELSE 0 END) END), dbo.GLHeader.GLHeader_ID, 
                      (CASE WHEN TransactionType_ID = 2 THEN 0 ELSE GLDetail.GLDetail_ID END), dbo.GLHeader.TransactionType_ID, 
                      dbo.GLHeader.SubLedgerReference
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 21 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros