Running Balance with the view

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

Open in new window

Starquest321Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
please take a look at this link
{link removed} see terms of use 6.9
I belive it will address what you are looking for creating a running total based off the previous columns I think you can incorporate this solution into your view :)
0
Starquest321Author Commented:
Perfect . . .
Since I am a little new at this can you put in the relevant code in the view?
0
Starquest321Author Commented:
I just need to output the final balance result. So this option would work best for me. How can I incorporate this into the view?

SELECT *,

    100 +
    (SELECT ISNULL(SUM(rate),0) /*we need to calculate only the sum in subquery*/
    FROM MyTbl AS a
    WHERE
        a.Code=b.Code /*the year in subquery equals the year in main query*/
        AND a.Yr<b.Yr /*main feature in our subquery*/
        ) AS base

FROM MyTbl AS b
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
NO points please

EE PAQ example with useful links

This may not be as simple as just dropping in some additional code to your existing query. Until you reach SQL Server 2012 there is no inbuilt feature for this and for earlier versions there are a variety of approaches to a running total each having different performance effects (which can be quite extreme). If you read the URL above you will see mention of RBAR which is "row by agonizing row" giving you an indication of this.

The choice of technique to apply may very well depend on how well the existing query performs (which we are unable to know). Whilst not huge that query isn't trivial, and it already involves a "group by", so I think I would suggest placing your existing query into a CTE (common table expression) and then calculate the running total from that CTE.*

* or, you could use a temp table

The query provided in the question seems to be at a lower level of detail than the question indicates, in particular those marked *** below
        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 ...                                        AS Description
***   , (CASE ...                                        AS [Check Number]
      , (CASE ...                                        AS Charge
      , (CASE ...                                        AS Payment
      , (CASE ...                                        AS Balance
***   , dbo.GLHeader.GLHeader_ID
***   , (CASE ...                                        AS GLDetail_ID
      , dbo.GLHeader.TransactionType_ID

Open in new window

Would  GLDetail_ID provide a unique id per row?
Where is "RATE"?

Although there are unknowns, a CTE approach might appear something like this
;with CTE as (
               -- your existing query here
             )
select
     cte.*
   , 100 +
        (SELECT ISNULL(SUM(rate),0) /* NOT PRESENT & no information on it*/
        FROM CTE AS a
        WHERE
            a.GLDetail_ID = cte.GLDetail_ID /* DOES THIS WORK? */
            AND a.[Date] < cte.[Date] /*IS [Date] by itself OK for this? */
            ) AS base           
from cte

Open in new window

Further to this I'd like to suggest an alteration to the syntax used to join the tables. Currenty you list the tables, then list a series of ON conditions. It is much more conventional for ANSI join syntax that the table and conditions are together.

/* AS IS: ON conditions seperated from tables */
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

/* Suggested: ON conditions next to table, and prior table listed first in each condition */        
FROM dbo.Tenant
INNER JOIN dbo.LeaseDetail
        ON dbo.Tenant.Tenant_ID = dbo.LeaseDetail.Tenant_ID
INNER JOIN dbo.GLDetail
        ON dbo.LeaseDetail.Lease_ID = dbo.GLDetail.Lease_ID
INNER JOIN dbo.ChartOfAccounts
        ON dbo.GLDetail.ChartOfAccounts_ID = dbo.ChartOfAccounts.ChartOfAccounts_ID
INNER JOIN dbo.GLHeader
        ON dbo.GLDetail.GLHeader_ID = dbo.GLHeader.GLHeader_ID
INNER JOIN dbo.Property
        ON dbo.GLDetail.Property_ID = dbo.Property.Property_ID

Open in new window


In Summary:
a. not sure if GLDetail_ID is unique for joins
b. no information available about [Rate]
c. a CTE approach might work, performance of existing query unknown, there are other options than that indicated above
d. join syntax used is unusual
0
Starquest321Author Commented:
Actually I amended an existing query to simply get the payment, charge, balance. If the query does much more than its all extra and can be removed.

Can you amend this query to simply extract that info? And then maybe the joins will not be as complex?
0
PortletPaulfreelancerCommented:
>> I amended an existing query to simply get the payment, charge, balance.
Why not post that here instead?
0
Starquest321Author Commented:
What I posted was the amended one. . . . The original was a little different but same idea.
0
PortletPaulfreelancerCommented:
You are in charge (we cannot guess what is needed), please take this list, remove ALL lines you do not NEED and post back that updated list.
        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 ...                                        AS Description
***   , (CASE ...                                        AS [Check Number]
      , (CASE ...                                        AS Charge
      , (CASE ...                                        AS Payment
      , (CASE ...                                        AS Balance
***   , dbo.GLHeader.GLHeader_ID
***   , (CASE ...                                        AS GLDetail_ID
      , dbo.GLHeader.TransactionType_ID

Open in new window

0
PortletPaulfreelancerCommented:
OH! and at ID: 39471131 you introduce something quite new:

ISNULL(SUM(rate),0)

which table does [rate]  come from? (we don't know that)

plus: How does the following relate to the original question (a running balance)?

100 +
    (SELECT ISNULL(SUM(rate),0) /*we need to calculate only the sum in subquery*/
    FROM MyTbl AS a
    WHERE
        a.Code=b.Code /*the year in subquery equals the year in main query*/
        AND a.Yr<b.Yr /*main feature in our subquery*/
        ) AS base


is this an additional column?
[running balance] , [this rate calculation]
0
Starquest321Author Commented:
Id 39471131 was the EXAMPLE code that came from the link that got declined due to some viloation by the admin comment. In that link they showed example code of how to achieve what I wanted. So I posted that code to try to merge it in the right way with my view.

So ID 39471131  is just example code and is unrelated to my view posted.


In relation to your previous question I don't need:

***   , dbo.GLHeader.GLHeader_ID
***   , (CASE ...                                        AS GLDetail_ID
      , dbo.GLHeader.TransactionType_ID

I really just need:

       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 ...                                        AS Description
***   , (CASE ...                                        AS [Check Number]
      , (CASE ...                                        AS Charge
      , (CASE ...                                        AS Payment
      , (CASE ...                                        AS Balance
0
PortletPaulfreelancerCommented:
Please confirm that this operates and has the required fields etc.
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
/* Suggested Changes: ON conditions next to table, and prior table listed first in each condition */        
FROM dbo.Tenant
INNER JOIN dbo.LeaseDetail
        ON dbo.Tenant.Tenant_ID = dbo.LeaseDetail.Tenant_ID
INNER JOIN dbo.GLDetail
        ON dbo.LeaseDetail.Lease_ID = dbo.GLDetail.Lease_ID
INNER JOIN dbo.ChartOfAccounts
        ON dbo.GLDetail.ChartOfAccounts_ID = dbo.ChartOfAccounts.ChartOfAccounts_ID
INNER JOIN dbo.GLHeader
        ON dbo.GLDetail.GLHeader_ID = dbo.GLHeader.GLHeader_ID
INNER JOIN dbo.Property
        ON dbo.GLDetail.Property_ID = dbo.Property.Property_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)

Open in new window

0
Starquest321Author Commented:
Confirmed
0
PortletPaulfreelancerCommented:
OK, this is just one of several possible approaches - but please remember I cannot do any testing. This approach places the confirmed query into a common table expression (CTE) so that the complexities of the case expressions and the where clause are undertaken just once. Then the running total is calculated by reusing that CTE via a correlated subquery & I have opted to include that in a CROSS APPLY.

Now I really do not know exactly how the data "correlates" so I'm assuming the [Tenant ID1] will be sufficient for this. Anyway try this out:
;WITH
CTE AS (
            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   
            FROM dbo.Tenant
            INNER JOIN dbo.LeaseDetail
                    ON dbo.Tenant.Tenant_ID = dbo.LeaseDetail.Tenant_ID
            INNER JOIN dbo.GLDetail
                    ON dbo.LeaseDetail.Lease_ID = dbo.GLDetail.Lease_ID
            INNER JOIN dbo.ChartOfAccounts
                    ON dbo.GLDetail.ChartOfAccounts_ID = dbo.ChartOfAccounts.ChartOfAccounts_ID
            INNER JOIN dbo.GLHeader
                    ON dbo.GLDetail.GLHeader_ID = dbo.GLHeader.GLHeader_ID
            INNER JOIN dbo.Property
                    ON dbo.GLDetail.Property_ID = dbo.Property.Property_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)
        )
SELECT
      [Tenant ID1]
    , [Tenant Name]
    , [Date]
    , [Account Number]
    , Description
    , [Check Number]
    , Charge
    , Payment
    , Balance 
    , ca1.[Running Balance]
FROM CTE
CROSS APPLY (
             SELECT SUM(Balance)
             FROM CTE AS CTE2
             WHERE CTE.[Account Number] = CTE2.[Account Number]
             AND CTE.[Tenant ID1] = CTE2.[Tenant ID1]
             AND CTE2.[Date] <= CTE.[Date]
            ) AS CA1 ([Running Balance])
ORDER BY
      [Tenant ID1]
    , [Date] ASC
 

Open in new window

Regarding performance and options. I would recommend you read this article by  Aaron Bertrand - if for no other reason than to look at a performance graph just above the conclusion.

By the way, if you are using a reporting tool such as Crystal Reports, those tools have easier running total features than is available in SQL 2005/8.

Please let me know:
a. if it works
b. is acceptable performance

ps: I've done my best to avoid typos and so on, if there are errors do please try to work those out - but if stuck come back here.

Note if you make ANY changes and get errors I will not know which query you are using so  you may have to re-post the modified query here too.
0
Starquest321Author Commented:
Wow - Really appreciated.
I get an incorrect syntax near ';'

When I try to create that view. I copied and pasted exactly as written.
0
PortletPaulfreelancerCommented:
There is only one semi-colon in what I provided, the very first character, do you have any "stuff" above that?

Did you try running the query with no changes at all?

>>When I try to create that view
When you say "create view" that indicates you may have added something: ANY change you make (no matter how small) is something I know nothing about.

& you may have to debug some stuff as I cannot run the query... only you can (and I will be in bed in a few minutes by the way).
0
PortletPaulfreelancerCommented:
you could try removing the ';' but usually this is required in front of WITH
0
PortletPaulfreelancerCommented:
Hi, any progress? Anyway I thought I would try to build something that mimics the approach and check my logic; seems my calculation of running balance my be wrong, and I need you to verify the correct logic.

In your question you start with a running balance of -100, but it jumps to 90 and remains positive. Looks to me like the running balance should all be negative numbers like this result:
[TENANT ID1][ACCOUNT NUMBER]  DATE      CHARGE PAYMENT  BALANCE [RUNNING BALANCE]
1234         act10          2013-01-01    100    0      100      -100
1234         act10          2013-02-01    0      10     -10      -90
1234         act10          2013-03-01    50     0      -50      -140
1234         act10          2013-04-01    0      10     -10      -130

Open in new window

The code that produced this is:
;WITH
 CTE AS (
            SELECT 1234 AS [Tenant ID1], 'act10' as [Account Number], cast('2013-01-01' as date) as [Date] ,100 AS charge, 0 AS payment,100 AS balance UNION ALL
            SELECT 1234 AS [Tenant ID1], 'act10' as [Account Number], cast('2013-02-01' as date) as [Date]  ,0 , 10,-10 UNION ALL
            SELECT 1234 AS [Tenant ID1], 'act10' as [Account Number], cast('2013-03-01' as date) as [Date]  ,50 , 0,-50 UNION ALL
            SELECT 1234 AS [Tenant ID1], 'act10' as [Account Number], cast('2013-04-01' as date) as [Date]  ,0 , 10,-10 
        )
SELECT
*
FROM CTE
CROSS APPLY (
             SELECT SUM(Payment) - SUM(Charge) --<< NOTE THE CHANGE HERE!!
             FROM CTE AS CTE2
             WHERE CTE.[Account Number] = CTE2.[Account Number]
             AND CTE.[Tenant ID1] = CTE2.[Tenant ID1]
             AND CTE2.[Date] <= CTE.[Date]
            ) AS CA1 ([Running Balance])
ORDER BY
      [Tenant ID1]
    , [Date] ASC

Open in new window

this can be seen operating at: http://sqlfiddle.com/#!3/1fa93/9080

Summary:
> I think the running balance calculation I provided earlier should be changed (see above)
> if you could provide me with a small sample (for just 2 tenant id's perhaps) of the data (from the confirmed query) I could mock-up a fiddle to check the sql operates as expected
> please confirm that the running balance shown above (all negatives) is correct
if it is not correct please discuss the "rules" that apply to the calculation.
0
PortletPaulfreelancerCommented:
by the way, query at ID: 39473255 is the 'confirmed query' and it is this output I need
& if providing a sample of data please change any 'private' information to nonsense but leave ID's and codes "as is".

(Ideally this data would be something I can easily get into a table e.g. Excel, CSV)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starquest321Author Commented:
I looked at the SQL fiddle. You are very close and I thank you so much.
Here is what we need:

1. I don't need two columns: Balance + Running Balance ---> Just the running balance.
2. Our convention here is that positive numbers are CHarges, Negative numbers are credits.
3. Here is some small data:

                                                                                  Charge  Payment Balance

06/03/2013       Edwin 14672345163631                 0       265       -265
07/01/2013       Edwin July Lot Rent                              265       0       265
07/02/2013       Edwin 14708823444106                0       265       -265
08/01/2013       Edwin August Lot Rent                      265       0       265
08/05/2013       Edwin 14702348924688                     0       265       -265
09/01/2013       Edwin September Lot Rent               265       0       265
0
Starquest321Author Commented:
To achieve point 2 above I changed:

     SELECT SUM(charge) - SUM(payment)

That got the signs working for me.
0
PortletPaulfreelancerCommented:
:(
I cannot use that sample - it does not provide ALL the columns of the confirmed query*
I also cannot tell where a column starts or finishes
There are no column headings
I need more than one "set" of rows (so I can ensure the logic is correct)
Excel or CSV perhaps?
I assume your dates are MM/DD/YYYY : is that correct?

If you paste data into a comment here, it would need to have tab delimiters like this which is direct from Excel (& don't edit it to align columns) :
ID	ACCT-NO	TRANS_DATE	TRANS_VALUE
1	123-01	2013-04-01	100.00
2	123-01	2013-05-02	200.00
3	123-01	2013-06-05	300.00
4	123-01	2013-07-10	400.00
5	123-01	2013-08-21	500.00

Open in new window

or: you can use sqlfiddle (look for "Text to DDL" in the toolbar)

* To really help you I need to "see" how the correlated subquery can reliably grab the information it needs.

ps: I don't need two columns:
so, don't include Balance in the selection list of the last select (after the CTE)
This is stuff you can do :)

note, if I use "select *" that does not mean you should, I do this for brevity only.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.