For my source table I have all the “Contact history for customers” in the correct order (ascending starting from earliest date at the top). What I need to do is group rows where the contacts are related to the same issue. The way to determine whether a issue is related to each other is based on the time. If the time difference <2 weeks then the two cases are related.
A 1 in the 'Pass or Fail' column highlights whether the case was fully resolved. A 0 indicates that the call from the below row is related to the same issue.
I need to produce a summary of this table to show where a customer has contacted multiple times against the same query
I am using SQL Server 2012
Here is an example of my source data. In my full table I will have multiple customers. You can see I have colour coded to show that there are a total of 9 contacts but just 4 different issues. There will be no fixed number of contacts or issues for each customer
| Customer_ID | start_date | Next_Date | Time_Difference_(Mins) | total_contacts | Pass_or_fail | NEXT_Contact_same_day |
|---|---|---|---|---|---|---|
| 12345 | 03/03/2022 | 05/05/2022 | 90794 | 1 | 1 | 0 |
| 12345 | 05/05/2022 | 05/05/2022 | 3 | 1 | 0 | 1 |
| 12345 | 05/05/2022 | 06/05/2022 | 1409 | 1 | 0 | 0 |
| 12345 | 06/05/2022 | 06/05/2022 | 2 | 1 | 0 | 1 |
| 12345 | 06/05/2022 | 06/05/2022 | 11 | 1 | 0 | 1 |
| 12345 | 06/05/2022 | 20/11/2022 | 284973 | 1 | 1 | 0 |
| 12345 | 20/11/2022 | 05/01/2023 | 66242 | 1 | 1 | 0 |
| 12345 | 05/01/2023 | 05/01/2023 | 178 | 1 | 0 | 1 |
| 12345 | 05/01/2023 | NULL | NULL | 1 | 1 | 0 |
I have tried using DENSE_RANK, ROW_NUMBER, LAG&LEAD on the Pass or Fail column, as well as MAX & MIN dates for the start and next dates, but am not having any luck.
E.g.
select Customer ID, sum(total_contacts), min(Start date), max(Next Date)
from (select *,
sum(case when Pass or fail = 0 then 1 else 0 end) over (order by Customer ID) as grp
from #Mastery
) #Mastery
where total_contacts = '1'
group by Customer ID, grp;-- this returns all contacts
select Customer ID, sum(total_contacts) AS total_Contacts, SUM (Pass or fail) AS Pass#, min(Start date) AS Mindate, max(Next Date) AS Maxdate,
ROW_NUMBER () over(partition by Customer ID order by Pass or fail) as Contactranking
from #Mastery2
group by Customer ID, Pass or fail-- this just returns the sum of passes and fails
This is the output I am looking for. You can see that against the same customer ID I have 4 rows, one row represents each issue. For each Row it should show the number of calls against the issue. To quantify the number of calls related to the same issue you have to use the 'Pass or Fail' column
1条答案
按热度按时间fjaof16o1#
This is a gaps and island style problem where you have the right idea about the grouping, but there are some details that makes this a bit tricker. Here's a potential solution:
First and foremost, the problem is that you have to get the previous
passorfailcolumn, because usually when you create a gaps and islands sum, you want to keep the value at 0, until a new group arrives, while here, 1 creates a new group. Therefore I useLAG(passorfail, 1, 0).The sorting here is also a bit tricky, because you have multiple duplicates dates. For this, I create the tiebreaker by
ISNULL(CONVERT(DATETIME, NextDate, 103), '99991231')so the open dates goes to the end. It also simplifies the MAX handling later.The rest is nothing special, except we flip back the date to null with
NULLIF(MAX(nextDate), '99991231')Just a few notes: you should really provide the test data in table format. Also, DD/MM/YYYY is a very unhandy date style to work with, YYYYMMDD is much nicer.