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.
Author |
Topic |
unibrands1
Starting Member
1 Post |
Posted - 2013-02-08 : 03:00:37
|
I have Two Tables(SI_invoiceMaster) Table1cust_Code--invcm_Date--------SaleAmount000157-----16/07/2012----------86000000157-----17/07/2012----------35784000157-----19/07/2012----------85741000157-----03/08/2012----------74574000157-----04/08/2012----------85327000157-----14/08/2012----------25874SI_ReceiptMaster Table2cust_Code--recm_date------ReceivedAmount000157-----01/08/2012---------150000000157-----14/09/2012---------240000000157-----02/11/2012----------40000how can i obtain the result like thisName---------date------SaleAmount---ReceivedAmount---Balance000157----16/07/2012-----86000------------------------86000000157----17/07/2012-----35784-----------------------121784000157----19/07/2012-----85741-----------------------207525000157----01/08/2012------------------150000----------57525 000157----03/08/2012-----74574-----------------------132099000157----04/08/2012-----85327-----------------------217426000157----14/08/2012-----25874-----------------------243300000157----14/09/2012------------------240000-----------3300000157----18/09/2012-----36984------------------------40284000157----02/11/2012-------------------40000------------284Rizwan 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, SaleAmountFROM SaleInvoiceUNION ALLSELECT Name, Date, ReceivedAmount ,CAST(0 AS int)FROM PaymentReceive)SELECT c1.Name,c1.Date,c1.ReceivedAmount,c1.SaleAmount,c2.CummAmount AS BalanceFROM CTE c1CROSS APPLY (SELECT SUM(SaleAmount)-SUM(ReceivedAmount) AS CummAmount FROM CTE WHERE Date<= c1.Date AND Name = c1.Name )c2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|