Join 132,611 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 933 people online right now. Registration is fast and FREE... Join Now!
Not sure I'm following this one. It sounds almost like you're looking for this:
CODE
SELECT Time, Date FROM tblAppt WHERE Date = '7/10/2008' group by Date having count(Time) < (SELECT COUNT(*) FROM tbl1 WHERE 1Enabled = 'True')
Hi baavgai!
Something like that. I tried that, but it doesn't work. I'm not even sure if this is the best way to do this. I may go back to the drawing board on this one, to see if there is a better way.
I'd be curious what "doesn't work" means. However, if you're taking another tack, some quick advise...
Do not use field names like Date, Time, DateTime, etc, Stay away from all field names that could conceivably have meaning to the database itself. Always define a primary key.
On meaning, tbl1 has none. Also, using a count from tbl1 for any kind of filter criteria is ambiguous at best. Try to use names that imply meaning so a new comer to the system can readily understand it. Remember, leave something you've worked on long enough and that new comer will be you.
I'd be curious what "doesn't work" means. However, if you're taking another tack, some quick advise...
Do not use field names like Date, Time, DateTime, etc, Stay away from all field names that could conceivably have meaning to the database itself. Always define a primary key.
On meaning, tbl1 has none. Also, using a count from tbl1 for any kind of filter criteria is ambiguous at best. Try to use names that imply meaning so a new comer to the system can readily understand it. Remember, leave something you've worked on long enough and that new comer will be you.
Good luck.
Good advice. Thank you. When I run the following
CODE
SELECT [Time], Date FROM tblSchedule WHERE Date = '7/16/2008' group by Date having count(Time) < (SELECT COUNT(*) FROM tblVis WHERE VisEnabled = 'True')
The code is the same, just had changed the names to make it simple when I first posted. I get this error
QUOTE
Column 'tblSchedule.Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Not sure how to handle that. In changing the field names, like TIME to FLDTIME. Can I change that in SQL 2005, and will it chnage my other SP's?
Yes, changing field names would impact everything. I wouldn't usually suggest it, but if you're starting over, you might as well benefit from the fresh start.
As to the error, I'm afraid my example has issues. This should get you some useful data:
CODE
SELECT [Time], Date, count(*) FROM tblSchedule WHERE Date = '7/16/2008' GROUP BY [Time], Date
To limit this based on that count, this should work:
CODE
SELECT [Time], Date, count(*) as ItemCount FROM tblSchedule WHERE Date = '7/16/2008' GROUP BY [Time], Date HAVING count(*) < (SELECT COUNT(*) as MaxCount FROM tblVis WHERE VisEnabled = 'True')
For more complex filters, this might work better for you:
CODE
SELECT a.[Time], a.Date FROM ( SELECT [Time], Date, count(*) as ItemCount FROM tblSchedule WHERE Date = '7/16/2008' GROUP BY [Time], Date ) a WHERE a.ItemCount < (SELECT COUNT(*) as MaxCount FROM tblVis WHERE VisEnabled = 'True')
16 Jul, 2008 - 07:10 AM
| 