Overview

You may want to know, from a list of customers and a date since when contract renewal letters should have been sent, for whom the letters were sent and the other for whom the letters were not sent and only emails were sent.

Solution

Create a support ticket and share the list of customers as a spreadsheet in the support ticket and the date. Please mention the Customer IDs in the spreadsheet.

<supportagent>

If the client has provided a list with CustIDs and a date and wants them partitioned into two lists (for whom the letters were sent and for whom the letters were not sent and only emails were sent), then follow the below steps.

1. Login to the Jump server 10.185.40.141.
2. Open Microsoft SSMS and connect to aes.cons.com.
3. Select New Query in the top bar.
4. Select the client-specific database.
5. Prepare the list of CustIDs as a comma-separated list, eg. 918745, 918746, 918747, 918748.
6. Execute this query to export the information for whom the letters were NOT sent

   select distinct(Letter.CustID), Customer.CustName, Customer.CustNo, Letter.CreateDate, LetterType.Description from Letter
   join LetterType on LetterType.LetterTypeId = Letter.LetterTypeID
   join Customer on Letter.CustID = Customer.CustID
   where
     Letter.PrintStatusID <> 4 and
     Letter.CustID in (<comma separated list of CustIDs from Step 5>) and
     LetterType.Description like '%contractrenewal%' and
     Letter.CreateDate > '<date provided by client as YYYY-MM-DD>'

7. Execute this query to export the information for whom the letters were sent

   select distinct(Letter.CustID), Customer.CustName, Customer.CustNo, Letter.CreateDate, LetterType.Description from Letter
   join LetterType on LetterType.LetterTypeId = Letter.LetterTypeID
   join Customer on Letter.CustID = Customer.CustID
   where
     Letter.PrintStatusID = 4 and
     Letter.CustID in (<comma separated list of CustIDs from Step 5>) and
     LetterType.Description like '%contractrenewal%' and
     Letter.CreateDate > '<date provided by client as YYYY-MM-DD>'

</supportagent>