SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

unibrands1
Starting Member

Pakistan
1 Posts

Posted - 02/08/2013 :  03:00:37  Show Profile  Reply with Quote
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
#

Edited by - unibrands1 on 02/08/2013 04:33:19

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/08/2013 :  03:31:27  Show Profile  Reply with Quote

;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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Edited by - visakh16 on 02/08/2013 05:15:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000