The issue I am having has to do with not being able to show different OrderDate for the same Unit
Unit OrderDate ShipDate ReceiptDate
Robot 20160101 20160102 20160103
Robot 20160102 20160103 20160104
Robot 20160103 20160104 20160105 What is happening instead is that it is showing the same OrderDate for all three records.
Unit OrderDate ShipDate ReceiptDate
Robot 20160101 20160102 20160103
Robot 20160101 20160103 20160104
Robot 20160101 20160104 20160105
select salesorder.unitid "Unit",max(salesorder.eventdate) "OrderDate",max(salesdelivery.eventdate) "ShipDate",max(salesreceipt.eventdate) "ReceiptDate"
from (select eventdate,unitid from sales where event='ORDER' and eventdate>'2015-12-31' and eventdate<'2016-02-01') salesorder
left outer join (select eventdate,unitid from sales where event='SHIP' and eventdate>'2015-12-31' and eventdate<'2016-02-01') salesdelivery
on salesorder.unitid=salesdelivery.unitid
left outer join (select eventdate,unitid from sales where event='RECEIPT' and eventdate>'2015-12-31' and eventdate<'2016-02-01') salesreceipt
on salesdelivery.unitid=salesreceipt.unitid
where salesdelivery.eventdate>salesorder.eventdate
and salesreceipt.eventdate>salesdelivery.eventdate
and salesorder.unitid='001'
group by salesorder.unitid