query assistance: state

Showing posts with label state. 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
>

Query Help

Hi all,
I have one â'tallâ' table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ======== VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
State Current Serial# Since Previous Serial#
CA QWEFD2 05/05/2005 QWEFD1
VA Z32WE13 01/01/2003 Z32WE12
TX POISD21 05/03/2005 Never Changed
Thanks in advance,
-AppreciatorIt seems to be a flaw in the data:
> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA', 'Z32WE12', '12/31/2002')
insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
insert into t1 values('VA', 'Z32WE13', '01/01/2003')
insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
insert into t1 values('TX', 'POISD21', '05/03/2005')
insert into t1 values('TX', 'POISD21', '05/04/2005')
insert into t1 values('TX', 'POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state, serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !='have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:
> Hi all,
> I have one â'tallâ' table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> State Current Serial# Since Previous Serial#
> CA QWEFD2 05/05/2005 QWEFD1
> VA Z32WE13 01/01/2003 Z32WE12
> TX POISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
> It seems to be a flaw in the data:
> > VA Z32WE12 12/31/2003
> > VA Z32WE13 01/01/2003
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA', 'Z32WE12', '12/31/2002')
> insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
> insert into t1 values('VA', 'Z32WE13', '01/01/2003')
> insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
> insert into t1 values('TX', 'POISD21', '05/03/2005')
> insert into t1 values('TX', 'POISD21', '05/04/2005')
> insert into t1 values('TX', 'POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by state, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:
> > Hi all,
> >
> > I have one â'tallâ' table that records the following on a regular basis:
> >
> > STATE SERIAL# DATE
> > ====== ======== ========> > VA Z32WE12 12/31/2003
> > CA QWEFD1 05/04/2005
> > VA Z32WE13 01/01/2003
> > CA QWEFD2 05/05/2005
> > TX POISD21 05/03/2005
> > TX POISD21 05/04/2005
> > TX POISD21 05/05/2005
> >
> > We are tracking the serial number for each state and would like to report on
> > the current and previous serial number for each state. Can someone please
> > help me with building the query in order to get the following:
> >
> > State Current Serial# Since Previous Serial#
> > CA QWEFD2 05/05/2005 QWEFD1
> > VA Z32WE13 01/01/2003 Z32WE12
> > TX POISD21 05/03/2005 Never Changed
> >
> > Thanks in advance,
> >
> > -Appreciator
> >sql

Friday, March 30, 2012

Query Help

Wednesday, March 28, 2012

Query Help

Query Help

Query Help

query assistance

Blog Archive

About Me