Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

problem creating directory

I have a package that calculates a date, downloads a zip file and then the problems begin.. I WANT to have it create a folder using the same filename (left of the .zip) but my problem appears to be how I'm doing this. It is a calculated variable that has scope for the whole package. I set the source property to false and hard coded the parent directory and then have an expression that concatenates the constant (parent) directory with the global variable and my intent is to use this in place of the source value for the create directory (I then unzip the file into that directory and have a for each loop to process said files and rename them). My problem is that, for some reason, VS calculates the expression and I get an error that says The connection xxxxxx is not found. This error is thrown by the Connections collection when the specific connection element is not found. (xxxxxx is the calculated direcotry and is correct). Why is it doing this calculation and erroring, its job is to create the folder? Thanks in advance.I'm guessing that either you are setting the wrong property, or the package is checking to see if the directory exists before the task to create it has run. You make make sure that DelayValidation is set to TRUE on all appropriate tasks.|||John, you were correct in that it was the wrong property (thank you)! Interestingly enough I also wasn't aware of the delay validation option so both of you responses were very helpful. thanks much!!!