The reason that you are getting duplicates is because your source data is all the same. If you want to prevent duplicates in your results, then you need to find...

This should do the trick

CREATE TABLE #Company (CompanyID INT, CompanyName VARCHAR (100));
CREATE TABLE #KVPairs (CompanyID INT, KeyColumn VARCHAR (100), ValueColumn VARCHAR (100));

INSERT...

You can try this

INSERT INTO DCert (stateID, custnumber, lastname, firstname, mailAddress)
WITH cteData AS (
SELECT
  DC.[State Id] as stateID
,...

Does this give you the required output?

-- This date is used so that SQL does not get an overflow error when calculating a large time...

It seems you have an issue with duplicate records for PartNum, DueDate, Quantity).

This should do the trick

WITH cteBaseData AS (
SELECT
  PartNum

Reported as spam

This sounds like a running totals issue.

Something along the lines of .....
SELECT as many random questions as needed
WHERE QuestionType = 'Short Note'
AND QuestionComplexity...

What about an option to MARK ALL AS READ.
Then when looking at the UNREAD tab, we can start with fresh data.

To select the records without deleting the duplicates

WITH cteBaseData AS (
SELECT
  cr.ClientRef
  , cr.LinkedClientRef
  , rn = ROW_NUMBER() OVER (ORDER...

This will remove the duplicates

-- Create a table to hold the data
CREATE TABLE #Links (
  ClientRef   INT
, LinkedClientRef INT
);

Your expected results do not match your supplied sample data.
For this reason, I am unable to create a tested result.  However, the code below, should get ypu started.