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.
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, RedenFROM Ziektes_VerlofWHERE (@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 1FROM Ziektes_VerlofWHERE ((@Datum BETWEEN Start_Datum AND Eind_Datum) OR(Start_Datum <= @Datum) AND (Eind_Datum IS NULL))AND Naam = Chauffeurs.Naam )[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 08:04:38
|
you're welcomeGlad that I could help you out ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|