Script that will take a field from one table and see if there has been an entry in another table containing that field

Question

I have an issue where we are monitoring uploads into the database and alerting if a file takes more than 10 minutes to upload.

However we are finding that a majority of the alerts are happening because the file size is such that it is taking more than 10 minutes to upload.

So what I am hoping that someone can help me with is a script that will take the ID from table A (unique per upload) and look in the second table for anything in the last 10 minutes that contains that ID in column B of the second table?

So Table A, Upload ID 121212 created > now - 10 minutes

In Table B has there been an entry into the table in the last 10 minutes containing 121212 in column UploadFile.

If No then return positive, if yes then end

What I thought would be a simple task I am failing miserably at creating

Answer 1

You can have query like below. Use INNER JOIN with condition for UploadID and B's Created between A's Created and 10 minutes from A's Created.

Rererence - DATEADD (Transact-SQL)

IF EXISTS(SELECT 1 
            FROM A
            INNER JOIN B
                ON B.UploadFile LIKE CONCAT('%:', A.UploadID)
                    AND B.Created BETWEEN A.Created AND DATEADD(MINUTE, 10, A.Created)
            WHERE A.UploadID = 121212)
BEGIN
    RETURN 0;
END
ELSE
BEGIN
    RETURN 1;
END