?
Solved

IF/ELSIF program in SQL

Posted on 2003-02-19
2
Medium Priority
?
802 Views
Last Modified: 2006-11-17
Chinese Zodiac question:

I have to write an SQL program which I have to find what animal is on my birthyear.  I need to declare a date variable birth_date and assign it to my birthdate.  Use an IF/ELSIF structure to test every year and determine the animal associated with my birth year.  
1924.36.48.60.72.84.96 - Rat
1925.37.49.61.73.85.97 - Cow
1926.38.50.62.74.86.98 - Tiger
1927.39.51.63.75.87.99 - Rabbit
1928.40.52.64.76.88.00 - Dragon
1929.41.53.65.77.89.01 - Snake
...................... - Horse
...................... - Sheep
...................... - Monkey
...................... - Chicken
...................... - Dog
1935.47.59.71.83.95.07 - Pig

output should be this
dbms_output.put_line('I was born in ' || year || ', which is the year of the ???') ??? = whatever animal is in that year.
0
Comment
Question by:leegarson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 7983828
Are you using a SQL Server database?  If not, the statement below will almost certainly not work.  However, the LOGIC of how to determine the animal should work in any database/language, if you convert the SYNTAX.


DECLARE @birth_date SMALLDATETIME
SET @birth_date = 'Aug 17, 1975' --or whatever
SELECT 'I was born in ' + CAST(YEAR(@birth_date) AS CHAR(4)) + ' which is the year of the ' +
CASE (YEAR(@birth_date) - 1924) % 12
WHEN 0 THEN 'Rat'
WHEN 1 THEN 'Cow'
WHEN 2 THEN 'Tiger'
--...
WHEN 11 THEN 'Pig'
END
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7983859
Note that you could of course easily go back to dates prior to 1924 if you want to: just back up 1924 by even multiples of 12 years, the rest of the logic should function perfectly.

By the way, % is the modulo (remainder) operator in SQL Server; on other systems it is MOD or \ or whatever else.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question