case statements - 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

case statements

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-03-25 : 14:19:12

Hi Guys,

I am trying to display some information using the case statement.
I want to display the dates that the contact information was edited in a database.
When I use the case statement each item goes into a new column on a different line and the customers entry for HomePhone, CellPhone and workPhone is repeated 3 times


e.g 
cardno WorkNoEntrydate CellNoEntrydate  HomeNoEntrydate HP WP CP
cust1  20070101        null             null             12 11 NUL
cust1  null            20060101         null             12 11 NUL
cust1  null            null             20050101         12 11 NUL

I actually want the above information to look like this
cardnumber   homephone    cellphone     workphone
cust1        20050101      20060101     20070101

How can I accomplish this? I have attached the code


select distinct c.cardnumber
	,FirstName
	,InitialsName
	,LastName
	,AddressLine1
	,AddressLine2
	,AddressLine3
	,HomePhone
	,CellPhone
	,WorkPhone
	,c.EntryDate as createdate
	,case when fieldname = 'Phone Work' then p.entrydate
	else 'none'
	end WorkPhoneEntrydate
	,case when fieldname = 'Phone Cell' then p.entrydate
	else 'none'
	end CellPhoneEntrydate
	,case when fieldname = 'Phone Home' then p.entrydate
	else 'none'
	end HomePhoneEntrydate	
from Customer c left outer join  phonedates p on c.cardnumber=p.cardno
where c.cardnumber in (select cardnumber from AlternateAccessKeys where IsCreditCardKey='yes')
order by c.cardnumber

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 14:29:20

[code]
select c.cardnumber
,FirstName
,InitialsName
,LastName
,AddressLine1
,AddressLine2
,AddressLine3
,HomePhone
,CellPhone
,WorkPhone
,c.EntryDate as createdate
,p.WorkPhoneEntrydate
,p.CellPhoneEntrydate
,p.HomePhoneEntrydate
from Customer c
left outer join
(select cardno,
max(case when case when fieldname = 'Phone Work' then p.entrydate
else null end) as WorkPhoneEntrydate,
max(case when case when fieldname = 'Phone Cell' then p.entrydate
else null end) as CellPhoneEntrydate,
max(case when case when fieldname = 'Phone Home' then p.entrydate
else null end) as HomePhoneEntrydate
from phonedates
group by cardno
)p on c.cardnumber=p.cardno
where exists (select 1 from AlternateAccessKeys where IsCreditCardKey='yes' and cardnumber = c.cardnumber)
order by c.cardnumber
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources