Complex select statement in oracle

The analytical LAG function will pair up the dates for you. This will pair up the current/previous dates:

SELECT  Client_ID,  Communication_Date AS PreviousCom_Date,  LAG(Communication_Date) OVER (    PARTITION BY Client_ID    ORDER BY Communication_Date DESC) AS LastCom_DateFROM PrevTable;

Then there's the problem of making sure only rows with "Previous" and "Last" values are included, unless like Client_ID 1003 there's only one row in the source table. That can be taken care of by counting the Client_ID rows and checking for (a) only one row for the client or (b) if more than one row for the client then both dates must have values:

SELECT * FROM (  SELECT    Client_ID,    Communication_Date AS PreviousCom_Date,    LAG(Communication_Date) OVER (      PARTITION BY Client_ID      ORDER BY Communication_Date DESC) AS LastCom_Date,    COUNT(*) OVER (      PARTITION BY Client_ID) AS DatePair_Count  FROM PrevTable  )WHERE DatePair_Count = 1   OR (PreviousCom_Date IS NOT NULL AND LastCom_Date IS NOT NULL)

Just precede the above query with CREATE TABLE whatever AS and it will create the new table.

There's a SQL Fiddle of the SELECT statement here.