Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Help finding the Max Total

    Hi,

    I have the following code
    Code:
    SELECT     
    PR.WBS2, 
    SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR' 
    THEN Ledgerar.amount * - 1 
    ELSE '0' END)  AS BillExt
    FROM   PR 
    LEFT JOIN  Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND 
    PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
    WHERE     PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
     AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND 
    pr.wbs1 = '001-298'
    GROUP BY PR.WBS2
    It prints out:
    Wbs2 BillExt
    0141 0
    0143 0
    1217 20580

    I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

    Can someone help me with this?

    Thanks.
    laura

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The quick and dirty version....

    Code:
    select top 1 a.WBS2, a.BillExt
    from 
    (SELECT     
    PR.WBS2, 
    SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR' 
    THEN Ledgerar.amount * - 1 
    ELSE '0' END)  AS BillExt
    FROM   PR 
    LEFT JOIN  Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND 
    PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
    WHERE     PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
     AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND 
    pr.wbs1 = '001-298'
    GROUP BY PR.WBS2) a
    order by BillExt desc

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nothing dirty about it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That would be nice if there was only 1 record that needed to be returned.
    Code:
    select a.WBS2, BillExt=max(a.BillExt)
    from 
    (SELECT	 
    PR.WBS2, 
    SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR' 
    THEN Ledgerar.amount * - 1 
    ELSE '0' END)  AS BillExt
    FROM   PR 
    LEFT JOIN  Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND 
    PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
    WHERE	 PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
     AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND 
    pr.wbs1 = '001-298'
    GROUP BY PR.WBS2) a
    group by a.WBS2
    order by BillExt desc
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    May 2004
    Posts
    105
    Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

    For instance :
    Code:
    Wbs2 BillExt    MaxValue
    0141   0          0
    0143   0          0
    1217  20580       1
    Thanks,
    Laura
    Last edited by lauramccord; 12-06-04 at 11:04.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    That would be nice if there was only 1 record that needed to be returned.

    Which is wat she asked for....I left my mind reading hat at home...

    Laura.....INSERT What to Where?

    Insert into that record?

    I don't understand.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2004
    Posts
    105
    I'm sorry it's hard to describe what I am asking.

    If I have the following information in a table:

    Code:
    WBS1     Amount      MAX
    0141          0       
    1217          2
    1222          200
    I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

    Ending result:
    Code:
     
    WBS1     Amount   MAX
    0141          0          
    1217          2          
    1222         200     1

    Is this possible?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

    I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

    Why do you want to mark this record and what are you going to do with it?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2004
    Posts
    105
    Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

    Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

    There is no insertion are update that can be done it is just for display purposes only.

    One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

    So I may have to go an entirely different path.

    Thanks for your help,
    Laura

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't use subqueries? What kind of lunacy is that?

    You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

    Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

    http://www.hov-hov.dk/you.htm
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by lauramccord
    Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

    Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

    There is no insertion are update that can be done it is just for display purposes only.

    One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

    So I may have to go an entirely different path.

    Thanks for your help,
    Laura
    Can you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

    -PatP

    Side note to Blindman, I'm going to have to bookmark that site!

Posting Permissions

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