Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: SQL Query Help (Please)

    I'm trying to write a sql statement to relieve countless hours of manual effort. Please help if you can.

    Keeping the data simple, think of one user listed 3 times. Action dates are, 8/30/2009, 9/5/2009, and 9/5/2009, using identical account codes. Sequence value for 8/30 is 0. Sequence values for 9/5 are 0 and 1. How can I only show the value for 8/30 with a sequence of 0 (highest for group in that date and account code range) and the one value from 9/5 with a sequence of 1 (max for the date and for the specific account code on that date)? Normally there are many names, account codes, and dates.

    I have this:
    SELECT NAME, ANNUAL_RT, Acct_CD, DIST_PCT, FUNDING_BEG_DT, JOB_EFFSEQ, ACTION_DT
    FROM Table
    GROUP BY NAME, ANNUAL_RT, Acct_CD, DIST_PCT, FUNDING_BEG_DT, JOB_EFFSEQ, ACTION_DT
    ORDER BY Acct_CD, Max(ACTION_DT);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.NAME
         , t.ANNUAL_RT
         , t.Acct_CD
         , t.DIST_PCT
         , t.FUNDING_BEG_DT
         , t.JOB_EFFSEQ
         , t.ACTION_DT
      FROM ( SELECT Acct_CD
                  , MAX(ACTION_DT) AS max_date
               FROM daTable
             GROUP
                 BY Acct_CD ) AS m
    INNER
      JOIN daTable AS t
        ON t.Acct_CD    = m.Acct_CD
       AND t.ACTION_DT  = m.max_date
    ORDER 
        BY Acct_CD
         , t.ACTION_DT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    The query doesn't return the proper results, close but not all. When there are multiple account codes on both 8/30 and 9/30, its only returning 1 record for 8/30 and the correct values for 9/30.

    Maybe more information would help.... Use this as an example of the data.
    ID Name Annual_RT ACCT_CD DIST_PCT FUND_BEG_DT SEQ ACT_DT
    1 JT 25261 G85252 30 9/1/2009 0 8/28/09
    2 JT 25261 G85252 30 9/1/2009 0 9/30/09
    3 JT 25261 G85252 30 9/1/2009 1 9/30/09
    4 JT 25261 G18752 10 9/1/2009 0 8/28/09
    5 JT 25261 G18752 10 9/1/2009 0 9/30/09
    6 JT 25261 G18752 10 9/1/2009 1 9/30/09
    7 JT 25261 K17813 10 9/1/2009 0 8/28/09
    8 JT 25261 K17813 10 9/1/2009 0 9/30/09
    9 JT 25261 K17813 10 9/1/2009 1 9/30/09
    10 JT 25261 T93854 50 9/1/2009 0 8/28/09
    11 JT 25261 T93854 50 9/1/2009 0 9/30/09
    12 JT 25261 T93854 50 9/1/2009 1 9/30/09

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by JtSearch View Post
    The query doesn't return the proper results, close but not all. When there are multiple account codes on both 8/30 and 9/30, its only returning 1 record for 8/30 and the correct values for 9/30.

    Maybe more information would help.... Use this as an example of the data
    What is your expected result for that sample data?

Posting Permissions

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