CODEWITHSUNDEEP
sqlsql-server
Dinav Ahire
Dinav Ahire

Reputation: 31

Filter data from 2 tables in SQL

I have two tables.

Table1:

table1Id,

some fields....

Table2:

table2Id,

table1Id,

ValidTill (Datetime),

some fields...

there are two concepts, Active records and Inactive records.

Inactive records = records from Table1 which are in Table2 with ValidTill date less than today.

Active records = records from Table1 which are in Table2 with ValidTill date more than today. Doesn't matter if such multiple table1Id present in Table2 whose ValidTill < today's date, if at least one record present in Table2 whose Validtill > today's date, then it will be considered Active record and should not fall under Inactive record.

Queries:

Active :

Select * from Table1 where table1Id in (Select table1Id from Table2 where ValidTill > GETDATE())

InActive :

Select * from Table1 where table1Id in (Select table1Id from Table2 where ValidTill < GETDATE())

with these two queries I get some records who are Active (may have such records whose validTill < GETDATE() but at least one record with ValidTill > GETDATE()) and still getting under Inactive records.

Need better query for Inactive records to avoid Active records coming along.

Thanks in advance.

Upvotes: 2

Views: 1062

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37367

In order to get active records use query:

select * from table1
where table1Id in (
    select table1Id from Table2
    group by table1Id
    having max(ValidTill) >= getdate()
) a

In order to get inactive records use query:

select * from table1
where table1Id in (
    select table1Id from Table2
    group by table1Id
    having max(ValidTill) < getdate()
) a

Basically idea is that you group by table1Id and get maximum date (that latest) for that ID and then if that maximum ID is greater then current date - it's active, inactive otherwise.

Upvotes: 1

Related Questions