CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sobhan
Sobhan

Reputation: 816

Cumulative sum across two tables

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?

Upvotes: 1

Views: 1218

Answers (4)

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

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;

Upvotes: 0

Transformer
Transformer

Reputation: 389

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]

Upvotes: 4

Ajay2707
Ajay2707

Reputation: 5808

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

Upvotes: 0

Justjyde
Justjyde

Reputation: 322

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.

Upvotes: 0

Related Questions