Need SQL Query to get Date and Time Column from another Table with certain Condition

Question

Need SQL Query to get Date and Time Column from another Table with certain Condition

DB Backup
https://1drv.ms/u/s!Ag8QU6ar3yRugYxRC-wmRlZ3zB_qyA?e=qjKXfJ

    select S.SampleCode,tp.TestPRepetitionCode,tp.TestCode,rr.ParameterCode,rr.ReportableResultCode,rr.IsCalculated  
      INTO #tempCustomer   
    from Samples S join SampleFractions SF  
    on S.SampleId = SF.SampleId  
    join TestPRepetitions TP  
    on TP.SampleFractionId = SF.SampleFractionId  
    join ReportableResults RR  
    on RR.TestPRepetitionId = tp.TestPRepetitionId  
    group by s.SampleCode ,S.SampleCode,tp.TestPRepetitionCode,tp.TestCode,rr.ParameterCode,rr.ReportableResultCode,rr.IsCalculated having count(*) > 1  

Above Code is Working, But Need Few Validation

1. 
2. Take each row from ReportableResultCode from TempTable and find the Row from
   FROM [TestingOnly].[dbo].[ReportableResults] where ReportableResultCode = temptable ReportableResultCode
   
3. [ReportableResultCode] should be always Greater then 1 in [TestingOnly].[dbo].[ReportableResults]
   If [ReportableResultCode] is 1 or less then 1 Remove the row from TempTable(Query Table)
   
   4. Order by [CreatedOnUtc] take the Latest Date and Pervious Date
      Compare the Date and Time (Excluding Seconds) both should Match
      
   5. If Latest and Previous [CreatedOnUtc] matchs then Add New Column CreatedOnUtc
      in TempTable and Take the Latest [CreatedOnUtc] and Add in TempTable Beside ReportableResultCode (including Seconds )

Answer 1

Hi @Indudhar Gowda
Check this:

--Validation 1  
;WITH CTE AS  
(  
 SELECT T.RepotableResultCode  
 FROM #tempCustomer T LEFT JOIN [TestingOnly].[dbo].[ReportableResults] R ON T.RepotableResultCode=R.RepotableResultCode  
 GROUP BY T.RepotableResultCode  
 HAVING COUNT(T.RepotableResultCode)=1  
)  
DELETE FROM #tempCustomer WHERE RepotableResultCode IN(SELECT RepotableResultCode FROM CTE)  
  
--Validation 2  
ALTER TABLE #tempCustomer ADD CreatedOnUtc DATETIME;  
ALTER TABLE #tempCustomer ADD RepotableResultID VARCHAR(50);  
;WITH CTE AS  
(  
 SELECT R.RepotableResultID,T.RepotableResultCode,R.CreatedOnUtc,T.CreatedOnUtc AS Temp_CreatedOnUtc,T.RepotableResultID AS Temp_RepotableResultID  
       ,ROW_NUMBER()OVER(PARTITION BY T.RepotableResultCode ORDER BY R.CreatedOnUtc DESC) AS RNum  
       ,DATEDIFF(MINUTE,R.CreatedOnUtc,LEAD(R.CreatedOnUtc)OVER(PARTITION BY T.RepotableResultCode ORDER BY R.CreatedOnUtc DESC)) AS TIME_COMPARE  
 FROM #tempCustomer T LEFT JOIN [TestingOnly].[dbo].[ReportableResults] R ON T.RepotableResultCode=R.RepotableResultCode  
)--SELECT New_Column,CreatedOnUtc FROM CTE WHERE RNum=1 AND TIME_COMPARE=0  
UPDATE CTE   
SET Temp_CreatedOnUtc = CreatedOnUtc, Temp_RepotableResultID = RepotableResultID  
WHERE RNum=1 AND TIME_COMPARE=0  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.