Help !!! SubQuery In Statement

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)

Help !!! SubQuery In Statement

Author

Topic

ying7690
Starting Member

2 Posts

Posted - 2011-06-14 : 21:38:05

SELECT RECID,BranchRECID,ScheduleContinueS,EndDate
FROM tblBranch_Schedule
WHERE DATEDIFF(DAY,StartDate,GETDATE()) > 0 AND DATEDIFF(DAY,GETDATE(),EndDate) >= 0
AND DATEDIFF(DAY,GETDATE(),EndDate) <=15 AND EXISTS (SELECT RECID,BranchRECID,ScheduleContinueS,EndDate
FROM tblBranch_Schedule
WHERE DATEDIFF(DAY,GETDATE(),StartDate) > 0);

SELECT RECID,BranchRECID,ScheduleContinueS,EndDate,DATEDIFF(DAY,GETDATE(),StartDate)
FROM tblBranch_Schedule
where DATEDIFF(DAY,GETDATE(),StartDate) > 0 AND DATEDIFF(DAY,GETDATE(),StartDate) <30;

I want to get the first sql result and then need to which not match in the second sql result

ying7690
Starting Member

2 Posts

Posted - 2011-06-14 : 23:40:17

SOLVE

ibbo14
Starting Member

10 Posts

Posted - 2011-06-14 : 23:46:16

Join the tables together and filter apply a where is null for the second table

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-17 : 17:49:55

You already know exists operator, you can use it to accomplish your goal.

However, don't use datediff function, your query can't use indexes that way. better use e.g.


WHERE StartDate < GETDATE() AND EndDate >= getDate() + 15...

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-17 : 18:28:31

And, don't use GETDATE() + 15 as that will not work with the new DATE/TIME/DATETIME2 data types. Better to use DATEADD to add/subtract.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources