Complex select statement in oracle

Question

I have a table that holds Client_ID and Communication_Date I would like to produce below table. Table should list previous and last communication dates.Is there any way to write a query for it? Any help will be appreciated.

Thanks

ORIGINAL TABLE

ID    Client_ID     Communication_date
1     1001          01-05-2013
2     1002          01-05-2013
3     1003          02-05-2013
4     1001          09-05-2013
5     1001          14-05-2013
6     1002          18-05-2013
7     1002          25-05-2013



Requested Table
Client_ID           PreviousCom_Date       LastCom_date
1001                01-05-2013             09-05-2013
1001                09-05-2013             14-05-2013
1002                01-05-2013             18-05-2013
1002                18-05-2013             25-05-2013
1003                02-05-2013             Null or whatever

Answer 1

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_Date
FROM 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.