SQL Query Help / MAX Function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL Query Help / MAX Function

  1. #1
    Join Date
    Jul 2014
    Posts
    1

    SQL Query Help / MAX Function

    I'm new to sql. My results have multiple lines because a new line is added on the last day of each month to the PS_DIST_LN table. What I want to do is select the record with the most recent accounting_dt in this table. I thought I could use MAX on the accounting_dt field but keep getting errors. Any help would be greatly appreciated.

    Also if there is a better way to write this query, I'd appreciate those comments as well. I'm new to this want am looking to learn the right way to do these things. Thanks again for your help.

    SELECT
    pa.BUSINESS_UNIT,
    pdr.DEPTID,
    pa.IN_SERVICE_DT,
    pa.ASSET_ID,
    pa.DESCR,
    pb.LIFE,
    pb.METHOD,
    pdr.COST,
    pdr.DEPR,
    pdr.depr_ytd,
    pdl.accounting_dt

    from PS_ASSET pa,
    PS_BOOK pb,
    PS_DEPR_RPT pdr,
    PS_DIST_LN pdl

    where pa.BUSINESS_UNIT = 'A0056'
    and pa.ASSET_ID = '000000000003'
    and pb.BOOK = 'PERFORM'
    and pdr.book = 'PERFORM'
    and pdl.book = 'PERFORM'
    and pdr.FISCAL_YEAR = '2014'
    and pdr.ACCOUNTING_PERIOD = '12'
    and pb.ASSET_ID= pa.ASSET_ID and pb.BUSINESS_UNIT = pa.BUSINESS_UNIT
    and pdr.ASSET_ID= pa.ASSET_ID and pdr.BUSINESS_UNIT = pa.BUSINESS_UNIT
    and pdl.ASSET_ID= pa.ASSET_ID and pdl.BUSINESS_UNIT = pa.BUSINESS_UNIT
    and pdl.distribution_type = 'DE'

    RESULTS:

    BU DEPT INSERVDT IDNO DSCR LIFE METH COST DEPREC ACCDP ACCT_DT

    A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-JAN-14
    A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 28-FEB-14
    A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-MAR-14
    A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 30-APR-14
    A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-MAY-14

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Try something like this:
    Code:
    SELECT *
      FROM ( SELECT Pa.Business_Unit
                  , Pdr.Deptid
                  , Pa.In_Service_Dt
                  , Pa.Asset_Id
                  , Pa.Descr
                  , Pb.Life
                  , Pb.Method
                  , Pdr.Cost
                  , Pdr.Depr
                  , Pdr.Depr_Ytd
                  , Pdl.Accounting_Dt
                  , ROW_NUMBER ( )
                       OVER ( PARTITION BY Pa.Business_Unit
                                         , Pdr.Deptid
                                         , Pa.Asset_Id
                              ORDER BY Pdl.Accounting_Dt DESC )
                       Rn
               FROM Ps_Asset Pa
                  , Ps_Book Pb
                  , Ps_Depr_Rpt Pdr
                  , Ps_Dist_Ln Pdl
              WHERE Pa.Business_Unit = 'A0056'
                AND Pa.Asset_Id = '000000000003'
                AND Pb.Book = 'PERFORM'
                AND Pdr.Book = 'PERFORM'
                AND Pdl.Book = 'PERFORM'
                AND Pdr.Fiscal_Year = '2014'
                AND Pdr.Accounting_Period = '12'
                AND Pb.Asset_Id = Pa.Asset_Id
                AND Pb.Business_Unit = Pa.Business_Unit
                AND Pdr.Asset_Id = Pa.Asset_Id
                AND Pdr.Business_Unit = Pa.Business_Unit
                AND Pdl.Asset_Id = Pa.Asset_Id
                AND Pdl.Business_Unit = Pa.Business_Unit
                AND Pdl.Distribution_Type = 'DE' )
     WHERE Rn = 1;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width