CODEWITHSUNDEEP
sql-server
Nakibuz Zaman
Nakibuz Zaman

Reputation: 21

Matching two or more column value and display in a single column in SQL server

I have two table in SQL Server. tbl_Invoice as consider bellow

PayDate ---- Amount
2017-08-23 | 2300
2017-08-23 | 2400
2017-08-23 | 2500
2017-08-24 | 5000

second Table tbl_Expense as consider bellow

ExpDate ---- ExpAmount
2017-08-23 | 1700
2017-08-25 | 2800
2017-08-25 | 2900

I want to join this two table and display the sum of receipt amount and expense amount of date. I want the output to be displayed as bellow

Date ------- Amount --- ExpAmount
2017-08-23 | 7200 | 1700
2017-08-24 | 5000 | 0
2017-08-25 | 0 |5700

Upvotes: 0

Views: 62

Answers (4)

Pop Catalin
Pop Catalin

Reputation: 62960

One way to do it is to get all the dates you're interested and use CROSS APPLY to get the sum of values for each date:

SELECT 
    Dates.Date, 
    ISNULL(Invoices.Amount, 0)    AS Amount, 
    ISNULL(Expenses.ExpAmount, 0) AS ExpAmount
FROM (SELECT ExpDate As [Date] FROM tbl_Expense UNION SELECT PayDate FROM tbl_Invoice) AS Dates
CROSS APPLY (
      SELECT SUM(Amount) as Amount FROM tbl_Invoice WHERE PayDate = [Date]) AS Invoices
CROSS APPLY (
      SELECT SUM(ExAmount) as ExpAmount FROM tbl_Expense WHERE ExpDate = [Date]) AS Expenses

Upvotes: 0

Anagha
Anagha

Reputation: 918

By simplest way try this

select  date , sum(Amount) as Amount ,sum(ExpAmount) as ExpAmount from 
(select PayDate as date , Amount, 0 as ExpAmount from tbl_Invoice
union 
select ExpDate as date,0 as Amount, ExpAmount from tbl_Expense ) as a 
group by date

Output

date                    Amount  ExpAmount
2017-08-23 00:00:00.000 7200    1700
2017-08-24 00:00:00.000 5000    0
2017-08-25 00:00:00.000 0       5700

Upvotes: 1

faibistes
faibistes

Reputation: 135

WITH suminv AS
  (SELECT paydate,sum(amount) AS amount FROM tbl_invoice group by paydate),
 sumexp as 
  (SELECT expdate,sum(expamount) AS expamount FROM tbl_expense group by expdate)
SELECT 
  coalesce(expdate,paydate) AS date,
  coalesce(amount,0),
  coalesce(expamount,0)
   FROM sumexp full outer join suminv
    on paydate=expdate

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

A full outer joins between the two tables would bring you close to the output you want. I say close because this approach still suffers from the problem of a given date not appearing in either table. In such a case, this date would simply not appear in your results. To get around this, one option would be to join to a calendar table containing all dates. Note that I aggregate both tables by date to get the total amounts.

SELECT
    COALESCE(t1.PayDate, t2.ExpDate) AS Date,
    COALESCE(t1.Amount, 0)           AS Amount,
    COALESCE(t2.ExpAmount, 0)        AS ExpAmount
FROM
(
    SELECT PayDate, SUM(Amount) AS Amount
    FROM tbl_Invoice
    GROUP BY PayDate
) t1
FULL OUTER JOIN
(
    SELECT ExpDate, SUM(ExpAmount) AS ExpAmount
    FROM tbl_Expense
    GROUP BY ExpDate
) t2
    ON t1.PayDate = t2.ExpDate

Demo here:

Rextester

Upvotes: 2

Related Questions