CODEWITHSUNDEEP
sqlsql-server
sab669
sab669

Reputation: 4104

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:

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.

Upvotes: 0

Views: 82

Answers (2)

Cato
Cato

Reputation: 3701

Use COALESCE for this. It gets the first parameter in a list that is not null. It is ISO standard and very useful

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

Upvotes: 1

neer
neer

Reputation: 4092

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()))
            )
    )

Upvotes: 1

Related Questions