June 8, 2009 at 2:41 pm
Hello,
I have a rather basic question but today I'm feeling a bit stumped. I want to create a count function based on an ID number per region in my statement.
DECLARE @HEADCNT INT
SET @HEADCNT = '20'
;with
CTEMain as
(SELECT
MAX(CASE WHEN CRM_Partners.DESCRIPTION = 'Exec. Service Employee' THEN CRM_Partners.ADDRESS ELSE NULL END) AS [Exec. Service Employee]
, MAX(CASE WHEN CRM_Partners.DESCRIPTION = 'Service Employee Group' THEN CRM_Partners.ADDRESS ELSE NULL END) AS [Service Employee Group]
, MAX(CASE WHEN CRM_Partners.DESCRIPTION = 'Regional Service Manager' THEN CRM_Partners.ADDRESS ELSE NULL END) AS [Regional Service Manager]
, CRMv_Order_Confirmation_PartsLabor.TRANSACTION_ID AS [Serv Order ID]
, CRMv_Order_Confirmation_PartsLabor.CONFIRMATION_ID AS [Conf ID]
, CRMv_Order_Confirmation_PartsLabor.[CONFIRMATION DESCRIPTION] AS [Conf Desc]
, CRMv_Order_Confirmation_PartsLabor.SRV_CONF_TIME AS [Labor Hrs]
, CRMv_Order_Confirmation_PartsLabor.SRV_CONF_TUNIT AS [Labor Hrs Unit]
, CRM_StatusCodes.USER_STATUS AS [Serv Order Status]
, CRMv_Order_Confirmation_PartsLabor.ABCDE_CODE_DESC AS [Serv Order Desc]
, CRM_Orders.CREATED_AT AS [Serv Order Created At]
, CRM_Orders.SERIAL AS [Serial]
, CRM_SubjectReferences.PRODUCT_ID AS [Equip No]
, MAX(CASE WHEN CRM_Partners.DESCRIPTION = 'Sold-To Party' THEN CRM_Partners.ADDRESS ELSE NULL END) AS [Sold-To Party]
, CRM_Orders.PRIORITY_DESCRIPTION AS [Priority Desc]
, CRM_Orders.PRIORITY AS [Priority Code]
, CRM_StatusCodes.END_DATE AS [Serv Order End date]
, CRM_StatusCodes.USNAM AS [Order Completed By]
FROM CRMv_Order_Confirmation_PartsLabor INNER JOIN
CRM_Orders ON CRMv_Order_Confirmation_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID INNER JOIN
CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_SubjectReferences ON CRM_StatusCodes.TRANSACTION_ID = CRM_SubjectReferences.TRANSACTION_ID
WHERE
CRMv_Order_Confirmation_PartsLabor.TRANSACTION_ID <> 'NULL'
AND CRM_StatusCodes.USER_STATUS = 'Complete'
AND CRMv_Order_Confirmation_PartsLabor.SRV_CONF_TIME <> '0.00'
GROUP BY
CRMv_Order_Confirmation_PartsLabor.TRANSACTION_ID
, CRMv_Order_Confirmation_PartsLabor.CONFIRMATION_ID
, CRMv_Order_Confirmation_PartsLabor.[CONFIRMATION DESCRIPTION]
, CRMv_Order_Confirmation_PartsLabor.ORDER_DATE
, CRMv_Order_Confirmation_PartsLabor.DESCRIPTION
, CRMv_Order_Confirmation_PartsLabor.SRV_CONF_TIME
, CRMv_Order_Confirmation_PartsLabor.SRV_CONF_TUNIT
, CRM_StatusCodes.USER_STATUS
, CRM_Orders.CREATED_AT
, CRMv_Order_Confirmation_PartsLabor.CONFIRMATION_ID
, CRMv_Order_Confirmation_PartsLabor.ABCDE_CODE_DESC
, CRM_Orders.CREATED_AT
, CRM_Orders.SERIAL
, CRM_SubjectReferences.PRODUCT_ID
, CRM_Orders.PRIORITY_DESCRIPTION
, CRM_Orders.PRIORITY
, CRM_StatusCodes.END_DATE
, CRM_StatusCodes.USNAM),
CTE2 as
(select *,
row_number() over (partition by [Serv Order ID], [Conf ID] order by [Serv Order ID]) as Row
from CTEMain)
select *
from CTE2
where Row = 1;
Here is an example of my result set:
Service Employee Group | Serv Order ID | Conf ID
T - CSAEZR7 Region 7 - Field30000000089000000933
T - CSAEZR7 Region 7 - Field30000000089000002367
T - CSAEZR7 Region 7 - Field30000000089000003896
T - CSAEZR7 Region 7 - Field30000000109000004973
T - CSAEZR7 Region 7 - Field30000000119000000774
T - CSAEZR7 Region 7 - Field30000000139000005127
T - CSAEZR6 Region 6 - Field30000000159000000806
T - CSAWZR1 Region 1 - Field30000000169000003199
T - CSAWZR1 Region 1 - Field30000000169000005080
T - CSAEZR6 Region 6 - Field30000000179000004856
T - CSAEZR6 Region 6 - Field30000000179000005282
In my example, I have 4 Serv Order ID's in Region 7, 2 in region 6, 1 in region 1, ect.
Could someone kindly help me out with this function? These functions are simple enough but for some reason it just isn't coming to me... must be Monday. :doze:
~Michelle
June 8, 2009 at 10:54 pm
I couldn’t understand what you want. Can you post the question again, but this time add a script to create a demo table, then write some insert statements to insert test data and explain the results that you want to get. This will help all other readers understanding what you need and you’ll get a solution much faster.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 8, 2009 at 11:06 pm
mmunson (6/8/2009)
Here is an example of my result set:Service Employee Group | Serv Order ID | Conf ID
T - CSAEZR7 Region 7 - Field30000000089000000933
T - CSAEZR7 Region 7 - Field30000000089000002367
T - CSAEZR7 Region 7 - Field30000000089000003896
T - CSAEZR7 Region 7 - Field30000000109000004973
T - CSAEZR7 Region 7 - Field30000000119000000774
T - CSAEZR7 Region 7 - Field30000000139000005127
T - CSAEZR6 Region 6 - Field30000000159000000806
T - CSAWZR1 Region 1 - Field30000000169000003199
T - CSAWZR1 Region 1 - Field30000000169000005080
T - CSAEZR6 Region 6 - Field30000000179000004856
T - CSAEZR6 Region 6 - Field30000000179000005282
~Michelle
Hey there, Michelle,
What we really need is some test data in a readily consumable format. Please read the article at the link in my signature line below. If you post the way the article suggests, you get help in the form of working code much, much quicker.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply