WHERE statement on two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE statement on two tables?

Author

Topic

dke01
Starting Member

3 Posts

Posted - 2008-05-26 : 23:16:51

How do I do a WHERE statement on two tables?

CREATE TABLE [Audit]
(
[AuditID] [int],
[AuditDate] [datetime]
)

CREATE TABLE [ToDoItem]
(
[ToDoItemID] [int],
[AuditID] [int],
[DueDate] [datetime],
[CompletedDate] [datetime]
)

What I need to do is select all Audits that
still have ToDoItems not complete
that the DueDate has passed
or where the audit was done over six months ago

What I tried is

SELECT
[AuditID]
FROM
Audit a
WHERE
(a.AuditID in (SELECT AuditID
FROM ToDoItem t
WHERE
t.CompletedDate is null
AND (t.DueDate < getDate()
or ( (DateAdd(Month, 6, < a.AuditDate)) < getDate()) )
))

----
Dave

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-26 : 23:32:46

SELECT
[AuditID]
FROM
Audit a
inner join [ToDoItem] b
on a.AuditID= b.AuditID
Where b.CompletedDate is null
AND (b.DueDate < getDate())
or Datediff(month,b.CompletedDate,getdate())<6

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-26 : 23:36:35

select *
from Audit
where AuditDate < dateadd(mm,-6,getdate()
or AuditID in (select AuditID from ToDoItem where DueDate < getdate() and CompletedDate is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 00:23:40

quote:
Originally posted by sodeep

SELECT
[AuditID]
FROM
Audit a
inner join [ToDoItem] b
on a.AuditID= b.AuditID
Where b.CompletedDate is null
AND (b.DueDate < getDate())
or Datediff(month,b.CompletedDate,getdate())<6

Shouldnt the difference be >6 as reqmnt is for audit done over six months ago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 00:24:29

quote:
Originally posted by nr

select *
from Audit
where AuditDate < dateadd(mm,-6,getdate())
or AuditID in (select AuditID from ToDoItem where DueDate < getdate() and CompletedDate is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Missed a closing braces

dke01
Starting Member

3 Posts

Posted - 2008-05-27 : 00:46:01

Thank you, both of your solutions work fine.

----
Dave

dke01
Starting Member

3 Posts

Posted - 2008-05-27 : 00:56:08

SO the problem is you cannot use a column name with in the DataAdd or DateDiff but you use it outside the getDate() inside the function?

----
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources