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 converting military time from CHAR column

Hi all,

I have a time column in CHAR(4) format. The contents are stored as 'military time':

1800

1830

2130

I tried using various functions, but was not able to convert to standard time.

I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:

2006-05-31 06:30 PM

Any help would greatly be appreciated.

Thanks.

- gshaf

Your military time conversion wont work because you dont have a colon separating your hours and minutes.

Try this statement:
PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)

Try this one instead, this will add a zero to your hour:

IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END

Which prints out
2006-05-31 09:30PM

Your time column is going to need that colon. Otherwise your military times will not be output properly.

|||

Worked! Just needed to correct the formatting.

Thanks so much Elliot!

- gshaf