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)
 query for multiple dates

Author  Topic 

tsimsnh1
Starting Member

4 Posts

Posted - 2012-02-29 : 20:23:17
I'm working on a query to return records about students and return dates. The fields I'm querying are StudentID, hiatus_start_date, hiatus_end_date, expected_return_date.
If the Student has 1 expected_return_date and it is within the range of the hiatus_start_date and hiatus_end_date then return the record.
If the student has more than 1 expected_return_date with any of those dates greater than the date range (in the future), then don't return those records regardless of whether or not a date is within the range of hiatus_start_date and hiatus_end_date. Seems straight-forward but I'm not getting it to work.

Thanks!
Tom S.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-29 : 22:20:32
quote:
If the student has more than 1 expected_return_date with any of those dates greater than the date range (in the future)

Can you explain more on what do you mean by that ? Preferably with some sample data and expected result


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:25:42
[code]
SELECT *
FROM Student s
WHERE NOT EXISTS (SELECT 1
FROM Student
WHERE StudentID = s.StudentID
AND expected_return_date > hiatus_end_date)
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tsimsnh1
Starting Member

4 Posts

Posted - 2012-03-01 : 13:52:56
Thanks for your replies khtan and visakh16. I will give the recommended solution a try and post the results. It looks like it will return records as expected.
~TomS
Go to Top of Page
   

- Advertisement -