Cumulative sum across two tables

Question

I have two tables for Bill & Payment. I have show the balance sheet from these two tables.

The Data in the tables are:

tblBill

enter image description here

tblPayment

enter image description here

My current output is:

enter image description here

The query I'm trying to use is:

select Particulars,Date,BillAmount,0'PaidAmount' from tblBill
union
select Particulars,Date,0'BillAmount',PaidAmount from tblPayment
order by Date

However, I need my output in this format:

enter image description here

Is it possible to get the required format?

If I have more time lately I'll write a proper query for you, but for now, you can check this answer for SQL Server 2008 - http://stackoverflow.com/a/13744550/1744834 — Roman Pekar, Jun 27 '14 at 08:30
Isn't that running total a job for the reporting tool, rather for the DB engine ? — iDevlop, Jun 27 '14 at 08:33

Transformer · Accepted Answer · 2014-06-27T10:17:39.850

There you go: Assuming there is only one transaction in a day....

With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[date] >= T2.[date]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

In case of more than one transactions in a day....

WITH Tb0 as
( SELECT [Date],Particulars,BillAmount,0'PaidAmount' from tblBill
        UNION
        SELECT [Date],Particulars,0'BillAmount',PaidAmount from tblPayment
)

, Tb1 as
( 
    SELECT Date,Particulars,BillAmount,PaidAmount,Row_Number() over (order by [Date] asc) as [OrderId]
        FROM    
            Tb0
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[OrderId] >= T2.[OrderId]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

Justjyde · Answer 2 · 2014-06-27T09:09:34.707

You will need to JOIN the two tables. First, there must be a link between the two tables (say customerid existing to show which customer is involved).

Then, you can do.

CREATE VIEW vwTransactionHistory as 
SELECT customerid, Particulars, [DATE], BillAmount, PaidAmount, 
    (SELECT SUM(BillAmount) FROM tblBill x WHERE x.customerid=temp1.customerid and x.date<=temp1.date) as bill2date, (SELECT SUM(PaidAmount) FROM tblPayment y WHERE y.customerid = temp1.customerid and y.date<=temp1.date) as Pay2Date
FROM
(
    select customerid, Particulars,[Date],BillAmount,0 AS 'PaidAmount' from tblBill
    union
    select customerid,Particulars,[Date],0 AS 'BillAmount',PaidAmount from tblPayment
) AS temp1
GROUP BY customerid, Particulars,[Date],BillAmount,PaidAmount

Then you can do

    SELECT TOP 1000 [customerid]
      ,[Particulars]
      ,[DATE]
      ,[BillAmount],[PaidAmount], isnull(bill2date,0) - isnull(pay2date,0) as Balance  
FROM [vwTransactionHistory]

Remember that you don't need to create a View. I use views for clarity and abstraction of complex Queries.

score 0 · Answer 3 · edited Aug 12 '15 at 01:27

0

You can use this query

SELECT Particulars,Date,BillAmount,PaidAmount,BillAmount-PaidAmount as Balance 
FROM(
select Particulars,Date,BillAmount,0'PaidAmount' from tblBill
union
select Particulars,Date,0'BillAmount',PaidAmount from tblPayment
order by Date
)
ORDER BY Date;

edited Aug 12 '15 at 01:27

Manisha

25
3

answered Jun 27 '14 at 08:23

Nebojsa Susic

1,220
1
9
12

its not showing the cumulative sum, but just showing the corrosponding result – Sobhan Jun 27 '14 at 08:29
Yes I miss the point:( Maybe you can write stored procedure. I am not too familiar with sql server but some pseudo code can be as follow. var balance = 0; for record in SELECT Particulars,Date,BillAmount,PaidAmount,BillAmount-PaidAmount as Balance FROM( select Particulars,Date,BillAmount,0'PaidAmount' from tblBill union select Particulars,Date,0'BillAmount',PaidAmount from tblPayment order by Date ) ORDER BY Date loop balance = balance + record.balance; recored.balance = balance; end loop; – Nebojsa Susic Jun 27 '14 at 09:26

score 0 · Answer 4 · answered Jun 27 '14 at 09:13

Check this query

select * from 
(
select Particulars,Date,BillAmount,0'PaidAmount' , BillAmount as Balance from tblBill
union
select Particulars,Date,0'BillAmount',PaidAmount, BillAmount - PaidAmount as Balance
 from tblPayment p
inner join tblBill b where p.Particulars = p.Particulars
) a
order by Date

Cumulative sum across two tables

4 Answers4