SQL Query ; I want to get Enquiry_No which has not been updated since last 7 days

Question

I am having two tables TblEnquiry with Enquiry _No as Primary Key and tblHistory for maintaing updatiing details with Enquiry_No as foreign key and History_CreatedOn field for date. I want to get Enquiries which have not been updated since last 7 days.

Answer 1

If you're using SQL Server:

SELECT
     <add columns here>
FROM
     tblEnquiry
WHERE
     NOT EXISTS
     (
          SELECT *
          FROM tblHistory H
          WHERE H.enquiry_no = E.enquiry_no
            AND H.history_createdon BETWEEN DATEADD(dy, -7, GETDATE()) AND GETDATE()
     )

Answer 2

SELECT e.*
FROM tblEnquiry e
WHERE NOT EXISTS(SELECT * FROM tblHistory h WHERE e.Enquiry_No = e.Enquiry_No AND h.History_CreatedOn >= DATEADD(dd, -7, GETDATE())

Answer 3

WITH Hist(enquiry_no, history_createdon) AS
(
    SELECT Enquiry_No, History_CreatedOn
    FROM tblHistory
    WHERE History_CreatedOn >= DATEADD(dd, -7, GETDATE())
)
SELECT * 
FROM tblEnquiry 
    LEFT OUTER JOIN Hist ON tblHist.enquiry_no = tblEnquiry.enquiry_no 
WHERE tblHistory.enquiry_no IS NULL

This will avoid the poor performance of the standard NOT EXISTS query

Answer 4

Got the answer

select h1.Enquiry_No from tblHistory h1 group by h1.Enquiry_No having DATEDIFF(DD,Max(h1.History_CreatedOn),GETDATE())>=7

I got the Enquiries that have not been updated since last 7 days