August 5, 2009 at 8:59 am
Good morning! 🙂
I have this simple query but I need to add a rather complex calculation and I'm not even sure where to start.
This is my query:
;with
CTEMain as
(SELECT DISTINCT
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 [Region]
, MAX(CASE WHEN CRM_Partners.DESCRIPTION = 'Ship-To Party' THEN CRM_Partners.ADDRESS ELSE NULL END) AS [Site]
, CRM_Orders.TRANSACTION_ID AS [Service Order ID]
, CRM_Orders.ORDER_DESCRIPTION AS [Service Order Desc]
, cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) AS [Service Order Created At]
, CRM_Orders.SERIAL AS [Serial]
, CRM_SubjectReferences.PRODUCT_ID AS [Equip ID]
, CRM_Orders.PRIORITY AS [Priority Code]
, CRM_Orders.PRIORITY_DESCRIPTION AS [Priority Desc]
, CRM_SubjectReferences.KURZTEXT AS [Service Order Categorization code]
, CRM_CodeTexts.ABCDE_CODE_DESC AS [Service Order Categorization]
, CRM_Orders.PROCESS_TYPE AS [Service Order Trans Type]
, case when datepart(month,CRM_StatusCodes.UDATE) <= 9 then cast(cast(datepart(month,CRM_StatusCodes.UDATE) as varchar(1)) as varchar(2)) else cast(datepart(month,CRM_StatusCodes.UDATE) as varchar(2)) end AS [Closed Month]
, DATENAME (MONTH,CRM_StatusCodes.UDATE) + ' - ' + DATENAME (YEAR,CRM_StatusCodes.UDATE) AS [Service Order Last Update At Month]
, datediff(day,CRM_StatusCodes.END_DATE, CRM_StatusCodes.START_DATE) AS [Service Order Duration (Days)]
, getdate() AS [Report Created At]
FROM CRM_Orders 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 INNER JOIN
CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID INNER JOIN
CRM_CodeTexts ON CRM_SubjectReferences.KURZTEXT = CRM_CodeTexts.KURZTEXT
WHERE
CRM_Orders.TRANSACTION_ID LIKE ('3%')
AND CRM_SubjectReferences.PRODUCT_Id <> ' '
--AND CRM_Orders.CREATED_AT LIKE ('200906%')
--and month(CRM_StatusCodes.UDATE) = month(getdate())
AND CRM_Orders.PRIORITY = '2'
GROUP BY
CRM_Orders.TRANSACTION_ID
, CRM_Orders.ORDER_DESCRIPTION
, CRM_Orders.CREATED_AT
, CRM_Orders.SERIAL
, CRM_SubjectReferences.PRODUCT_ID
, CRM_Orders.PRIORITY
, CRM_Orders.PRIORITY_DESCRIPTION
, CRM_SubjectReferences.KURZTEXT
, CRM_CodeTexts.ABCDE_CODE_DESC
, CRM_Orders.PROCESS_TYPE
, CRM_StatusCodes.END_DATE
, CRM_StatusCodes.START_DATE
, CRM_StatusCodes.UDATE),
CTE2 as
(select *,
row_number() over (partition by [Service Order ID], [Equip ID] order by [Service Order ID]) as Row
from CTEMain)
select *
from CTE2
where Row = 1;
But the enduser wants me to add a calculation where I find any orders (CRM_Orders.TRANSACTION_ID AS [Service Order ID]) that I have the same serial # CRM_Orders.SERIAL AS [Serial]) and the same Service Order Categorization Code CRM_SubjectReferences.KURZTEXT AS [Service Order Categorization code]) and that has been opened (, cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) AS [Service Order Created At]) within 5 days from each other.
I hope this makes sense.
Here is an example:
Service Order ID | Service Order Created At | Serial | Service Order Categorization code
3000000021 | 2009-03-15 20:34:10.000 | 110214 | A04 B22 C17 D00 E00
3000000049 | 2009-03-15 21:00:49.000 | 110214 | A04 B22 C17 D00 E00
I have two different Service Order ID numbers but they both have the same serial number, the same Service Order Categorization code and it fits the criteria of being created at within 5 days.
The objective is to find out if customers are calling in for the same problem with their machine within 5 days. That helps us determine if the machines are getting fixed properly the first time.
I have no idea where to even start with this or if sql is the best tool to use. I thought about reporting services as well. And I can't visulaize what the output will look like either. That's why I was hoping somebody who's done this before could get me on the right path.
Thank you very much! Your help is appreciated in advance!
Michelle :unsure:
August 5, 2009 at 1:01 pm
Service Order ID | Service Order Created At | Serial | Service Order Categorization code
3000000021 | 2009-03-15 20:34:10.000 | 110214 | A04 B22 C17 D00 E00
3000000049 | 2009-03-15 21:00:49.000 | 110214 | A04 B22 C17 D00 E00
Let me try to get you started.
You want to display order 3000000049, because it has a previous matching order within the 5 day period. So lets suppose your output is just that, plus the number of the previous order, in case anyone wants to spot-check your results.
Previous Order
3000000049 | 2009-03-15 21:00:49.000 | 110214 | A04 B22 C17 D00 E00 | 3000000021
How many input orders are we talking about here? I can think of several approaches, all of which involve the ROW_NUMBER() function, so read up on it while I set up a sample solution.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 5, 2009 at 1:38 pm
Hi Bob!
Thanks for replying. There could be a total hundreds of orders per month, maybe around 600 - 800. But total of matching serials numbers with the same code within 5 days, perhaps around 10.
I'll read up on row_numbers a bit more, I'm already using a row_number function for my CTE too...
Thanks for any help you can provide, I'm a bit lost on this one....
Michelle 🙂
August 5, 2009 at 2:22 pm
Hang on just a little while longer. Caught a rush job at my office.
I haven't forgotten you and the solution was half-coded before I had to switch gears.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 5, 2009 at 2:30 pm
Thanks Bob. That was really nice of you to write back and taking the time to help me.
Michelle :kiss:
August 5, 2009 at 2:57 pm
Check this out. The basic plan is to populate a worktable with the last two orders for every serial#/categoryCode combination. RowNo = 1 is the most recent. The worktable is then joined to itself (which eliminates any singleton orders), and the where clause filters out the pairs where the previous order is over 5 days from the most recent. Play around with the creation dates to test it.
This could probably be tweaked by doing a SELECT INTO a temporary table, then building a clustered index after the table is populated. You could also write a query to check for prior rows and CROSS APPLY IT. But for an occasional query with less than a couple thousand rows to consider, this should run just fine. Let me know if it isn't working like you expect, or if you have any questions. 🙂
--Service Order ID | Service Order Created At | Serial | Service Order Categorization code
--3000000021 | 2009-03-15 20:34:10.000 | 110214 | A04 B22 C17 D00 E00
--3000000049 | 2009-03-15 21:00:49.000 | 110214 | A04 B22 C17 D00 E00
declare @sample table (soID bigint, soCreated datetime, serial int, CatCode varchar(30))
declare @workTbl table (soID bigint, soCreated datetime, serial int, CatCode varchar(30), rowNo int, primary key(serial,catCode,rowno))
insert into @sample
select 3000000001, '2009-03-09 20:34:10.000', 110213, 'A04 B22 C17 D00 E03' union all
select 3000000002, '2009-03-11 22:37:15.000', 100215, 'A04 B22 C16 D00 E08' union all
select 3000000021, '2009-03-15 20:34:10.000', 110214, 'A04 B22 C17 D00 E00' union all
select 3000000025, '2009-03-15 20:38:10.000', 100215, 'A04 B22 C16 D00 E08' union all
select 3000000049, '2009-03-15 21:00:49.000', 110214, 'A04 B22 C17 D00 E00'
;with cte1 as
(select *,row_number() over (partition by serial,catcode order by soCreated desc) as rowNo from @sample)
-- primary key is serial#, category code, row number base on date descending
insert into @worktbl
select *
from cte1
where rowNo = dateadd(day,-5,w1.soCreated)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 5, 2009 at 3:55 pm
Dude! That's freakin' awesome! I am definately most impressed....
Aw, I'm so happy now. I didn't even know how to conceptulize it and you knew exactly what I was after.
Thank you so much! You made my day. I need to do some studying now..
Michelle 😀
August 6, 2009 at 6:18 am
You're very welcome, Michelle. Happy I could help.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply