query assistance: address

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

Saturday, February 25, 2012

Query Assistance.

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.

Address ID Workstation Test-a 1 WS1 Test-b 2 WS1 Test-a 5 WS2 Test-d 3 WS2 Test-b 7 WS2

I am trying to write a query that will display this result into Excel.

Address Duplicate WS1 WS2 Test-a
Yes
1
5 Test-b
Yes
2
7 Test-d
No

Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

What are the possible values for the Workstation column? Is it always at most 2 workstations for any address?|||No, there are actually around 8-10 workstations.
|||

This should give you an idea about how to approach the solution.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Address varchar(20),
[ID] int,
Workstation varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'Test-a', 1, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 2, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-a', 5, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-d', 3, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 7, 'WS2' )

SELECT
Duplicate = CASE WHEN dt.ADDRESS IS NULL THEN 'No' ELSE 'Yes' END,
m.Address,
m.[ID],
m.Workstation
FROM @.MyTable m
LEFT JOIN (SELECT Address
FROM @.MyTable
GROUP BY
Address
HAVING count( Address ) >= 2
) dt
ON m.Address = dt.Address
ORDER BY
Address,
Workstation

Duplicate Address ID Workstation
-- -- --
Yes Test-a 1 WS1
Yes Test-a 5 WS2
Yes Test-b 2 WS1
Yes Test-b 7 WS2
No Test-d 3 WS2

|||I appreciate the information. I have been working with this to pull the data I need. I have modified the insert statement to pull the data from another table.

I am getting an error however:
Server: Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PointAddress'.

As I mentioned I am new to alot of this, how do I identify line 15? I have searched on the error itself and found references that I should be creating an alias for the "pointaddress" column.

I appreciate the help with this. Explanations will be helpful as well.

Code Snippet

SET NOCOUNT ON

DECLARE @.dup_trnd table
( RowID int IDENTITY,
PointAddress varchar(30),
TrendID int,
Workstation varchar(30)
)

INSERT dup_trnd
SELECT PointAddress,TrendID,Workstation from DuplicateTrends

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
m.PointAddress,
m.TrendId,
m.Workstation
FROM @.dup_trnd m
LEFT JOIN (SELECT PointAddress
FROM @.dup_trnd
GROUP BY
PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON m.PointAddress = dt.PointAddress
ORDER BY
PointAddress,
Workstation

|||

Try this:

Code Snippet

SET NOCOUNT ON

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
d.PointAddress,
d.TrendId,
d.Workstation
FROM Dup_Trnd d
LEFT JOIN (SELECT PointAddress
FROM Dup_Trnd
GROUP BY PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON d.PointAddress = dt.PointAddress
ORDER BY
d.PointAddress,
d.Workstation

|||Thank you for the assistance. This is mostly what I need and can work on it from here.

Friday, March 30, 2012

Query Help

Wednesday, March 28, 2012

Query Help

Query Help

Saturday, February 25, 2012

Query Assistance.

query assistance

Blog Archive

About Me