Query to find records with last two approval date

Question

I have two tables, Plan and Plan_Log. Their structure looks like below:

Table: Plan

Id  PlanName  PlanApprovalDate         PlanStatus
--  --------  -----------------------  ----------
1   abc       18-04-2014 15:48:22.790  A
2   def       19-07-2014 15:48:22.790  A

Table: Plan_Log

Id  PlanName  PlanApprovalDate
--  --------  -----------------------
1   abc       18-04-2014 15:48:22.790
2   abc       17-04-2014 15:48:22.790
3   abc       15-04-2014 15:48:22.790 
4   def       19-07-2014 15:48:22.790
5   def       18-07-2014 15:48:22.790
6   def       17-07-2014 15:48:22.790

From these records I want to retrieve each plan with its two most recent PlanApprovalDate records. Also, I need to make sure that these plans are active ones (PlanStatus). So my final list should retrieve:

ID  PlanName  PlanApprovalDate
--  --------  -----------------------
1   abc       18-04-2014 15:48:22.790
2   abc       17-04-2014 15:48:22.790
3   def       18-07-2014 15:48:22.790 
4   def       19-07-2014 15:48:22.790

How can I achieve this result?

I am using Sybase ASE 12.5.4

What version of Sybase are you using? In particular, does it support window functions such as `row_number()`? — Gordon Linoff, Jul 22 '14 at 11:36

score 0 · Accepted Answer · answered Jul 22 '14 at 11:38

0

You can do this with row_number() quite easily (supported by some versions of Sybase):

select id, plan_name, PlanApprovalDate
from (select pl.*,
             row_number() over (partition by plan_name order by PlanApprovalDate desc
                               ) as seqnum
      from plan_log pl
     ) pl
where seqnum <= 2;

Without windows functions, you can do this in different ways. Here is a method with a correlated subquery in the where clause:

select id, plan_name, PlanApprovalDate
from plan_log pl
where 2 >= (select count(*)
            from plan_log pl2
            where pl2.plan_name = pl.plan_name and
                  pl2.PlanApprovalDate >= pl.PlanApprovalDate
           );

answered Jul 22 '14 at 11:38

Gordon Linoff

1,242,037
58
646
786

I have slightly modified my question. I have added one flag (PlanStatus). I need to check for only active plans. I am using Sybase 12.5.4 version. – som6233 Jul 22 '14 at 11:50
@user3049531 . . . Just add a `where` clause to the queries. For the second one, add the `where` clause to both the subquery and the outer query. – Gordon Linoff Jul 22 '14 at 12:16
Can you provide the query. – som6233 Jul 22 '14 at 15:31

Query to find records with last two approval date

1 Answers1