2 Replies Latest reply: May 26, 2011 11:04 AM by Subhasish RSS

    Getting Salary record in SQL

    user278427
      How do I get the records for 26-MAY-11 only?



      select pppc.component_id, ppp.pay_proposal_id, ppp.object_version_number, ppp.change_date, pppc.COMPONENT_REASON
      from per_pay_proposal_components pppc,
      per_pay_proposals ppp
      where pppc.pay_proposal_id = ppp.pay_proposal_id
      and ppp.assignment_id = 786
      and ppp.change_date = (select max(change_date)
      from per_pay_proposals ppp2
      where ppp2.pay_proposal_id = ppp.pay_proposal_id)



      COMPONENT_ID PAY_PROPOSAL_ID OBJECT_VERSION_NUMBER CHANGE_DATE COMPONENT_REASON
      ---------------------- ---------------------- ---------------------- ------------------------- ------------------------------
      2245 43783 2 26-MAY-11 PROM
      2243 43783 2 26-MAY-11 MERIT
      2244 43783 2 26-MAY-11 MKAD
      1281 41795 2 01-APR-11 MKAD
      1280 41795 2 01-APR-11 MERIT
        • 1. Re: Getting Salary record in SQL
          Duncan Casemore
          Do you really mean just get the records for 26th May? If so:
          and ppp.change_date = to_date('20110526','YYYYMMDD')
          Or do you mean get the last pay proposal for the assignment?
          select pppc.component_id, ppp.pay_proposal_id, ppp.object_version_number, ppp.change_date, pppc.COMPONENT_REASON
          from   per_pay_proposal_components pppc,
                 per_pay_proposals ppp
          where  pppc.pay_proposal_id = ppp.pay_proposal_id
          and    ppp.assignment_id = 786
          and    ppp.change_date = (select max(change_date) 
                                    from   per_pay_proposals ppp2
                                    where  ppp2.assignment_id = 786);
          • 2. Re: Getting Salary record in SQL
            Subhasish
            If you want the salary records for the current date only then you could use this condition
            and ppp.change_date  = trunc(sysdate)
            If you want the records which were modified on the latest date then use this in your sub query
            (select max(change_date) 
            from per_pay_proposals ppp2
            where ppp2.assignment_id = ppp.assignment_id)