sql server - SQL - Join multiple table -

sql server - SQL - Join multiple table -

i have 4 tables customer, sales, invoice, , receipt.

customer id name 1 sales id name 1 ben invoice id amt date customerid salesid 1 12 1/9/2014 1 1 2 10 1/10/2014 1 1 3 20 2/10/2014 1 1 4 30 3/10/2014 1 1 receipt id amt date customerid salesid 1 10 4/10/2014 1 1

i wish bring together 4 table below sum ammount(s), stuck how can accomplish desired

result customerid salesid inv_amt rep_amt month 1 1 12 0 9 1 1 60 10 10

i've been stuck days. but, have no thought how proceed.

you can month wise total receipt , invoice amount grouping , sub query below :

select invoice.customerid [customerid], invoice.salesid [salesid], sum(invoice.amt) [invoice_amt], isnull((select sum(amt) receipt customerid = invoice.customerid , salesid = invoice.salesid , month(date) = month(invoice.date)),0) [receipt_amt], month(invoice.date) month invoice grouping invoice.customerid, invoice.salesid, month(invoice.date)

sql fiddle demo1

warning : here info come months in invoice table. if month, there no info in invoice table no result come month receipt also.

update: result months of invoice , receipt table, need using cte below :

;with cte ( select invoice.customerid, invoice.salesid, month(invoice.date) monthno invoice union select receipt.customerid, receipt.salesid, month(receipt.date) monthno receipt ) select cte.customerid [customerid], cte.salesid [salesid], isnull((select sum(amt) invoice customerid = cte.customerid , salesid = cte.salesid , month(date) = cte.monthno),0) [invoice_amt], isnull((select sum(amt) receipt customerid = cte.customerid , salesid = cte.salesid , month(date) = cte.monthno),0) [receipt_amt], monthno cte

sql fiddle demo2

sql sql-server join