Refactoring SQL code

Question

I'm looking for suggestions on re-factoring the code shown below. You'll notice that the code identified as 'DATAPOINT2' and 'DATAPOINT3' are extremely similar and ripe for simplifying. But I'm running into a wall trying to think how I can elegantly combine 'DATAPOINT2' and 'DATAPOINT3'.

CREATE TABLE Employee (EmployeeId INTEGER, BonusPaidOn DATE, Status VARCHAR(50));

INSERT INTO Employee VALUES (1, '01-JAN-2013', 'Paid in Full');
INSERT INTO Employee VALUES (2, '01-FEB-2013', 'Paid in Full');
INSERT INTO Employee VALUES (2, '01-MAR-2013', 'Partial Payment');
INSERT INTO Employee VALUES (2, '01-APR-2013', 'Partial Payment');
INSERT INTO Employee VALUES (3, '01-APR-2013', 'Refused');
COMMIT;

WITH
DATAPOINT1 AS
  (SELECT DISTINCT
       EmployeeId
  FROM
      Employee
  )
,DATAPOINT2 AS
  (SELECT
      EmployeeId
      ,Status
      ,MAX(BonusPaidOn) AS BonusPaidOn
  FROM
      Employee
  WHERE
    Status IN ('Paid in Full','Partial Payment', 'Refused')
  GROUP BY
    EmployeeId, Status
  )
,DATAPOINT3 AS
  (SELECT
      EmployeeId
      ,MAX(BonusPaidOn) AS BonusPaidOn
  FROM  
      DATAPOINT2 temp
  WHERE  
      Status IN ('Paid in Full','Partial Payment')
  GROUP BY
      EmployeeId
  )
SELECT
    A.EmployeeId
    ,B.BonusPaidOn AS LastBonusPaidInFullOn
    ,C.BonusPaidOn AS LastBonusPaidOn
    ,D.BonusPaidOn AS LastBonusRefusedOn
FROM
    DATAPOINT1 A
LEFT OUTER JOIN DATAPOINT2 B ON A.EmployeeId = B.EmployeeId AND B.Status = 'Paid in Full'
LEFT OUTER JOIN DATAPOINT3 C ON A.EmployeeId = C.EmployeeId
LEFT OUTER JOIN DATAPOINT2 D ON A.EmployeeId = D.EmployeeId AND D.Status = 'Refused';

A request was made for the expected results:

EmployeeId  LastBonusPaidInFullOn  LastBonusPaidOn  LastBonusRefusedOn
----------  ---------------------  ---------------  ------------------
         3                                          01-APR-13
         1  01-JAN-13              01-JAN-13
         2  01-FEB-13              01-APR-13

Answer 1

The query seems very complicated. I think the following does what you want -- geting the last date for each of the status types:

SELECT EmployeeId, 
       max(case when Status IN ('Paid in Full') then BonusPaidOn
            end) as LastBonusPaidInFullOn
       max(case when Status IN ('Partial Payment') then BonusPaidOn
           end) as LastBonusPaidOn
       max(case when Status IN ('Refused') then BonusPaidOn
           end) as BonusPaidPartialRefused
FROM Employee 
WHERE Status IN ('Paid in Full', 'Partial Payment', 'Refused')
GROUP BY EmployeeId