sql - Summarize Table Based on Two Date Fields -

i have table that, in simplified form, has 2 date fields , amount field. 1 of date fields holds order date, , 1 of fields contains shipped date. i've been asked report on both amounts ordered , shipped grouped date.

i used self join seemed working fine, except found doesn't work on dates no new orders taken, orders shipped. i'd appreciate figuring out how best solve 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 follows:

select a.ddate, a.soldamt, b.shippedamt               (select order_date ddate, sum(amount) soldamt table group order_date)       left join          (select shipped_date ddate, sum(amount) shippedamt table group 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, because there no new orders on date.

it's important note being done in visual foxpro table using t-sql syntax, of features found in more popular databases not exist (for example, pivot)

the simplest change use full outer join instead of left. full join combines both right , left joins including unmatched records in both directions.

select a.ddate, a.soldamt, b.shippedamt       (select order_date ddate, sum(amount) soldamt table group order_date)   full outer join      (select shipped_date ddate, sum(amount) shippedamt table group shipped_date) b on a.order_date = b.shipped_date