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)
 Problem with querry
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 05/14/2013 :  07:49:53  Show Profile  Reply with Quote
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?

Edited by - nechtmarrie on 05/14/2013 07:50:52

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2013 :  08:02:33  Show Profile  Reply with Quote
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
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 05/14/2013 :  08:23:11  Show Profile  Reply with Quote
Yep that was it, thnx alot!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/15/2013 :  00:37:33  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
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.05 seconds. Powered By: Snitz Forums 2000