SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [SOLVED]Querry question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 05/16/2013 :  07:03:21  Show Profile  Reply with Quote
Okay so i got an existing querry that creates a list of unused staff from a task list.

SELECT Naam FROM Chauffeurs WHERE NOT EXISTS (SELECT Naam FROM Planlijst WHERE Naam = Chauffeurs.Naam AND Datum = @Datum)

I would also like to remove any staff that is schedualed as sick or vacation.

I use the sollowing querry to determine of a person is sick on a specific date:

SELECT Naam, Reden
FROM Ziektes_Verlof
WHERE (@Datum BETWEEN Start_Datum AND Eind_Datum) OR
(Start_Datum <= @Datum) AND (Eind_Datum IS NULL)

Is there any way to combine these 2 querrys so that i can get a list of unused staff that is not reported as sick or on vaction?

Thnx for ur help,
Necht

Edited by - nechtmarrie on 05/16/2013 08:02:06

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/16/2013 :  07:22:25  Show Profile  Reply with Quote

SELECT Naam FROM Chauffeurs WHERE NOT EXISTS (SELECT Naam FROM Planlijst WHERE Naam = Chauffeurs.Naam AND Datum = @Datum)
AND NOT EXISTS (SELECT 1
FROM Ziektes_Verlof
WHERE ((@Datum BETWEEN Start_Datum AND Eind_Datum) OR
(Start_Datum <= @Datum) AND (Eind_Datum IS NULL))
AND Naam = Chauffeurs.Naam 
)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 05/16/2013 :  08:01:36  Show Profile  Reply with Quote
So you can just use 2 Subquerys?
I always thought this was limited to just one.

Thanks visakh, uv been a real help with my few questions :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/16/2013 :  08:04:38  Show Profile  Reply with Quote
you're welcome

Glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000