Thread: Grouping on Max

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Philadephia
    Posts
    16
    Rep Power
    0

    Grouping on Max


    Good afternoon, I have a query set from a previous post that I now want to group by the "matter" and only return 1 row from what might be 1-12 rows (billing per month) per matter code. The last month of each matter group would then have the correct amount that was billed.

    Here is my query so far: (not working correctly)

    SELECT [Bill_Year]
    ,MAX(CAST([Bill_Month] AS INT)) as Bill_Month
    ,[Bill_Period]
    ,[Client]
    ,[RelClient]
    ,[Sort]
    ,[Matter]
    ,[Matter_Desc]
    ,MAX([Billed_YTD]) as Billed_YTD
    FROM [Datapump_Staging].[dbo].[Elite_Matters]
    WHERE Matter = '00000003-7000'
    GROUP BY Matter,
    bill_year,bill_month,bill_period,
    Matter_Desc,
    client,relclient,sort
    ORDER BY bill_year,
    bill_month,
    Matter


    This returns 12 rows in the example I am using, where I only want the single row with the greatest value, and only one row if all billed_ytd values are 0

    Data Sample:

    Bill_Year Bill_Month Bill_Period Client RelClient Sort Matter Matter_Desc Billed_YTD
    2010 1 1009 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 115325.64
    2010 2 1109 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 218326.21
    2010 3 1209 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 272254.06
    2010 4 0110 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 426491.95
    2010 5 0210 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 469225.10
    2010 6 0310 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 578301.03
    2010 7 0410 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 666837.65
    2010 8 0510 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 697156.75
    2010 9 0610 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 796365.87
    2010 10 0710 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 848248.65
    2010 11 0810 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 908671.32
    2010 12 0910 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Sub to RG for ABCDEF: OCD/DRU 1005167.47


    Thanks in advance as always

    Bryan Holmstrom
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    SELECT t.Bill_Year
         , t.Bill_Month
         , t.Bill_Period
         , t.Client
         , t.RelClient
         , t.Sort
         , t.Matter
         , t.Matter_Desc
         , t.Billed_YTD
      FROM ( SELECT Bill_Year
                  , MAX(Bill_Month) AS latest
               FROM Datapump_Staging.dbo.Elite_Matters
              WHERE Matter = '00000003-7000'
             GROUP 
                 BY Bill_Year ) AS m
    INNER
      JOIN Datapump_Staging.dbo.Elite_Matters AS t
        ON t.Bill_Year = m.Bill_Year
       AND t.Bill_Month = m.latest
       AND t.Matter = '00000003-7000'
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo