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

Can you define precisely what you mean by "PreviousCom_Date"? Is it just the next-to-last Communication_Date? — Drew Shafer
– Drew Shafer, Commented Jun 14, 2013 at 20:31
are you just doing a group by with the max comdate? If so, what do you mean by previous com date? — Daniel Moses
– Daniel Moses, Commented Jun 14, 2013 at 20:39
I would like to flag first date in table as Previous communication date and the second date as Last Communication date. If I choose the date range for example between 01-05-2013 and 30-05-2013 I would like to list like requested table. — Hakan
– Hakan, Commented Jun 14, 2013 at 20:40
select client_id, min(com_date) as first_com, max(com_date) as last_com from foobar where com_date < ? and com_date > ? group by client_id — Daniel Moses
– Daniel Moses, Commented Jun 14, 2013 at 20:41
Not actually, as you will see from second table on next communication, last communication date is becoming first communication date. — Hakan
– Hakan, Commented Jun 14, 2013 at 20:43

Ed Gibbs · Accepted Answer · 2013-06-14 20:46:16Z

4

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.

answered Jun 14, 2013 at 20:46

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hakan Over a year ago

Thank you so much Ed. I am really appreciated for your help. All the best. :))))

Ed Gibbs Over a year ago

You're welcome Hakan - glad it worked for you, and great question!

Collectives™ on Stack Overflow

Complex select statement in oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related