Sounds Simple But Yet.... - 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 2005 Forums

Transact-SQL (2005)

Sounds Simple But Yet....

Author

Topic

sdseal
Starting Member

9 Posts

Posted - 2009-10-21 : 21:42:29

Here's an example table:

SectionName....LessonPlanName.....BeginDate.....EndDate
1st............Plan 1.............2009/10/10....2009/10/17
3D.............A Plan.............2009/10/10....2009/10/17
5T.............Cool Plan..........2009/10/11....2009/10/17
1st............Plan 2.............2009/10/18....2009/10/23

Okay here's what I need to return: JUST a list of SectionNames that DO NOT have a lesson plan for say... 2009/10/20

Obviously, I want the list that would be returned to look like:

SectionName
3D
5T

If I did a simple date comparison, the section "1st" still gets returned because it has a "older" lesson that the date 2009/10/20 does not fall into.

But,,, Section "1st" DOES have a plan that fits this criteria so I DON'T want to include it in the list.

This seems so simple, but my mind is looping and looping.

I'm beating my head against the wall.....

Any help would be appreciated

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-21 : 22:40:26

select SessionName from <u table>
where convert(varchar,EndDate,112) < '20091020'
group by SessionName
having count(SessionName) = 1

gaauspawcscwcj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 23:17:51

[code]
select distinct SectionName
from example_table e
where not exists
(
select *
from example_table x
where x.SectionName = e.SectionName
and x.BeginDate <= '20091020'
and x.EndDate >= '20091020'
)
[/code]

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

sdseal
Starting Member

9 Posts

Posted - 2009-10-22 : 09:26:39

Bless you guys...

khtan you pointed me in the right direction. I didn't think of NOT EXISTS. I was trying a long string of AND's and OR's.

YOU ROCK!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources