Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Max effect date 2 related records in same table

    I thought of using this table, that has all the info that I want, and linking to itself using the program as a parameter and linking by the Model to get the activity. In addition, need to apply the latest effective date to the activity and program. Not sure if it is useful but the first five digits of the program is always the first five of the activity.



    Model ATTB ATTB Value Eff Dt.
    1234567 Activity 1212145 1/4/2001
    1234567 Activity 1234675 2/28/2012
    1234567 Activity 1212463 1/1/2001
    1234567 Program 12121 7/1/2001
    1234567 Program 12121 1/1/2001
    1234567 Program 12346 2/4/2001


    Want to report this:
    Model ATTB ATTB Value Eff Dt.
    1234567 Activity 1234675 2/28/2012
    1234567 Program 12346 2/4/2001

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.Model 
         , t.ATTB 
         , t.ATTBValue 
         , t.EffDt
      FROM ( SELECT Model
                  , ATTB
                  , MAX(EffDt) AS latest
               FROM daTable
             GROUP
                 BY Model
                  , ATTB ) AS m
    INNER
      JOIN daTable AS t
        ON t.Model = m.Model
       AND t.ATTB  = m.ATTB
       AND t.EffDt = m.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    I tried putting in SQL Expression field and the Record Selection Formula Editor but it doesn't like the first part - SELECT t.Model thinks its missing something

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, man, i don't do crystal reports

    but the sql by itself should be okay

    is there no sql window you can just paste the entire statement into?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    I got the report to pull the data but I am seeing:
    GH1) Program
    D) 1234567
    GH1)Activity
    D)1234567

    What I want is:
    GH1.) Program
    GH2.) Activity
    D) 1234567
    Is that easy to fix in Crystal?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have absolutely no idea what you're talking about

    there is no correlation to your sample data provided in post #1 except for the value 1234567
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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