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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query HELP

Author  Topic 

unibrands1
Starting Member

1 Post

Posted - 2013-02-08 : 03:00:37
I have Two Tables
(SI_invoiceMaster) Table1

cust_Code--invcm_Date--------SaleAmount
000157-----16/07/2012----------86000
000157-----17/07/2012----------35784
000157-----19/07/2012----------85741
000157-----03/08/2012----------74574
000157-----04/08/2012----------85327
000157-----14/08/2012----------25874

SI_ReceiptMaster Table2

cust_Code--recm_date------ReceivedAmount
000157-----01/08/2012---------150000
000157-----14/09/2012---------240000
000157-----02/11/2012----------40000

how can i obtain the result like this

Name---------date------SaleAmount---ReceivedAmount---Balance
000157----16/07/2012-----86000------------------------86000
000157----17/07/2012-----35784-----------------------121784
000157----19/07/2012-----85741-----------------------207525
000157----01/08/2012------------------150000----------57525
000157----03/08/2012-----74574-----------------------132099
000157----04/08/2012-----85327-----------------------217426
000157----14/08/2012-----25874-----------------------243300
000157----14/09/2012------------------240000-----------3300
000157----18/09/2012-----36984------------------------40284
000157----02/11/2012-------------------40000------------284


Rizwan Habib
#

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 03:31:27
[code]
;With CTE
(
SELECT Name, Date,CAST(0 AS int) AS ReceivedAmount, SaleAmount
FROM SaleInvoice
UNION ALL
SELECT Name, Date, ReceivedAmount ,CAST(0 AS int)
FROM PaymentReceive
)
SELECT c1.Name,c1.Date,c1.ReceivedAmount,c1.SaleAmount,
c2.CummAmount AS Balance
FROM CTE c1
CROSS APPLY (SELECT SUM(SaleAmount)-SUM(ReceivedAmount) AS CummAmount
FROM CTE
WHERE Date<= c1.Date
AND Name = c1.Name
)c2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

- Advertisement -