I have two SQL tables one with sales order date and total for the day. Then I have another table with invoice date and total invoice for the day. On both of these tables there are some dates with no records at all.
I need your help to join these two tables and get sales order total and invoice total against each and every day as you can see in the below image.
Perhaps the easiest way would be to aggregate the results from a UNION ALL
Example
Select [Date] = OrderDate
,Sales = sum(Sales)
,Invoice = sum(Invoice)
From (
Select OrderDate
,Sales = sum(LovalValue)
,Invoice = cast(null as money)
From SalesOrd_HDR
Group By OrderDate
Union All
Select TransDate
,cast(null as money)
,sum(Amount)
From DR_Trans
WHERE TRANSTYPE = '1'
Group By TransDate
) A
Group By OrderDate
You could achieve that with two aggregate subqueries and aFULL OUTER JOIN like :
SELECT
COALESCE(ord.oday, inv.iday) AS date,
ord.ototal AS order_total,
inv.itotal AS invoice_total
FROM
(
SELECT
CAST(orderdate AS DATE) AS oday,
SUM(localvalue) AS ototal,
FROM dbo.SALESORD_HRD
GROUP BY CAST(orderdate As DATE)
) AS ord FULL OUTER JOIN (
SELECT
CAST(transdate AS DATE) iday,
SUM(amount) itotal,
FROM dbo.DR_TRANS
WHERE TRANSTYPE = '1'
GROUP BY CAST(transdate As DATE)
) AS inv
ON ord.oday = inv.iday
