query assistance: city

Showing posts with label city. Show all posts

Friday, March 30, 2012

Wednesday, March 28, 2012

Hi All,
Hoping you might be able to help with this one.
Say I have this layout:
Tbl_People
Person_ID, Name, Address, City, State, Zip.
Tbl_Response
Response_ID, Person_ID, Q_ID (integer)
LU_Questions
Q_ID, Q_Label
So basically I have a lookup table with a bunch of question IDs. What I'm
really looking to get is data in this format:
Person_ID Q_Label1 QLabel2 QLabel3
---
5 Yes Yes
No
6 No Yes
Yes
Where I have one column for each record in LU_Questions. If a record exists
in Tbl_Response with that Person_ID and Q_ID, then it will return Yes, if
not, then No.
Any idea how I can do this?James
Can you post sample data to be tested?
"James" <minorkeys@.gmail.com> wrote in message
news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Hoping you might be able to help with this one.
> Say I have this layout:
> Tbl_People
> Person_ID, Name, Address, City, State, Zip.
> Tbl_Response
> Response_ID, Person_ID, Q_ID (integer)
> LU_Questions
> Q_ID, Q_Label
>
> So basically I have a lookup table with a bunch of question IDs. What I'm
> really looking to get is data in this format:
> Person_ID Q_Label1 QLabel2 QLabel3
> ---
> 5 Yes Yes No
> 6 No Yes Yes
> Where I have one column for each record in LU_Questions. If a record
> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> Yes, if not, then No.
> Any idea how I can do this?
>|||I'm not sure what format you'd be looking for it in, but something like:
Tbl_People:
Person_ID Name Address City
1 Bob 123 Street Madison
2 John 321 Ave Johnstown
Tbl_Response:
Person_ID Q_ID
1 1
1 2
2 1
2 2
2 4
Tbl_Questions:
Q_ID Q_Label
1 Golf
2 Camping
3 Hiking
4 Fishing
So ultimately I want:
Name OtherMetaData Golf Camping Hiking Fishing
Bob street/city/etc Yes Yes No No
John street/city/etc Yes Yes No Yes
Make sense? But it would need to be dynamic as Tbl_Questions grows.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
> James
> Can you post sample data to be tested?
>
>
> "James" <minorkeys@.gmail.com> wrote in message
> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>|||On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <u...@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>
> > James
> > Can you post sample data to be tested?
> > "James" <minork...@.gmail.com> wrote in message
> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> >> Hi All,
> >> Hoping you might be able to help with this one.
> >> Say I have this layout:
> >> Tbl_People
> >> Person_ID, Name, Address, City, State, Zip.
> >> Tbl_Response
> >> Response_ID, Person_ID, Q_ID (integer)
> >> LU_Questions
> >> Q_ID, Q_Label
> >> So basically I have a lookup table with a bunch of question IDs. What
> >> I'm really looking to get is data in this format:
> >> Person_ID Q_Label1 QLabel2 QLabel3
> >> ---
> >> 5 Yes Yes No
> >> 6 No Yes Yes
> >> Where I have one column for each record in LU_Questions. If a record
> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> >> Yes, if not, then No.
> >> Any idea how I can do this... Hide quoted text -
> - Show quoted text -
select
tp.name
, tp.other
, tq.Q_Label
, tr.golf
, tr.camping,
, tr.hiking
, tr.fishing
from tbl_people tp
join (
select person_id
,Q_id
,case when Q_id =1 then 'yes' else 'no' end golf
,case when Q_id =2 then 'yes' else 'no' end Camping
,case when Q_id =3 then 'yes' else 'no' end Hiking
,case when Q_id =4 then 'yes' else 'no' end Fishing
from tbl_response
)as tr on tp.Person_id =tr.Person_id|||James
> I'm not sure what format you'd be looking for it in, but something like:
That what I meant
create table Tbl_People (Person_ID int, Name varchar(10)
,Address varchar(20), City varchar(20))
insert into Tbl_People values (1,'Bob','123 Street','Madison')
insert into Tbl_People values (2,'John','321 Ave',' Johnstown')
create table Tbl_Response (Person_ID int,Q_ID int)
insert into Tbl_Response values (1,1)
insert into Tbl_Response values (1,2)
insert into Tbl_Response values (2,1)
insert into Tbl_Response values (2,2)
insert into Tbl_Response values (2,4)
create table Tbl_Questions (Q_ID int,Q_Label varchar(10))
insert into Tbl_Questions values (1,'Golf')
insert into Tbl_Questions values (2,'Camping')
insert into Tbl_Questions values (3,'Hiking')
insert into Tbl_Questions values (4,'Fishing')
select Name ,Address, City,max(case when Q_Label ='Golf' then 'yes' else
'no' end) as Golf,
max(case when Q_Label ='Camping' then 'yes' else
'no' end) as Camping,
max(case when Q_Label ='Hiking' then 'yes' else
'no' end) as Hiking,
max(case when Q_Label ='Fishing' then 'yes' else
'no' end) as Fishing
from Tbl_People tp join Tbl_Response tr
on tp.Person_ID=tr.Person_ID
join Tbl_Questions tq on tq.Q_ID=tr.Q_ID
group by Name ,Address, City
"James" <minorkeys@.gmail.com> wrote in message
news:%23Tx7Y6nsHHA.4916@.TK2MSFTNGP04.phx.gbl...
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>> James
>> Can you post sample data to be tested?
>>
>>
>> "James" <minorkeys@.gmail.com> wrote in message
>> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>>
>|||Thank you both. This is pretty close and I may end up having to work with
this.
Is there any way to make the column names dynamic, instead of hardcoded?
Either way thank you immensely for your help.
"schal" <shivaramchalla@.gmail.com> wrote in message
news:1182264456.599338.38270@.w5g2000hsg.googlegroups.com...
> On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
>> I'm not sure what format you'd be looking for it in, but something like:
>> Tbl_People:
>> Person_ID Name Address City
>> 1 Bob 123 Street Madison
>> 2 John 321 Ave Johnstown
>> Tbl_Response:
>> Person_ID Q_ID
>> 1 1
>> 1 2
>> 2 1
>> 2 2
>> 2 4
>> Tbl_Questions:
>> Q_ID Q_Label
>> 1 Golf
>> 2 Camping
>> 3 Hiking
>> 4 Fishing
>> So ultimately I want:
>> Name OtherMetaData Golf Camping Hiking Fishing
>> Bob street/city/etc Yes Yes No No
>> John street/city/etc Yes Yes No
>> Yes
>> Make sense? But it would need to be dynamic as Tbl_Questions grows.
>> "Uri Dimant" <u...@.iscar.co.il> wrote in message
>> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>>
>> > James
>> > Can you post sample data to be tested?
>> > "James" <minork...@.gmail.com> wrote in message
>> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> >> Hi All,
>> >> Hoping you might be able to help with this one.
>> >> Say I have this layout:
>> >> Tbl_People
>> >> Person_ID, Name, Address, City, State, Zip.
>> >> Tbl_Response
>> >> Response_ID, Person_ID, Q_ID (integer)
>> >> LU_Questions
>> >> Q_ID, Q_Label
>> >> So basically I have a lookup table with a bunch of question IDs. What
>> >> I'm really looking to get is data in this format:
>> >> Person_ID Q_Label1 QLabel2 QLabel3
>> >> ---
>> >> 5 Yes Yes No
>> >> 6 No Yes Yes
>> >> Where I have one column for each record in LU_Questions. If a record
>> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will
>> >> return
>> >> Yes, if not, then No.
>> >> Any idea how I can do this... Hide quoted text -
>> - Show quoted text -
> select
> tp.name
> , tp.other
> , tq.Q_Label
> , tr.golf
> , tr.camping,
> , tr.hiking
> , tr.fishing
> from tbl_people tp
> join (
> select person_id
> ,Q_id
> ,case when Q_id =1 then 'yes' else 'no' end golf
> ,case when Q_id =2 then 'yes' else 'no' end Camping
> ,case when Q_id =3 then 'yes' else 'no' end Hiking
> ,case when Q_id =4 then 'yes' else 'no' end Fishing
> from tbl_response
> )as tr on tp.Person_id =tr.Person_id
>

Wednesday, March 21, 2012

Query for each City the ZipCode with the highest Total

Let's assume I have a table with the following fields:
City, ZipCode, Total
.. and the following data:
Roswell 32032 100
Roswell 32032 120
Roswell 34589 75
Atlanta 31902 345
Atlanta 35865 200
Atlanta 46321 110
Denver 31411 25
Denver 31411 50
I need a query that will return for each City the ZipCode with the highest
Total.
For example:
Roswell 32032
Atlanta 31902
Denver 31411Assuming that the city should be deterministic that should do the work:
Select * from
SomeTable s
Inner join
(
Select city,max(total) as Total
From SomeTable
Group by City
) Subquery
ON
Subquery.City = s.City AND
Subquery.Total = s.Total
THe Problem is that you didnt point out what is more important in that
case, the ZipCode or the city. One mor problem could be that there can be
duplicated. If you share some DDL with us, its always more easy to identify
your problem and help you solving it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Scott" <someone@.microsoft.com> schrieb im Newsbeitrag
news:uPZkCD8XFHA.3876@.TK2MSFTNGP10.phx.gbl...
> Let's assume I have a table with the following fields:
> City, ZipCode, Total
> .. and the following data:
> Roswell 32032 100
> Roswell 32032 120
> Roswell 34589 75
> Atlanta 31902 345
> Atlanta 35865 200
> Atlanta 46321 110
> Denver 31411 25
> Denver 31411 50
> I need a query that will return for each City the ZipCode with the highest
> Total.
> For example:
> Roswell 32032
> Atlanta 31902
> Denver 31411
>
>|||Try,
select City, ZipCode, Total
from t1
where total = (select max(a.total) from t1 where a.city = t1.city)
-- or
select t1.City, t1.ZipCode, t1.Total
from t1 inner join (select city, max(total) as max_total from t1 group by
city) as t2
on t1.city = t2.city
AMB
"Scott" wrote:

> Let's assume I have a table with the following fields:
> City, ZipCode, Total
> ... and the following data:
> Roswell 32032 100
> Roswell 32032 120
> Roswell 34589 75
> Atlanta 31902 345
> Atlanta 35865 200
> Atlanta 46321 110
> Denver 31411 25
> Denver 31411 50
> I need a query that will return for each City the ZipCode with the highest
> Total.
> For example:
> Roswell 32032
> Atlanta 31902
> Denver 31411
>
>|||Correction,

> select t1.City, t1.ZipCode, t1.Total
> from t1 inner join (select city, max(total) as max_total from t1 group by
> city) as t2
> on t1.city = t2.city
select t1.City, t1.ZipCode, t1.Total
from t1 inner join (select city, max(total) as max_total from t1 group by
city) as t2 on t1.city = t2.city and t1.total = t2.max_total
AMB
"Alejandro Mesa" wrote:
> Try,
> select City, ZipCode, Total
> from t1
> where total = (select max(a.total) from t1 where a.city = t1.city)
> -- or
> select t1.City, t1.ZipCode, t1.Total
> from t1 inner join (select city, max(total) as max_total from t1 group by
> city) as t2
> on t1.city = t2.city
>
> AMB
> "Scott" wrote:
>

Friday, March 30, 2012

Query Help

Wednesday, March 28, 2012

Query Help

Query Help

Wednesday, March 21, 2012

Query for each City the ZipCode with the highest Total

query assistance

Blog Archive

About Me