Premium Retail Services

The SQL Server Agent job Integrations - HOURLY is executing various stored procedures and one of them is validating and importing the QTrax reps and 4PL TSDE records.

In the event of an error, an email is sent to the users under the permissions group ID=72 named TSDE Import Emails ID=20 (the email is attached).

In order to receive the email with the error(s), we need to run the stored procedure

EXEC [ps_WEB_TSDE_ImportPayrollRecords] 
@JobID= 68030
 ,@ErrRecipient='paulg@premiumretail.com;'
 ,@ErrCC ='paulg@premiumretail.com;'

--->DUPLICATE ENTRIES ~ SAME REP SAME STORE SAME VISIT DATE ON SAME SERVICE ORDER

On the email, an attached text file with the service order(s) with the issue along with a description of the error will be provided.

Run the query to find the duplicate entry 

DECLARE @SOID INT=47873664

SELECT ANSWERS.SOID,svQuestions.AssessmentID, ANSWERS.QuestionID, svQuestions.SeqNO, svQuestions.Question, ANSWERS.Answer, ANSWERS.ResponseID

, svQuestions.QuestionType, ANSWERS.AnswerDate

FROM svQuestions INNER JOIN ANSWERS ON svQuestions.ID = ANSWERS.QuestionID

WHERE ANSWERS.SOID=@SOID

ORDER BY svQuestions.AssessmentID, svQuestions.SeqNo;

now update the ANSWERS table with the duplicate name like

UPDATE ANSWERS SET Answer = [Answer] + ' 2' WHERE SOID=47873664 AND QuestionID=5313815

Now if we run again the stored procedure

EXEC [ps_WEB_TSDE_ImportPayrollRecords] 

 @JobID= 68030

 @ErrRecipient='paulg@premiumretail.com;'

 @ErrCC ='paulg@premiumretail.com;'

this time, we should not receive any email with import issues and the records have been appended to the tables

DECLARE @INT_SOID INT=47873664

 SELECT INT_TSDEtable.QTsoid, INT_TSDEtable.QTjobID, INT_TSDEtable.StoreNo

 , INT_TSDEtable.QTdateWorked,INT_TSDEtable.QTrepID, INT_TSDEtable.RLName, INT_TSDEtable.RFName,INT_TSDEtable.[Hours]

 , INT_TSDEtable.[Role],INT_TSDEtable.Title, INT_TSDEtable.Company, INT_TSDEtable.Vendor

 , INT_TSDEtable.CheckIn,INT_TSDEtable.CheckOut, INT_TSDEtable.SystemCheckIn, INT_TSDEtable.SystemCheckOut

, INT_TSDEtable.ts_datetime,INT_TSDEtable.QTtoid, INT_TSDEtable.QTrepActive

, INT_TSDEtable.QTsiteID, INT_TSDEtable.QTstatus, INT_TSDEtable.autoID

FROM Integrations..RiteAid_TS INT_TSDEtable

WHERE INT_TSDEtable.QTsoid=@INT_SOID

ORDER BY INT_TSDEtable.StoreNo, INT_TSDEtable.QTdateWorked,INT_TSDEtable.RLName, INT_TSDEtable.RFName;

--->DUPLICATE ENTRIES ~ SAME REP SAME STORE SAME VISIT DATE ON D I F F E R E N T   SERVICE ORDERS

In the event that the above process is not returning any records, then run the two scripts below to find the duplicate records (same rep/same store/same day) on DIFFERENT service orders.

DECLARE @JobID INT=67917
-- DUPLICATE PREMIUM REPS
SELECT QTJobID,StoreNo,QTrepID,CheckIn,Count(autoID)
        FROM  RiteAID_TS
         WHERE  Company='Premium'
         AND  QTjobID=@JobID
        AND  (QTtoid IS NULL OR QTtoid <> -99)
         GROUP BY QTJobID,StoreNo,QTrepID,CheckIn
         HAVING COUNT(autoID)>1

-- DUPLICATE TEMP AGENTS
          SELECT QTJobID,StoreNo,QTagentID,RFName,RLName,CheckIn,COUNT(autoID)
         FROM  RiteAID_TS
         WHERE  Company<>'Premium' AND QTagentID>0 -- valid Agent was found above (invalid agents are already marked as error'
        AND    QTjobID=@JobID
        
         GROUP BY QTJobID,StoreNo,QTagentID,RFName,RLName,CheckIn
         HAVING COUNT(autoID)>1 AND MIN(QTtoid) IS NULL

SELECT * FROM Integrations..RiteAid_TS
WHERE QTjobID=67917 AND StoreNo=6613 AND CheckIn>='4/26/2022'