• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1799
  • Last Modified:

MaxDate and Second to max date

Hi all,

Ok Ill provide 2 records to start the ball rolling

Worksrollnumber               Surname                 Latest Pay Date    Amount      Last Pay Date     Amount
   123456                             Bloggs                     21/10//08              890              21/09/08            890
   789101                             Doe                          21/010/08            1000             21/02/08           650

Ok, the 2 records above are what I want to see, and a comparison will be done to highlight differences in monthly pension payments.   What I need to do is find the Max Date of all employees i.e their Latest pay Date and then the Second to max date, which means when a last entry was made, so it is 21/09/08 for Bloggs and 21/02/08 for Doe.  I have written a query to get the Max Date for each row.  Now I need to find the second to max date.  I have tried multiple nested queries and also created a MaxDate view and queried that but I only got 'too many values'  What I am trying to do is get a maxdate of the set of dates which arent equal the current max date.   Sounds simple but it has been a ball ache :/  

Thanks
0
BPMonk
Asked:
BPMonk
1 Solution
 
sujith80Commented:
You can use a query like this:

select surname, max(case when cn = 1 then pay_date else null end) latest_pay,
max(case when cn = 2 then pay_date else null end) last_pay
from (
select surname, pay_date, row_number() over(partition by surname order by pay_date desc) cn
from <your table>
)
group by surname;
SQL> select * from tbl1;
 
SURNAME              PAY_DATE
-------------------- --------
Bloggs               21/10/08
Bloggs               21/09/08
Bloggs               21/08/08
Doe                  21/10/08
Doe                  21/02/08
Doe                  21/01/08
 
6 rows selected.
 
SQL> 
SQL> select surname, max(case when cn = 1 then pay_date else null end) latest_pay,
  2  max(case when cn = 2 then pay_date else null end) last_pay
  3  from (
  4  select surname, pay_date, row_number() over(partition by surname order by pay_date desc) cn 
  5  from tbl1
  6  )
  7  group by surname;
 
SURNAME              LATEST_P LAST_PAY
-------------------- -------- --------
Bloggs               21/10/08 21/09/08
Doe                  21/10/08 21/02/08
 
SQL> 

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now