Summarize Table Based on Two Date Fields

Question

I have a table that, in its simplified form, has two date fields and an amount field. One of the date fields is holds the order date, and one of the fields contains the shipped date. I've been asked to report on both the amounts ordered and shipped grouped by date.

I used a self join that seemed to be working fine, except I found that it doesn't work on dates where no new orders were taken, but orders were shipped. I'd appreciate any help figuring out how best to solve the problem. (See below)

Order_Date    Shipped_Date    Amount
6/1/2015      6/2/2015        10
6/1/2015      6/3/2015        15
6/2/2015      6/3/2015        17

The T-SQL statement I'm using is as follows:

select a.ddate, a.soldamt, b.shippedamt 
    from 
        (select order_date as ddate, sum(amount) as soldamt from TABLE group by order_date) a  
    left join 
        (select shipped_date as ddate, sum(amount) as shippedamt from TABLE group by shipped_date) b
    on a.order_date = b.shipped_date

This results in:

ddate     soldamt    shippedamt
6/1/2015  15         0
6/2/2015  17         10

The amount shipped on 6/3/2015 doesn't appear, obviously because there are no new orders on that date.

It's important to note this is being done in a Visual FoxPro table using T-SQL syntax, so some of the features found in more popular databases do not exist (for example, PIVOT)

Answer 1

The simplest change would be to use a FULL OUTER JOIN instead of LEFT. A full join combines both right and left joins including unmatched records in both directions.

SELECT a.ddate, a.soldamt, b.shippedamt 
FROM 
    (select order_date as ddate, sum(amount) as soldamt from TABLE group by order_date) a  
FULL OUTER JOIN 
    (select shipped_date as ddate, sum(amount) as shippedamt from TABLE group by shipped_date) b
ON a.order_date = b.shipped_date

Answer 2

An other method (besides full outer join) is to use union all and an additional aggregation:

select ddate, sum(soldamt) as soldamt, sum(shippedamt) as shippedamt
from ((select order_date as ddate, sum(amount) as soldamt, 0 as shippedamt
       from TABLE
       group by order_date
      ) union all 
      (select shipped_date as ddate, 0, sum(amount) as shippedamt
       from TABLE
       group by shipped_date
      )
     ) os
group by ddate;

This also results in fewer NULL values.