Using Max Value in a group

  • Hello,

    I having a heck of a problem trying to figure out how to use the max function within a group. I've been working on it all day and trying to google as much as I can...

    Here's my query:

    SELECTCRM_StatusCodes.TRANSACTION_ID as [Service Order ID],

    CRM_Partners.PARTNER,

    CRM_Partners.PARTNER_FCT,

    CRM_Partners.DESCRIPTION [Function],

    CRM_StatusCodes.SYSTEM_STATUS as [Service Order Status],

    CRM_Partners.ADDRESS,

    CRM_orders.created_at as [Back Log Date],

    max(crm_statuscodes.end_date) as [LastStatus],

    count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */

    FROMCRM_Partners

    INNER JOINCRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID

    INNER JOINCRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID

    INNER JOINCRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID

    WHERECRM_StatusCodes.SYSTEM_STATUS in

    ('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',

    'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',

    'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',

    'In Process','Open')

    AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')

    AND CRM_PartsLabor.DESCRIPTION = 'FSE Labor'

    and CRM_StatusCodes.TRANSACTION_ID not like ('9%')

    and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())

    group by CRM_StatusCodes.TRANSACTION_ID, CRM_Partners.ADDRESS, CRM_Partners.PARTNER, CRM_Partners.PARTNER_FCT,

    CRM_Partners.DESCRIPTION, CRM_StatusCodes.SYSTEM_STATUS, CRM_orders.created_at

    When I run this, I get:

    Service Order IDPARTNER_FCT Service Order StatusADDRESS LastStatus

    300000341652 Distribution lockBrian Clayton 2009-04-23 18:06:20.000

    300000341652 Released Brian Clayton 2009-04-23 18:08:13.000

    3000003416ZRSM Distribution lockRobert Smith 2009-04-23 18:06:20.000

    3000003416ZRSM Released Robert Smith 2009-04-23 18:08:13.000

    300000341656 Distribution lockRegion 12 2009-04-23 18:06:20.000

    300000341656 Released Region 12 2009-04-23 18:08:13.000

    (Sorry the table doesn't look very good, I tried.)

    However, I'm only concerned with the results that have a MAX LastStatus (CRM_StatusCodes.SYSTEM_STATUS ) and they have a Service Order Status of "Released'

    I tried to break it down by doing this:

    CRM_StatusCodes.end_date = (select max(crm_statuscodes.end_date) from crm_statuscodes where CRM_StatusCodes.TRANSACTION_ID = CRM_StatusCodes.system_status)

    But then it didn't give me any results at all.

    I'm a real newbie at this and I hope I'm not asking dumb questions, so I'm hoping that somebody knows how to do this. I catch quick if it's written for me or I see an example. But I'm totally lost here.

    Thank you in advance!!

    Michelle :crying:

  • You need to have a nested SELECT statement to return the maximum status end date and the Service Order Count by transaction id.

    SELECT CRM_StatusCodes.TRANSACTION_ID

    , MAX(CRM_StatusCodes.end_date) as [LastStatus]

    , count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count]

    FROM CRM_StatusCodes

    WHERE CRM_StatusCodes.SYSTEM_STATUS in

    ('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',

    'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',

    'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',

    'In Process','Open')

    GROUP BY CRM_StatusCodes.TRANSACTION_ID

    Then JOIN the nested SELECT to the remainder of your query:

    SELECT CRM_StatusCodes.TRANSACTION_ID as [Service Order ID],

    CRM_Partners.PARTNER,

    CRM_Partners.PARTNER_FCT,

    CRM_Partners.DESCRIPTION [Function],

    CRM_StatusCodes.SYSTEM_STATUS as [Service Order Status],

    CRM_Partners.ADDRESS,

    CRM_orders.created_at as [Back Log Date],

    CRM_Orders_Status_Current.[LastStatus],

    CRM_Orders_Status_Current.[Service Order Count]

    FROM CRM_Partners

    INNER JOIN CRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID

    INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID

    JOIN (SELECT CRM_StatusCodes.TRANSACTION_ID

    , MAX(CRM_StatusCodes.end_date) as [LastStatus]

    , count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count]

    FROM CRM_StatusCodes

    WHERE CRM_StatusCodes.SYSTEM_STATUS in

    ('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',

    'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',

    'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',

    'In Process','Open')

    GROUP BY CRM_StatusCodes.TRANSACTION_ID

    ) AS CRM_Orders_Status_Current

    on CRM_Orders_Status_Current.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID

    and CRM_Orders_Status_Current.[LastStatus] = CRM_StatusCodes.end_date

    WHERE CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')

    AND CRM_PartsLabor.DESCRIPTION = 'FSE Labor'

    and CRM_StatusCodes.TRANSACTION_ID not like ('9%')

    and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())

    Note that the GROUP BY is needed for the nested SELECT but is not needed for the outer SELECT.

    As a side note: the below restrict condition cannot be used with an idexed column,which will affect performance.

    cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())

    If you are attempting to restrict to within the last 5 days, please read these posts:

    http://www.sqlservercentral.com/Forums/Topic379596-8-1.aspx

    To round a datetime to a date only, fastest is DATEADD(dd,DATEDIFF(dd,0,getdate()),0)

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply