Cycle time based on assigned user from a single date field
-
Hi
I have a requirement where I have to find the cycle time for each user that they have been assigned to the ticket. If you see the sample data, I need to get 67 days for JB user for the ticket CAS-22596-Y1Q7G2 and null or 0 for JP user for the ticket CAS-23895-X5D4T6. Please find the sample data in the attachment.
CREATE TABLE Ticket
(`hss03_CaseNumber` varchar(16), `hss03_AssignedDate` datetime, `hss03_AssignedFromName` varchar(5), `hss03_AssignedToName` varchar(2))
;
INSERT INTO Ticket
(`hss03_CaseNumber`, `hss03_AssignedDate`, `hss03_AssignedFromName`, `hss03_AssignedToName`)
VALUES
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'KM'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'IP'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'ADMIN', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-14 00:00:00', 'SG', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'KS'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'SH'),
('CAS-23895-X5D4T6', '2018-08-20 00:00:00', 'SH', 'JP')
;IP assigned the ticket to JB on 2018/03/26 and JB assigned back to himself on 2018/05/30 which is 65 days and again SG assigned the ticket to JB on 2018/08/14 to SB assigned back to himself on 2018/08/16, thats 2 days.
So total no of days he worked on the ticket is 67 for the ticket CAS-22596-Y1Q7G2In the second ticket, SH assigned ticket to JP on 2018/08/20 and he didn't assign it back to anyone or himself, that means he is still working on it. So the cycle time should show 0 or null for the ticket CAS-23895-X5D4T6
-
Most people are unwilling to open a file posted to the Internet by a random stranger. Please post sample data as outlined in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thank you Drew. I have provided the code now.
-
And what are the rules you are using to derive those numbers for a specific user for a specific ticket?
-
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'KM'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'IP'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'ADMIN', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-14 00:00:00', 'SG', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'KS'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'SH'),
('CAS-23895-X5D4T6', '2018-08-20 00:00:00', 'SH', 'JP')IP assigned the ticket to JB on 2018/03/26 and JB assigned back to himself on 2018/05/30 which is 65 days and again SG assigned the ticket to JB on 2018/08/14 to SB assigned back to himself on 2018/08/16, thats 2 days.
So total no of days he worked on the ticket is 67 for the ticket CAS-22596-Y1Q7G2In the second ticket, SH assigned ticket to JP on 2018/08/20 and he didn't assign it back to anyone or himself, that means he is still working on it. So the cycle time should show 0 or null for the ticket CAS-23895-X5D4T6
-
And who was working on the ticket from 2018-05-30 to 2018-08-07? I would assume that it was JB, because it hasn't been reassigned to anyone else. Why aren't those days counted?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
yes, that's the problem I am getting. admin or admin privileged users can assign the ticket to anybody even its already been assigned to someone. In this case, though it was assigned JB on 2018-05-30 00:00:00, the ticket was assigned to SG by IP on 2018-08-07 till 2018-08-14. that means the ticket was not with JB, so we should not calculate those days.
- bonk6399 - Wednesday, September 19, 2018 7:58 AM
As has been pointed out, although in perhaps less detail, your data has a problem. You have an apparent point in time when IP was assigned the first ticket in your data, and yet, there's no record of it taking place. Thus you are NOT going to get an accurate result. In order for IP to have assigned it to SG, IP would have had to been assigned the ticket first, but again, no record of this. You would need to either be able to identify this problem and not calculate a result for this ticket while identifying an error with it, or, you need to make some kind of assumption. I feel the latter is kind of dangerous and may lead to seriously distorted results.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - bonk6399 - Wednesday, September 19, 2018 12:41 PM
That doesn't answer the question of who was working on it until the reassignment. I'm assuming that JB was, but you're not counting that time.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - sgmunson - Wednesday, September 19, 2018 2:00 PM
You are right Steve. There was no record of JB assigned the ticket back to IP or whoever the next person assigning the ticket someone while JB was still holding the ticket. So I manually inserted a record for this case and it is working fine with below Query. But I am not sure how to write that in code.
CREATE TABLE dbo.Ticket
(hss03_CaseNumber varchar(20), hss03_AssignedDate date, hss03_AssignedFromName varchar(20), hss03_AssignedToName varchar(20))
INSERT INTO Ticket
(hss03_CaseNumber, hss03_AssignedDate, hss03_AssignedFromName,hss03_AssignedToName)
VALUES
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'KM'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'KM', 'IP'),
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'IP'), -------------------------Inserted a record here -------------------------
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'ADMIN', 'SG'),
('CAS-22596-Y1Q7G2', '2018-08-14 00:00:00', 'SG', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-08-16 00:00:00', 'JB', 'JB'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'KS'),
('CAS-23895-X5D4T6', '2018-08-17 00:00:00', 'KS', 'SH'),
('CAS-23895-X5D4T6', '2018-08-20 00:00:00', 'SH', 'JP')
;
SELECT hss03_CaseNumber,hss03_AssignedToName,SUM([Days]) AS [Duration]
FROM
(
Select hss03_CaseNumber,hss03_AssignedToName,
DATEDIFF(Day, hss03_AssignedDate, (Select MIN(hss03_AssignedDate)
from dbo.Ticket t2
where t2.hss03_AssignedFromName = t1.hss03_AssignedToName
and t2.hss03_AssignedDate >= t1.hss03_AssignedDate
)) as [Days]
from dbo.Ticket t1
where hss03_CaseNumber like 'CAS-22596%'
) AS InnerQuery
GROUP BY hss03_CaseNumber,hss03_AssignedToName
order by hss03_CaseNumber
Thanks,
Naveen -
WITH DaysWorked AS
(
SELECT hss03_CaseNumber, hss03_AssignedToName, DATEDIFF(DAY, hss03_AssignedDate, LEAD(hss03_AssignedDate, 1, hss03_AssignedDate) OVER(PARTITION BY hss03_CaseNumber ORDER BY hss03_AssignedDate)) AS DaysWorked
FROM #Ticket
)
SELECT hss03_CaseNumber, hss03_AssignedToName, SUM(DaysWorked) AS DaysWorked
FROM DaysWorked
WHERE hss03_AssignedToName IN ('JB', 'JP')
GROUP BY hss03_CaseNumber, hss03_AssignedToNameDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, September 20, 2018 7:49 AM
Drew,
It's already working with my query also,
I guess my requirement got changed. Now, How do I insert the record dynamically when the value for assigned_ from and assigned_to is same and the same person need to assign to next very next person in the ticket. I am not sure how to explain this but if you see below , JB assigned the ticket IP so that he was able to release the ticket to IP and IP can assign the ticket to someone else.
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'IP'), -------------------------Inserted a record here -------------------------
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
- bonk6399 - Thursday, September 20, 2018 10:07 AMdrew.allen - Thursday, September 20, 2018 7:49 AM
Drew,
It's already working with my query also,
I guess my requirement got changed. Now, How do I insert the record dynamically when the value for assigned_ from and assigned_to is same and the same person need to assign to next very next person in the ticket. I am not sure how to explain this but if you see below , JB assigned the ticket IP so that he was able to release the ticket to IP and IP can assign the ticket to someone else.
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'IP'), -------------------------Inserted a record here -------------------------
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
You're query requires reading the table twice, whereas mine does not.
You also have data quality issues that make it impossible to answer this question. Specifically, there are two entries on 2018-08-07 where SG is assigned the task: once from IP and once from Admin. We cannot know which path is correct based on the information given JB > IP > SG > Admin > SG, JB > Admin > SG > IP > SG, or some other path.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - bonk6399 - Thursday, September 20, 2018 10:07 AMdrew.allen - Thursday, September 20, 2018 7:49 AM
Drew,
It's already working with my query also,
I guess my requirement got changed. Now, How do I insert the record dynamically when the value for assigned_ from and assigned_to is same and the same person need to assign to next very next person in the ticket. I am not sure how to explain this but if you see below , JB assigned the ticket IP so that he was able to release the ticket to IP and IP can assign the ticket to someone else.
('CAS-22596-Y1Q7G2', '2018-03-26 00:00:00', 'IP', 'JB'),('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'JB'),
('CAS-22596-Y1Q7G2', '2018-05-30 00:00:00', 'JB', 'IP'), -------------------------Inserted a record here -------------------------
('CAS-22596-Y1Q7G2', '2018-08-07 00:00:00', 'IP', 'SG'),
There's an inherent problem with correcting the data that way. One, you wipe out any validity to JB's time with the ticket, and two, it's entirely possible that someone is updating this table OUTSIDE of the application, and reassigning tickets that way, which really ought to be fixed before you ever start trying to write a solution or insert data that isn't going to reflect reality. Otherwise, you're in for an ever increasing quantity of garbage in your database. Trust me when I tell you that's something you don't want to own.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply