First Contact Resolution-CodePudding

I am using Microsoft SQL Server Management Studio

I have a table which contains a unique customerId, date when the contact was made and reason why the contact was made.

customerId
,DateOfContact
,ContactReason

I need to create two Yes (Y) or No (N) columns.

Column 1 named 7DayContact
Column 2 named 7DaySameContact

Column 1 should provide me with a Y or N if the same customerId had another contact in the previous 7 days (interval of 7 days)

Column 2 should provide me with a Y or N if the same customerId had another contact in the previous 7 days with the same contactReason.

How should I go about it?

I didn't manage to do anything.

CodePudding user response：

Please note that you have two different tags mysql <> sql-server.

I used SQL server coding, best to use the LAG function:

SELECT customerId, DateOfContact, ContactReason,
  CASE WHEN LAG(DateOfContact, 1) OVER (PARTITION BY customerId ORDER BY DateOfContact) BETWEEN DateOfContact - INTERVAL 7 DAY AND DateOfContact THEN 'Y' ELSE 'N' END AS 7DayContact,
  CASE WHEN LAG(ContactReason, 1) OVER (PARTITION BY customerId ORDER BY DateOfContact) = ContactReason THEN 'Y' ELSE 'N' END AS 7DaySameContact
FROM yourTable

CodePudding user response：

I am using Microsoft SQL Server Management Studio.

What I did for the first scenario is the below query, however, all results are showing as 'N' whilst there are results that should show as 'Y'.

Basically, I want to look at the con.CustomerId, go back 7 days and check whether the same con.CustomerId shows up. If the same con.CustomerID shows up, then give 'Y' else 'N'.

SELECT 
      con.[CustomerID]
      ,con.[ContactDate]
      ,con.[ContactReason1]
      ,con.[ContactReason2]
      ,con.[ContactReason3]
      ,CASE 
            WHEN LAG(con.[ContactDate], 1) OVER (PARTITION BY con.[CustomerID] ORDER BY con.[ContactDate]) BETWEEN con.[ContactDate] AND DATEADD(DAY, -7, con.[ContactDate])
            THEN 'Y' ELSE 'N' 
            END AS '7DayContact'


  FROM [DWH_Unknown1].[unknown2].[unknown3] con

Order By disp.DispositionDate DESC

For the second scenario I want to look at the con.CustomerId and con.ContactReason2, go back 7 days and check whether the same con.CustomerId having the same con.ContactReason2 shows up. If the same con.CustomerID having the same con.ContactReason2 shows up, then give 'Y' else 'N'.