April 28, 2009 at 4:35 pm
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:
April 28, 2009 at 7:23 pm
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