distinct case query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

distinct case query

Author

Topic

ged
Starting Member

2 Posts

Posted - 2009-01-12 : 05:41:22

Hi,

I'm very new to SQL. I have access to the database at work and am trying to construct a query to aid some colleagues with an audit.

The problem I've got is that I'm receiving a seperate set of results for each case query. I want each case query to be performed en bloc.

I've created a fake script using two fictional tables:

TABLES:

-individuals table-
person
------
John Smith
Jane Smith

-incedents table-
person                    events
--------------------------------------------
John Smith                letter generated
John Smith                seen off site
Jane Smith                letter generated
Jane Smith                seen on site

Query:

select 
	individuals.person,

	case (incedents.events)
		when 'letter generated' then '1'
		else '0'
	end as 'Letter?',

	case (incedents.events)
		when 'seen on site' then '1'
		when 'seen off site' then '1'
		else '0'
	end as 'seen?'
from individuals
inner join incedents on individuals.person = incedents.person

The output would look something like:


person       letter?          seen?
---------------------------------------------------------
John Smith    1                0
John Smith    0                1
Jane Smith    1                0
Jane Smith    0                1

But I want to be able to get output like


person                letter?            seen?
---------------------------------------------------------
John Smith                1                1
Jane Smith                1                1

The script I've got is far more complicated and the database has too many individuals to allow manual editing.

I hope this is clear and that someone can help. Please ask for clarification if I've missed something important.

Thanks.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-12 : 05:54:07

select
individuals.person,
max(case (incedents.events)
when 'letter generated' then '1'
else '0'
end) as 'Letter?',

max(case (incedents.events)
when 'seen on site' then '1'
when 'seen off site' then '1'
else '0'
end )as 'seen?'
from individuals
inner join incedents on individuals.person = incedents.person
group by individuals.person

Jai Krishna

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-12 : 06:10:07

Use left join which brings result even both are zero.
check it out.

declare @individuals table (person varchar(30))
insert into @individuals select'John Smith' union all
select 'Jane Smith'

declare @incedents table(person varchar(50),events varchar(50))

insert into @incedents
select 'John Smith', 'letter generated' union all
select'John Smith' ,'seen off site' union all
select'Jane Smith' ,'letter generated' union all
select'Jane Smith' ,'seen on site'

select individuals.person,max(case (incedents.events)when 'letter generated' then '1'else '0'end) as 'Letter?',
max(case (incedents.events)when 'seen on site' then '1'when 'seen off site' then '1'else '0'end )as 'seen?'
from @individuals individuals left join @incedents incedents on individuals.person = incedents.person
group by individuals.person

ged
Starting Member

2 Posts

Posted - 2009-01-12 : 06:14:10

perfect, thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources