question

IDGO-6443 avatar image
0 Votes"
IDGO-6443 asked IDGO-6443 commented

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

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. 205178-1.png

  2. Take each row from ReportableResultCode from TempTable and find the Row from
    FROM [TestingOnly].[dbo].[ReportableResults] where ReportableResultCode = temptable ReportableResultCode
    205140-2.png

  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)
    205224-image.png

  4. Order by [CreatedOnUtc] take the Latest Date and Pervious Date
    Compare the Date and Time (Excluding Seconds) both should Match
    205179-image.png

  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 )

205150-image.png


sql-server-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-integration-services
1.png (71.2 KiB)
2.png (76.3 KiB)
image.png (395.3 KiB)
image.png (383.9 KiB)
image.png (668.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Output should be having new column with date and time including seconds for each row

0 Votes 0 ·

1 Answer

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered IDGO-6443 commented

Hi @IDGO-6443
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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I need [ReportableResultId] as well to Temp Table

205503-image.png


0 Votes 0 ·
image.png (376.3 KiB)

Hi @IDGO-6443
I edited my answer above, please check.

1 Vote 1 ·
IDGO-6443 avatar image IDGO-6443 LiHongMSFT-3908 ·

@LiHongMSFT-3908 thanks mate..this is working fine

0 Votes 0 ·