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 2008 Forums
 Transact-SQL (2008)
 [SOLVED]Querry question

Author  Topic 

nechtmarrie
Starting Member

24 Posts

Posted - 2013-05-16 : 07:03:21
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 07:22:25
[code]
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

)
[/code]


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

nechtmarrie
Starting Member

24 Posts

Posted - 2013-05-16 : 08:01:36
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

52326 Posts

Posted - 2013-05-16 : 08:04:38
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
   

- Advertisement -