Trying to Determine the Ticket Title for Orders

Transact-SQL
1

Hi All,

Hope you are doing well!... I am trying to get the exact ticket raised for each of the orders in a company which does online sales...The tickets are raised by the customers due to the issues faced by them with regard to the order...One table has the order and the corresponding tickets raised for the order ... Another table has the order and whether the order is a pre-sell -Yes/no..Now I have to follow the following rules to determine the final table containing the order and the exact title..

  1. In Table 1 (##input1) I have to ignore the following ticket titles while trying to determine the exact ticket title for the order..(ticket titles that contain the following should be ignored)...Shipping Carrier Package Claim, Item Verification, Internal - Return Credit Queue, Purchase Order Vendor Transfer, Purchase Order High Shipping Rate, Amazon A-To-Z Guarantee Claims, Item Verification -, Chargeback

  2. After ignoring the above ticket titles for any order ID I have to take the latest ticket title (most recent date-ts) for every Order ID

  3. After that I have to consider the following ticket titles as Backorder (ticket title)

Cancellation - Expected Beyond 30 Days
Cancellation - Extended Backorder
Cancellation - Backorder
Cancellation - Delayed Shipment
Cancellation - Expected Late
Cancellation - Past 30 days

Also other ticket titles that have cancellation - discontinued as discontinued or anything with Order Issues - as just Order Issues ..See mapping below:

Cancellation - Buyer's Remorse Buyer's Remorse

Cancellation - Discontinued Discontinued
Cancellation - Listing Error Listing Error
Cancellation - NOFRAUD verification status fail Fraud
Cancellation - OJ Requested OJ Requested
Order Issues - Order Issues

  1. After that if the ticket title from ##Input 1 after running through the above steps comes to be either Buyers Remorse or Order Issues then it remains the same as the final ticket title for that order..If the ticket title from ## input1 is other than Buyers remorse or Order Issues then if the presell from ##Input2 table is yes then it should be presell else the ##input1 ticket title remains for the order....Can you please help me here...The input and the output tables below..

##Input1

Create table ##input1
(orderid int,
ticket_title varchar(100),
ts DATETIME2)

insert into ##input1 values
('4539463','Chargeback','2020-07-09 21:20:56:05'),
('4539463','Cancellation - Buyers Remorse','2020-07-03 18:59:02:03'),
('4539463','Order Issues - Damaged - Complete Item','2020-05-27 15:30:05:02'),
('4579758','Chargeback','2020-07-07 20:13:12:06'),
('4579758','Amazon A-To-Z Guarantee Claims','2020-07-06 14:59:04:07'),
('4579758','Order Issues - Defective - Parts','2020-06-11 05:15:02:09'),
('4612474','Chargeback','2020-07-02 20:45:20:05'),
('4612474','Customer Backorder Notification','2020-05-27 05:02:12:04'),
('4620316','Chargeback','2020-07-09 21:14:05:08'),
('4620316','Amazon A-To-Z Guarantee Claims','2020-07-06 22:59:11:07'),
('4650014','Chargeback','2020-07-06 20:23:20:09'),
('4650014','Amazon A-To-Z Guarantee Claims','2020-07-05 14:58:44:08'),
('4650014','Order Issues - Defective - Parts','2020-06-21 16:00:03:07'),
('4663584','Chargeback','2020-07-04 20:15:15:09'),
('4663584','Amazon A-To-Z Guarantee Claims','2020-07-03 14:58:50:08'),
('4663584','Order Issues - Defective - Parts','2020-06-23 12:30:03:07'),
('4665480','Chargeback','2020-07-03 20:04:04:09'),
('4665480','Amazon A-To-Z Guarantee Claims','2020-07-02 14:59:00:08'),
('4665480','Order Issues - Remorse','2020-06-28 21:45:04:07'),
('4665656','Cancellation - Expected Late','2020-07-09 05:05:03:06'),
('4667841','Shipping Carrier Package Claim - 977225711067255','2020-07-09 12:59:35:07'),
('4667841','Order Issues - Damaged','2020-07-08 17:30:03:06'),
('4675796','Cancellation - Extended Backorder','2020-07-09 09:36:58:09'),
('4675796','Shipping Carrier Package Claim - 394025727836','2020-07-08 15:13:26:08'),
('4675796','Shipping Carrier Package Claim - 394025727620','2020-07-08 15:13:26:07'),
('4675796','Shipping Carrier Package Claim - 394025727295','2020-07-08 15:13:26:06'),
('4675796','Shipping Carrier Package Claim - 394025728979','2020-07-08 14:42:41:05'),
('4675796','Order Issues - Not Delivered - Not Received','2020-07-08 13:12:47:04')

## Input2

create table ##input2
(orderid int,
presellyesno varchar(10))

insert into ##input2 values
('4539463','yes '),
('4579758','no'),
('4612474','yes'),
('4620316','yes'),
('4650014','no'),
('4663584','yes'),
('4665480','yes'),
('4665656','yes'),
('4667841','no'),
('4675796','yes')

##Output

Create table ##output
(orderid int,
ticket_title varchar(20)
)

insert into ##output values
('4539463','Buyers Remorse '),
('4579758','Order Issues'),
('4612474','Presell'),
('4650014','Order Issues'),
('4663584','Order Issues'),
('4665480','Order Issues'),
('4665656','Presell'),
('4667841','Order Issues'),
('4675796','Backorder'),
('4675796','Order Issues')

The query that I tried

select o.orderid ,
case when o.ticket_title like '%Buyers Remorse%' then 'Buyers Remorse'
when o.ticket_title Like '%Order Issues%' then 'Order Issues'
when o.ticket_title like '%Shipping Carrier Package Claim%' then rn+1
when o.ticket_title like '%Chargeback%' then rn+1
when o.ticket_title like '%Item Verification%' then rn+1
when o.ticket_title like '%Internal - Return Credit Queue%' then rn+1
when o.ticket_title like '%Purchase Order Vendor Transfer%' then rn+1
when o.ticket_title like '%Purchase Order High Shipping Rate%' then rn+1
when o.ticket_title like '%Amazon A-To-Z Guarantee Claims%' then rn+1
when b.presellyesno='yes' and o.ticket_title not like '%Buyers Remorse%' then 'pre-sell'
when b.presellyesno='yes' and o.ticket_title not like '%Order Issues%' then 'pre-sell'
when o.ticket_title='Cancellation - Discontinued' then 'Discontinued'
when o.ticket_title='Cancellation - Listing Error' then 'Listing Error'
when o.ticket_title='Cancellation - NOFRAUD verification status fail' then 'Fraud'
when o.ticket_title='Cancellation - OJ Requested' then 'OJ Requested'
else o.ticket_title
end as ticket_title
from
(select a.orderid,ticket_title,row_number() over(partition by a.orderid order by ts desc) as rn
from ##input1 a (nolock)
)o
join ##input2 b (nolock)
on o.orderid=b.orderid

Thanks,
Arun

2

I do not understand all the rules but this should get you started:

WITH IgnoreTitles
AS
(
	SELECT V.ticket_title
	FROM
	(
		VALUES
		('Shipping Carrier Package Claim')
		,('Item Verification')
		,('Internal - Return Credit Queue')
		,('Purchase Order Vendor Transfer')
		,('Purchase Order High Shipping Rate')
		,('Amazon A-To-Z Guarantee Claims')
		,('Item Verification -')
		,('Chargeback')
	) V (ticket_title)
)
,TitleMapping
AS
(
	SELECT V.presellyesno, V.not_like, V.org_title, V.ticket_title
	FROM
	(
		VALUES
		('yes', 'Y', '%Buyers Remorse%', 'pre-sell')
		,('yes', 'Y', '%Order Issues%', 'pre-sell')
		,('%', 'N', '%Buyers Remorse%', 'Buyers Remorse')
		,('%', 'N', '%Order Issues%', 'Order Issues')

		-- 4675796 has Backorder and Order Issues in #output
		-- 4665656 seems to be a BackOrder but result is Presell in #output
		-- Maybe backorders should be dealt with separately
		,('%', 'N', 'Cancellation - Expected Beyond 30 Days', 'Backorder')
		,('%', 'N', 'Cancellation - Extended Backorder', 'Backorder')
		,('%', 'N', 'Cancellation - Backorder', 'Backorder')
		,('%', 'N', 'Cancellation - Delayed Shipment', 'Backorder')
		,('%', 'N', 'Cancellation - Expected Late', 'Backorder')
		,('%', 'N', 'Cancellation - Past 30 days', 'Backorder')

		,('%', 'N', 'Cancellation - Discontinued', 'Discontinued')
		,('%', 'N', 'Cancellation - Listing Error', 'Listing Error')
		,('%', 'N', 'Cancellation - NOFRAUD verification status fail', 'Fraud')
		,('%', 'N', 'Cancellation - OJ Requested', 'OJ Requested')

	) V (presellyesno, not_like, org_title, ticket_title)
)
,Relevant_Titles
AS
(
	SELECT I.orderid, COALESCE(M.ticket_title, I.ticket_title) AS ticket_title
		--,P.presellyesno, M.not_like, M.org_title, I.ts
		,ROW_NUMBER() OVER
		(
			PARTITION BY I.orderid
			ORDER BY I.ts DESC
				,CASE WHEN P.presellyesno = 'yes' THEN 2 ELSE 1 END
				,M.not_like
		) AS rn
	FROM ##input1 I
		JOIN ##input2 P
			ON I.orderid = P.orderid
		LEFT JOIN TitleMapping M
			ON P.presellyesno LIKE M.presellyesno
				AND
				(
					(COALESCE(M.not_like , '') = 'Y' AND I.ticket_title NOT LIKE M.org_title)
					OR
					(COALESCE(M.not_like , '') <> 'Y' AND I.ticket_title LIKE M.org_title)
				)
	WHERE NOT EXISTS
	(
		SELECT 1
		FROM IgnoreTitles T
		WHERE I.ticket_title LIKE '%' + T.ticket_title + '%'
	)
)
SELECT T.orderid, T.ticket_title
FROM Relevant_Titles T
WHERE T.rn = 1;
3

hi arun

i tried to do something .. please see
; with cte as
(
select * from input1
where
(ticket_title not like '%Shipping Carrier Package Claim%') AND
(ticket_title not like '%Item Verification%') AND
(ticket_title not like '%Internal - Return Credit Queue%' )AND
(ticket_title not like '%Purchase ANDder VendAND Transfer%' ) AND
(ticket_title not like '%Purchase ANDder High Shipping Rate%') AND
(ticket_title not like '%Amazon A-To-Z Guarantee Claims%' )AND
(ticket_title not like '%Item Verification%' ) AND
(ticket_title not like '%Chargeback%' )
) , cte_rn as
(
select ROW_NUMBER( ) over(partition by orderid order by ts desc) as rn ,* from cte
) , cte_get_max as
(
select * from cte_rn where rn =1
) , cte_put_title as
(
select 'Back Order' as ORG,'Cancellation - Expected Beyond 30 Days' as TITLE union all
select 'Back Order','Cancellation - Extended Backorder' union all
select 'Back Order','Cancellation - Backorder' union all
select 'Back Order','Cancellation - Delayed Shipment' union all
select 'Back Order','Cancellation - Expected Late' union all
select 'Back Order','Cancellation - Past 30 days' union all
select 'Buyers Remorse', 'Cancellation - Buyers Remorse' union all
select 'Discontinued', 'Cancellation - Discontinued' union all
select 'Listing Error' , 'Cancellation - Listing Error' union all
select 'Fraud', 'Cancellation - NOFRAUD verification status fail' union all
select 'OJ Requested', 'Cancellation - OJ Requested' union all
select 'Order Issues', 'Order Issues - Order Issues'
)
select * from cte_get_max A JOIN INPUT2 B ON A.orderid = B.ORDERID

image
image781×213 8.44 KB

pleaase explain what you tried and where it was going wrong ..

if you can show excel sheet explanations .. its easy for us to understand and give solution !!

examples ..

Table_Output_Required_Result_V2
Table_Output_Required_Result_V21098×486 32.2 KB
Excel Explain SQL Team
Excel Explain SQL Team1920×1080 23.7 KB

4

Is this a third party ticketing system your company acquired or is it home grown?

Are you in a position to modify it? Do the customers themselves add the ticket or they call and your folks create the ticket.
From what I can sense, the title is being used as a ticket categorization field when you should really have a list of canned ticket categories.
Trying to parse a title field to figure out how to categorize tickets is going to come to bite you. People make mistakes when typing orders can be mispelled odors :smile: