Problem with querry - SQL Server Forums

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)

Problem with querry

Author

Topic

nechtmarrie
Starting Member

24 Posts

Posted - 2013-05-14 : 07:49:53

Hello, i ame trying to create a querry that selects the still available staff based on they vacations on a date

In my Ziektes_Verlof table i got the name of the staff member a start date and an end date.

When no end date is entered the duration is treathed as infinite when the start date is passed.

The Chauffeurs table contains all staff and the querry should select all names and their Rijbewijs from the chauffeurs table when they are not deemed as not working in the Ziektes_Verlof table

Below is my querry:

SELECT Naam, Type
FROM Chauffeurs
WHERE NOT EXISTS (SELECT Naam FROM Ziektes_Verlof WHERE (@Datum BETWEEN Start_Datum AND Eind_Datum) OR (@Datum >= Start_Datum AND Eind_Datum IS NULL))

For some reason the querry returns no result.
The sub querry works as it should.
Dous anyone have an idea what is wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 08:02:33

you're not relating subquery to main query.
I think something like below should work though I'm not sure as I cant get requirement fully from your explanation above


SELECT Naam, Type
FROM Chauffeurs c
WHERE NOT EXISTS (SELECT 1 FROM Ziektes_Verlof WHERE Naam = c.Naam
AND (@Datum BETWEEN Start_Datum AND Eind_Datum) OR (@Datum >= Start_Datum AND Eind_Datum IS NULL))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nechtmarrie
Starting Member

24 Posts

Posted - 2013-05-14 : 08:23:11

Yep that was it, thnx alot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:37:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources