I'm trying to solve a problem in my analytical project whereas a representative gets a customer call/email and if needed they would ask for an advisor help (advise ring) and after that the representative would resolve the email/phone, henceforth sending a survey to the customer.

I want to track the first survey sent after the advise ring have been resolved only, but it does not matter if the customer opened the incident again and the representative resolved the incident without an advise ring, we would not want to count that survey.

Use case: A Representative gets an email > Pulls an advise ring > The advisor resolves it > Representative solves the email > Survey is sent (Gets counted). > Customer open the incident again > Rep solves the incident without an advise ring > Survey is sent (Does not get counted).

The problem here is that I can't track the subsequent dates for some reason.

Survey Table example

Incident_ID|    Survey_sent_date  | survey_id | response  | 
----------- --------- ----- ------------ ------------------
3324324    | 2022-03-03 16:23:02.1|     7     |           | 
3324324    | 2022-03-03 18:32:0.1 |    14     |     N     | 
3324324    | 2022-03-04 11:32:0.1 |     9     |     Y     |
3324324    | 2022-03-05 16:23:02.1|    17     |     Y     | 
3324324    | 2022-03-11 18:31:12.1|   134     |     N     | 
3324324    | 2022-03-11 20:35:12.1|   139     |     N     |
3324324    | 2022-03-15 19:45:26.0|    29     |     Y     |
3324324    | 2022-03-17 19:45:26.0|    229    |     Y     |

Advise ring table

Incident_ID|    advise_ring_resolve_date| advise_ring_id 
----------- --------- ----- ------------ --------------
3324324    | 2022-03-11 18:30:52.1       |    247     | 
3324324    | 2022-03-15 19:30:37.0       |    143     |

Expected Results:

Incident_ID|    Survey_sent_date   | survey_id | response  | 
----------- --------- ----- ------------ ------------------
   3324324 | 2022-03-11 18:31:12.1 | 134       | N         |  
   3324324 | 2022-03-15 19:45:26.0 | 29        | Y         |

I have tried to get the first rank of each date between each survey_sent_date and get the subsequent survey_id between the advise_ring_resolve_date and the survey_sent_date.

What I have in mind is the following:

On the advise_ring_id 247, get the survey sent on 2022-03-11 18:31:12.1 ONLY as it was immediately sent after the advise ring was resolved.

Similarly, the advise_ring_id 143 would get the survey sent on 2022-03-15 19:45:26.0 as it was immediately after the advise ring was resolved. and so on.

WITH surveys_sent as(
     SELECT DISTINCT 
     ss.incident_id,
     ss.survey_sent_date,
     ss.survey_id,
     ss.response,
     ROW_NUMBER OVER (PARTITION BY ss.survey_id ORDER BY ss.survey_sent_date) as req
     FROM SURVEY_TABLE SS
     WHERE ss.incident_id = '3324324'),

 advise_ring as(
     SELECT DISTINCT 
     ar.incident_id,
     ar.advise_ring_resolve_date,
     ar.advise_ring_id
     FROM ADVISE_TABLE
     WHERE ss.incient_id = '3324324')

  SELECT DISTINCT 
       finalar.case_id,
       finalss.survey_id,
       finalss.survey_sent_date
  FROM advise_ring finalar
  INNER JOIN surveys_sent finalss
  ON finalar.case_id = finalss.case_id AND finalar.advise_ring_resolve_date BETWEEN 
  finalar.advise_ring_resolve_date AND finalss.survey_sent_date
  WHERE finalss.req = 1;

I tried to full outer and left join the table, also tried to remove the ranking bit which did not help at all.

The full outer join provided me with a union like join where both of the columns are empty at their respective timestamp.

The desired result would having the count OR the details of the surveys sent after the first advise ring has been sent on that specific date.

Do you have any advise on the above?

Thanks!

EDIT:

I have tried to get the rank of the lowest difference between the advise ring date and the survey sent date, however it gives me duplicate values for some reason.

WITH base AS 
( 
  SELECT DISTINCT * 
  FROM ADVISE_TABLE
  WHERE INCIDENT_ID = 3324324
 )
SELECT *
FROM 
(
SELECT
base.advise_ring_id,
ss.survey_sent_date,
ss.survey_id,
base.advise_ring_resolve_date,
DATEDIFF(sec,ss.survey_sent_date,base.advise_ring_resolve_date) 
as time_diff
,ROW_NUMBER() over (PARTITION BY base.advise_ring_id ORDER BY 
DATEDIFF(sec,ss.survey_sent_date,base.advise_ring_resolve_date) 
DESC) as rk
FROM base
LEFT JOIN SURVEY_TABLE ss
on ss.incident_id = base.incident_id 
WHERE 
DATEDIFF(sec,ss.survey_sent_date,base.advise_ring_resolve_date) 
<=0
)
WHERE rk = 1

Result is from real table but different data:

base.advise_ring_id|Survey_sent_date  | survey_id | base.advise_ring_resolve_date| 
----------- --------- ----- ------------ ------------------
    123        | 2022-12-22 16:23:02.1|     7      |     2022-12-15 13:23:02.1        | 
    333        | 2022-01-03 06:55:25.1 |    14     |     2022-12-23 18:23:02.1     | 
    333        | 2022-01-03 06:55:25.1 |    14     |     2022-12-29 11:23:02.1     |

Basically from the result the top 2 only should be included not the third one, so by each date.

CodePudding user response：

I think something like this should work. Use LAG to get the previous survey date for survey. Then search for any Advise_Ring records between the current and previous survey date range.

WITH cte AS (
   SELECT *
          , LAG(Survey_Sent_Date, 1) OVER( 
              PARTITION BY Incident_Id ORDER BY Survey_Sent_Date
          ) AS Prev_Survey_Sent_Date
   FROM   Survey_Table
)

SELECT * 
FROM   cte s INNER JOIN Advise_Ring ar 
           ON s.Prev_Survey_Sent_Date < ar.advise_ring_resolve_date
           AND s.Survey_Sent_Date > ar.advise_ring_resolve_date

Results:

db<>fiddle here