[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

need a specific record from the query

SELECT  
      fbv.book_type_code,
      fdp.period_name,  
      fab.asset_number,
       fab.description,
       fcb.segment1 || '.' || fcb.segment2 asset_category,
       fbv.original_cost original_asset_cost,
       DECODE (fbv.cost, 0, 0, (fbv.cost - fds.deprn_reserve)) net_book_value,
       fds.ytd_deprn,
       fds.deprn_reserve acc_deprn,
       fbv.date_placed_in_service,
       fbv.deprn_method_code,
       (fbv.life_in_months / 12) life_in_years, fds.*
  FROM apps.fa_additions_v fab,
  apps.fa_categories_b fcb,
  apps.fa_books_v fbv,
       fa_deprn_summary fds,
      apps.fa_deprn_periods fdp--,
       --apps.fa_distribution_history fdh,
       --apps.gl_code_combinations gcc,
       --apps.fa_locations fl
WHERE     1 = 1
       AND fab.asset_id = fbv.asset_id
       AND fcb.category_id = fab.asset_category_id
       AND fbv.transaction_header_id_out IS NULL
       AND fdp.book_type_code = fbv.book_type_code
              AND fds.book_type_code = fbv.book_type_code
       and fdp.period_counter=fds.period_counter
       --AND fdh.asset_id = fbv.asset_id
       --AND fdh.code_combination_id = gcc.code_combination_id
       --AND fdh.location_id = fl.location_id
       AND fbv.transaction_header_id_out IS NULL
      -- AND fdh.transaction_header_id_out IS NULL
       AND fds.asset_id = fbv.asset_id
      AND  fbv.book_type_code  in  ('TPC US COMP TAX')-- ( 'TPC_US_COMP')--, 'TPC US COMP TAX')
    and asset_number = '39341'
     and fdp.period_name in ( 'Jan-2011', 'Feb-2011', 'Mar-2011', 'Apr-2011', 'May-2011',
      'Jun-2011', 'Jul-2011', 'Aug-2011', 'Sep-2011', 'Oct-2011', 'Nov-2011', 'Dec-2011')
asset-list.txt
0
pardeshirahul
Asked:
pardeshirahul
1 Solution
 
pardeshirahulAuthor Commented:
the output i need only the max of period name records in this case DEC-11 is the max period for other assets it can be different month
0
 
gatorvipCommented:
Do you need "only the max of period name records" (i.e., the last period)? Or all the records associated with that period?

If the former, then you could do this:

select max( to_date(period_name, 'Mon-YYYY') )
from
...


If the latter, then add a rank() clause to your select:

select *
from
(
select ...
 , rank() over(order by to_date(fdp.period_name, 'MON-YYYY') desc) most_recent
from
...
)
where most_recent=1

Open in new window


Since rank() gives the same value to rows with the same ranking criteria, everything that corresponds to the most recent value in fdp.period_name will have a rank value of 1.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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