sab669 sab669 - 3 months ago 8
SQL Question

How to find records from one table where multiple columns meet different criteria?

I'm using MS SQL Server 2012.

I'm trying to create a stored procedure that takes

@n int
as a parameter. The procedure is basically trying to find a subset of records from one table that need to have some information re-calculated, based on the records' dates being older than some dates in another table.

The procedure must do the following:


  • Find records from
    Table1
    where
    Table1.UpdatedOn
    is within the past
    @n
    days.

  • Find records from
    Table2
    where
    Table2.EndDate
    is before the date in the record(s) returned by the first bullet point (so basically if
    T2.EndDate
    <
    T1SubSet.UpdatedOn
    , then we want that record)

  • If
    Table2.EndDate
    is
    null
    , then we use a different date column for this comparison.

  • Insert those records from
    Table2
    that meet this criteria into
    Table3
    . More specifically, it should insert
    Table2.RecordID
    into
    Table3
    , as well as some "hard coded" values.



This is what I've got:

INSERT INTO Table3 (ColA, ColB, ColC, ColD)
SELECT RecordID, GETDATE(), 0, null
FROM Table2
WHERE EXISTS
(SELECT RecordID FROM Table2 WHERE EndDate IS NOT NULL AND EndDate < (SELECT UpdatedOn FROM Table1 WHERE UpdatedOn > DATEADD(day, @n, GETDATE())) OR
SELECT RecordID FROM Table2 WHERE EndDate IS NULL And SomeOtherDate < (SELECT UpdatedOn FROM Table1 WHERE UpdatedOn > DATEADD(day, @n, GETDATE())))


Logically, this probably isn't right but either way when I try to execute that command I get two errors:


Line 21
Incorrect syntax near the keyword 'SELECT'.

Line 21
Incorrect syntax near ')'.


I'm not sure what's wrong with my nested queries.

Answer

Use TOP 1 and OR

INSERT INTO Table3 (ColA, ColB, ColC, ColD)
SELECT RecordID, GETDATE(), 0, null 
FROM Table2
WHERE EXISTS
    (
        SELECT RecordID FROM Table2 
        WHERE 
            (
                EndDate IS NOT NULL AND 
                EndDate < (SELECT TOP 1 UpdatedOn FROM Table1 WHERE UpdatedOn > DATEADD(day, @n, GETDATE()))
            ) OR
            (
                EndDate IS NULL And 
                SomeOtherDate < (SELECT TOP 1 UpdatedOn FROM Table1 WHERE UpdatedOn > DATEADD(day, @n, GETDATE()))
            )
    )