IF ELSE statements in SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

IF ELSE statements in SQL

Author

Topic

skiSQL2
Starting Member

2 Posts

Posted - 2009-09-16 : 09:35:53

Question:
How and where might I add an "if than else" statement to the current query (below) so that IF there is a record for the person where the LocationType is EUROPE, return that recentBirthdayDate and recentBirthdayLocation, ELSE if there is a record for the person where the LocationType is AMERICAS, return that recentBirthdayDate and recent BirthdayLocation, ELSE do not return a record (i.e. we don't want to see records where the LocationType is ASIA for this scenario)

Current Query
SELECT DISTINCT * FROM (
SELECT top 100 percent
tableA.id,
tableA.name,
convert(varchar(10),tableB.recentBirthdayDate, 101),
tableB.recentBirthdayLocation,
tableA.address
FROM
tableA
INNER JOIN tableB ON tableA.id = tableB.id
WHERE
(tableB.LocationType = 'EUROPE') AND (tableB.addressDate =
(SELECT
MAX(addressDate)
FROM
tableC
WHERE
tableB.id = tableC.id))
ORDER BY
UPPER(RTRIM(tableA.name))) tableD
ORDER BY tableD.name

Reference Tables
TableA
id name address
123 Betsy 4 Main St
456 Sarah 1 High Street
789 Meghan 8 Pine Street

TableB
id recentBirthdayDate recentBirthdayLocation LocationType addressDate
123 5/5/2009 London, UK EUROPE 5/1/2009
123 5/5/2009 Boston, MA AMERICAS 5/10/2009
456 2/1/2009 New York, NY AMERICAS 2/6/2009
789 1/1/2009 Hong Kong, China ASIA 1/2/2009

TableC
id addressDate
123 5/10/2009
123 5/1/2009
456 2/2/2009
456 2/6/2009

TableD
…. name ….

Current Return from Current Query
id name recentBirthdayDate recentBirthdayLocation address
123 Betsy 5/5/2009 London, UK 4 Main St

Goal Return with IF ELSE fix
id name recentBirthdayDate recentBirthdayLocation address
123 Betsy 5/5/2009 London, UK 4 Main St
456 Sarah 2/1/2009 New York, NY 1 High St

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-16 : 09:56:53

Why not just add WHERE LocationType in ('EUROPE','AMERICA') and add LocationType to your results instead of (tableB.LocationType = 'EUROPE')?

skiSQL2
Starting Member

2 Posts

Posted - 2009-09-16 : 10:15:10

Thank you for the response. Unfortunately that would not solve the problem, as we need the name value to be unique, thus returning EUROPE record if available, otherwise AMERICAS value.

Your fix would return two records for Betsy, I believe:
id name recentBirthdayDate recentBirthdayLocation address locationType
123 Betsy 5/5/2009 London, UK 4 Main St EUROPE
123 Betsy 5/5/2009 Boston, MA 4 Main St AMERICAS
456 Sarah 2/1/2009 New York, NY 1 High St AMERICAS

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-16 : 12:49:31

I know it looks crude but one of my team mates used this technique before

WHERE (CASE WHEN tableB.LocationType IN ('EUROPE' 'AMERICAS')
THEN 'Keep'
ELSE ' '
END) = 'Keep'

John

"The smoke monster is just the Others doing barbecue"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:17:20

[code]SELECT a.id,
a.name,
COALESCE(MAX(CASE WHEN LocationType ='EUROPE' THEN recentBirthdayDate ELSE NULL END),MAX(CASE WHEN LocationType ='AMERICAS' THEN recentBirthdayDate ELSE NULL END)) AS recentBirthdayDate,
COALESCE(MAX(CASE WHEN LocationType ='EUROPE' THEN recentBirthdayLocation ELSE NULL END),MAX(CASE WHEN LocationType ='AMERICAS' THEN recentBirthdayLocation ELSE NULL END)) AS recentBirthdayLocation,
a.address
FROM TableA a
INNER JOIN TableB b
ON b.id=a.id
WHERE b.LocationType IN ('EUROPE','AMERICAS')
GROUP BY a.id,a.name,a.address
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources