simple query, complex calculation (for me anyway)

  • 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:

  • 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

  • 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 🙂

  • 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

  • Thanks Bob. That was really nice of you to write back and taking the time to help me.

    Michelle :kiss:

  • 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

  • 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 😀

  • 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