Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Querying for "next to last" date?

    I need to compare the most recent paycheck and the previous paycheck. Basically, I'm looking for something like this IF i knew what the last two check dates were per person, which I won't

    SELECT gross_amount
    FROM paychecks
    WHERE check_dt = 9/1/2010 and check_dt=9/15/2010

    So while it's easy to identify the MAX check_dt, I don't know how to grab the next to last check date.
    Last edited by turk99; 05-28-10 at 17:11.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select max(hire_date) from employees;
    
    MAX(HIRE_
    ---------
    21-APR-08
    
    SQL>  select max(hire_date) from employees where hire_date < (select max(hire_date) from employees);
    
    
    MAX(HIRE_
    ---------
    24-MAR-08
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is just a simple anonymous block that should list the last two checks.
    Code:
    declare
    
      v_emp_no        number( 5,0 ) := 12345;
      v_check_dt      date;
      v_gross_amount  number( 9,2 );
    
      CURSOR emp_chk_cur( p_emp_no in number ) is
      select check_dt, gross_amount
       from  paychecks
      where  emp_no  =  p_emp_no
      order by check_dt desc;
    
    begin
      dbms_output.enable(100000);
      open emp_chk_cur( v_emp_no );
    
      for i IN 1..2
      loop
        fetch emp_chk_cur into v_chk_dt, v_gross_amout;
        dbms_output.put_line( 'Emp_no-'       || to_char( v_emp_no )                 || ', ' ||
                              'Check_dt-'     || to_char( v_check_dt, 'yyyy-mm-dd' ) || ', ' ||
                              'Gross_Amount-' || to_char( v_gross_amount, 99999.99 ) );
      end loop;
      close emp_chk_cur;
    end;

  4. #4
    Join Date
    May 2006
    Posts
    132
    Quote Originally Posted by turk99 View Post
    I need to compare the most recent paycheck and the previous paycheck.
    Not really sure what you mean by "compare", but the following provides a gross amount for the last two paychecks for each employee:

    Code:
    select emp_id, sum(check_amount) gross_amount
    from (
    	select emp_id
    	,	check_amount
    	,	row_number() over (partition by emp_id order by payday desc) rn
    	from paycheck
    	order by payday desc)
    where rn <= 2
    group by emp_id;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •