SQL join query with WHERE

Question

Hello,

I have two tables - Files and Claims. I want to select all rows from Files and Claims tables where Claim status is "Pending" and records that are more than 7 days old. To find more than 7 days old, I use two fields SubmittedOn and UpdatedOn. If UpdatedOn is Null then use SubmittedOn date for comparison. If UpdatedOn is NOT NULL, then use UpdatedOn date for comparison.

Here is my query but it is not returning anything but I see records in database that satisfy these conditions.

DECLARE @TodayDATE DATETIME
SET @TodayDATE = GETDATE();
DECLARE @DATETOCOMPARETO DATETIME  = DATEADD(DAY, -7, @TodayDATE);
PRINT @TodayDATE;
PRINT @DATETOCOMPARETO;
SELECT Files.FileId, Files.Filename, Files.Claim_id, Claims.SubmittedOn 
FROM dbo.Files
    INNER JOIN dbo.Claims
    ON dbo.Claims.Id LIKE dbo.Files.Claim_id
   WHERE dbo.Claims.Status LIKE 'Pending' AND
    ((dbo.Claims.UpdatedOn IS NULL AND dbo.Claims.SubmittedOn < @DATETOCOMPARETO) OR 
    (dbo.Claims.UpdatedOn IS NOT NULL AND dbo.Claims.UpdatedOn < @DATETOCOMPARETO))

Answer 1

When you use JOIN, you need to use a equal sign (=) after the ON. And also you need to use a DATEDIFF function to compare the date. Try this:

DECLARE @TodayDATE DATETIME
SET @TodayDATE = GETDATE();
DECLARE @DATETOCOMPARETO DATETIME  = DATEADD(DAY, -7, @TodayDATE);
SELECT Files.FileId, Files.Filename, Files.Claim_id, Claims.SubmittedOn 
FROM dbo.Files
INNER JOIN dbo.Claims
     ON dbo.Claims.Id = dbo.Files.Claim_id
WHERE dbo.Claims.Status = 'Pending' AND
    DATEDIFF(DAY, ISNULL(dbo.Claims.UpdatedOn, dbo.Claims.SubmittedOn), @DATETOCOMPARETO) > 7

Answer 2

Hi @Joe Green ,

You can also try COALESCE function e.g.,

FORMAT(COALESCE(dbo.Claims.UpdatedOn,  dbo.Claims.SubmittedOn), 'yyyy-MM-dd') < FORMAT(@DATETOCOMPARETO, 'yyyy-MM-dd')   

Thanks! :)

Answer 3

In your JOIN - you are using LIKE and since there are no wildcards it would be much better to use equal. In the WHERE clause you are also using LIKE - but again, no wildcards are specified. If you are looking for the Status = 'Pending' and not a Status that starts with 'Pending' then use an equal sign, but if (as I suspect) you want any status that has 'Pending' then you need:

Status Like '%Pending%'

But beware - that leading wildcards will not be able to utilize an index if one is available.

A couple more points:

1) Datetime data type includes the time - so this code is dependent on when it is run. You should - instead - set your variable to a midnight time which can be done like this: dateadd(day, datediff(day, 0, getdate()) - 7, 0)
2) You should use table aliases - and then use that alias when referencing columns.

 DECLARE @DATETOCOMPARETO DATETIME  = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 7, 0);  

 PRINT @DATETOCOMPARETO;  

 SELECT fil.FileId, fil.Filename, fil.Claim_id, clm.SubmittedOn   
 FROM dbo.Files         fil  
     INNER JOIN dbo.Claims   clm  
     ON clm.Id = fil.Claim_id  
    WHERE clm.Status LIKE 'Pending%' AND  
     (clm.SubmittedOn < @DATETOCOMPARETO OR clm.UpdatedOn < @DATETOCOMPARETO)  

But - that presents a problem...because what if both SubmittedOn and UpdatedOn are more than 7 days ago?

I think you might need something like this:

WHERE ...  
AND clm.SubmittedOn < @DATETOCOMPARETO   
AND clm.UpdatedOn IS NULL  

This assumes that what you are really looking for are unprocessed files - and that is indicated by an UpdatedOn column not being populated until after the file has been processed.

Answer 4

Hi @Joe Green ,

For such problems, generally need to provide CREATE statement and INSERT INTO statement and the desired result (picture or excel format is good).

Please try:

 DECLARE @TodayDATE DATETIME  
 SET @TodayDATE = GETDATE();  
 DECLARE @DATETOCOMPARETO DATETIME  = DATEADD(DAY, -7, @TodayDATE);  
  
 SELECT Files.FileId, Files.Filename, Files.Claim_id, Claims.SubmittedOn   
 FROM dbo.Files  
 INNER JOIN dbo.Claims  
 ON dbo.Claims.Id=dbo.Files.Claim_id  
 WHERE dbo.Claims.Status='Pending' AND  
 ((dbo.Claims.UpdatedOn IS NULL AND dbo.Claims.SubmittedOn < @DATETOCOMPARETO) OR   
 (dbo.Claims.UpdatedOn IS NOT NULL AND dbo.Claims.UpdatedOn < @DATETOCOMPARETO))  

The above statement has not been tested, if it does not work, please provide a minimal example so that we can do some testing.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.