SQL Teaser - Zodiac Signs 1

[ca8msm]

The Aim
For each person in the following table, return their star sign.

Example Data

DECLARE @PEOPLE TABLE (id int IDENTITY(1,1), Name varchar(20), DOB datetime )

INSERT @PEOPLE VALUES ('Joe', '20060101')
INSERT @PEOPLE VALUES ('Fred', '20060201')
INSERT @PEOPLE VALUES ('John', '20060301')
INSERT @PEOPLE VALUES ('Paul', '20060401')
INSERT @PEOPLE VALUES ('Harold', '20060501')
INSERT @PEOPLE VALUES ('Mark', '20060601')
INSERT @PEOPLE VALUES ('Simon', '20060701')
INSERT @PEOPLE VALUES ('Peter', '20060801')
INSERT @PEOPLE VALUES ('David', '20060901')
INSERT @PEOPLE VALUES ('Matthew', '20061001')
INSERT @PEOPLE VALUES ('Andrew', '20061101')
INSERT @PEOPLE VALUES ('Graham', '20061201')

Assumptions
We'll assume that the dates for each zodiac do not change from year to year and that they always fall between these dates:
[tt]
Aries Mar 21–Apr 20
Taurus Apr 21–May 20
Gemini May 21–Jun 20
Cancer Jun 21–Jul 21
Leo Jul 22–Aug 21
Virgo Aug 22–Sep 21
Libra Sep 22–Oct 22
Scorpio Oct 23–Nov 21
Sagittarius Nov 22–Dec 20
Capricorn Dec 21–Jan 19
Aquarius Jan 20–Feb 18
Pisces Feb 19–Mar 20
[/tt]

The Rules
I must be able to use your query and get the full list of people back with their zodiac sign. I must also be able to add new people to this table and get their sign returned. You can use any method you want and it doesn't matter if it will only run in the latest version of SQL Server.

The Winner
The shortest code wins...


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[AlexCuse]

Here goes (SQL 2005)

Spoiler

with zodSigns(zSign, sDate) as
(
select 'Capricorn', cast('19000101' as datetime)
union all select 'Aquarius', '19000120'
union all select 'Pisces', '19000219'
union all select 'Aries', '19000321'
union all select 'Taurus', '19000421'
union all select 'Gemini', '19000521'
union all select 'Cancer', '19000621'
union all select 'Leo', '19000722'
union all select 'Virgo', '19000822'
union all select 'Libra', '19000922'
union all select 'Scorpio', '19001023'
union all select 'Sagittarius', '19001122'
union all select 'Capricorn', '19001221'
)
select a.*, b.zSign from @PEOPLE a
inner join zodSigns b
on b.sDate = (select max(sDate) from zodSigns where dateadd(year, datediff(year, sDate, a.DOB), sDate) <= a.DOB)
;

--I decided I'd rather add the extra row for capricorn than put an OR in there.

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[Otto]

Spoiler

with Z (N, D) as (
select 'Aries', '0321'
union select 'Taurus', '0421'
union select 'Gemini', '0521'
union select 'Cancer', '0621'
union select 'Leo', '0722'
union select 'Virgo', '0822'
union select 'Libra', '0922'
union select 'Scorpio', '1023'
union select 'Sagittarius', '1122'
union select 'Capricorn', '1221'
union select 'Aquarius', '0120'
union select 'Pisces', '0219'
) select P.*, isnull((select top 1 N from Z where D<=right(convert(char(8), P.DOB, 112), 4) order by D desc), 'Capricorn') ZODIAC from @PEOPLE P

 

[ESquared]

420 characters (I had to include line breaks to not mess up this thread, take them out to measure):

[spoiler];WITH S AS(SELECT M=1,D=20,I=72160UNION ALL SELECT M+1,I%3+D+29,I/3FROM S WHERE M<12)
SELECT P.*, Substring('Aquarius   Pisces     Aries      Taurus     Gemini     Cancer     '
+ 'Leo        Virgo      Libra      Scorpio    SagittariusCapricorn',M*11-10,11)
FROM @People P INNER JOIN(SELECT B=id,T=Max(M)FROM S 
INNER JOIN @People P ON DatePart(y,DOB)>=D OR (DatePart(y,DOB)<20 AND M=12)
GROUP BY id)Z ON id=B INNER JOIN S ON T=M[/spoiler]

Alex's is 715 characters, with one character per line break.
Otto's is 522 characters, with no character per line break (he used spaces). Compressed the way my query is, they rate at:
Alex: 602
Otto: 472

And now, using ideas from their code, 324 characters (again, remove line breaks and string continuation):

[spoiler];WITH S AS(SELECT M=1,D=-11,I=216481UNION ALL SELECT M+1,I%3+D+29,I/3FROM S WHERE M<13)
SELECT P.*, Substring('Capricorn  Aquarius   Pisces     Aries      Taurus     Gemini     Cancer     '
+ 'Leo        Virgo      Libra      Scorpio    SagittariusCapricorn',
(SELECT Max(M)FROM S WHERE DatePart(dy,DOB)>=D)*11-10,11)FROM @People P[/spoiler]

Here are those two queries again with proper formatting, if you'd like to see them:

[spoiler];WITH S
AS(
   SELECT
      M = 1,
      D = 20,
      I = 72160
   UNION ALL SELECT
      M + 1,
      I % 3 + D + 29,
      I / 3
   FROM S
   WHERE M < 12
)
SELECT
   P.*,
   Substring('Aquarius   Pisces     Aries      Taurus     Gemini     Cancer     '
+ 'Leo        Virgo      Libra      Scorpio    SagittariusCapricorn', M * 11 - 10, 11)
FROM
   @People P
   INNER JOIN(
      SELECT B = id, T = Max(M)
      FROM
         S
         INNER JOIN @People P ON DatePart(y,DOB)>=D OR (DatePart(y,DOB) < 20 AND M = 12)
      GROUP BY id
   ) Z ON id = B
   INNER JOIN S ON T = M

;WITH S
AS (
   SELECT
      M = 1,
      D = -11,
      I = 216481
   UNION ALL SELECT
      M+1,
      I%3+D+29,
      I/3
   FROM S
   WHERE M<13
)
SELECT
   P.*,
   Substring(
      'Capricorn  Aquarius   Pisces     Aries      Taurus     Gemini     Cancer     '
+ 'Leo        Virgo      Libra      Scorpio    SagittariusCapricorn',
      (SELECT Max(M) FROM S WHERE DatePart(dy, DOB) >= D) * 11 - 10,
      11
   )
FROM @People P[/spoiler]

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[Lamprey13]

For fun:

[spoiler]SELECT 
	P.Name,
	Z.Z
FROM 
	@People P
INNER JOIN
	(
		SELECT 'Aries' Z, 80 S, 110 E
		UNION SELECT 'Taurus', 111, 140
		UNION SELECT 'Gemini', 141, 171
		UNION SELECT 'Cancer', 172, 202
		UNION SELECT 'Leo', 201, 233  
		UNION SELECT 'Virgo', 234, 264
		UNION SELECT 'Libra', 265, 295
		UNION SELECT 'Scorpio', 296, 325
		UNION SELECT 'Sagittarius', 326, 354
		UNION SELECT 'Capricorn', 355, 19
		UNION SELECT 'Aquarius', 20, 49
		UNION SELECT 'Pisces', 50, 79
	) Z
	ON DATEPART(DY, p.DOB) BETWEEN z.S AND z.E
	ORDER BY P.Name[/spoiler]

 

[Lamprey13]

Dang, forgot to test it before I sent it.. Scratch my entry as it doesn't work correctly.

 

[Lamprey13]

Dang hit the wrong button, this version should work correctly:

[spoiler]SELECT 
	P.Name,
	Z.Z
FROM 
	@People P
INNER JOIN
	(
		SELECT 'Aries' Z, 80 S, 110 E
		UNION SELECT 'Taurus', 111, 140
		UNION SELECT 'Gemini', 141, 171
		UNION SELECT 'Cancer', 172, 202
		UNION SELECT 'Leo', 201, 233  
		UNION SELECT 'Virgo', 234, 264
		UNION SELECT 'Libra', 265, 295
		UNION SELECT 'Scorpio', 296, 325
		UNION SELECT 'Sagittarius', 326, 354
		UNION SELECT 'Capricorn', 355, 365
		UNION SELECT 'Aquarius', 20, 49
		UNION SELECT 'Pisces', 50, 79
		UNION SELECT 'Capricorn', 1, 19
	) Z
	ON DATEPART(DY, p.DOB) BETWEEN z.S AND z.E
	ORDER BY P.Name[/spoiler]

 

[ESquared]

Oh, and I can lose a character by using

DatePart(y

instead of

DatePart(dy

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[SQLDenis]

Esquared you can also get rid off the aliases P. and P after @People

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Otto]

Before making the final decision who the winner is...
I'm sorry to say guys (ESquared and Lamprey13) but your codes, however small enough, are wrong. Both do mistakes in leap years. Try this:

INSERT @PEOPLE VALUES ('ESquared', '19680320');
INSERT @PEOPLE VALUES ('Lamprey13', '19680420');

Only AlexCuse's and mine give right results. But 'the time is not up' .

 

[ESquared]

Fruit monkeys! Here's a fix:

328 characters (remove line breaks and string split:

[spoiler];WITH S AS(SELECT 33D,1C,136226551I UNION ALL
 SELECT C*32+I%5+19,C+1,I/5 FROM S WHERE C<13)SELECT*,Substring('Capricorn  Aquarius   Pisces     Aries      Taurus     Gemini     '
+'Cancer     Leo        Virgo      Libra      Scorpio    SagittariusCapricorn',
(SELECT Max(C)FROM S WHERE Month(DOB)*32+Day(DOB)>=D)*11-10,11)FROM @People[/spoiler]
and here it is "uncompressed" if you want to see it:
[spoiler];WITH S
AS(
   SELECT
      D = 33,
      C = 1,
      I = 136226551
   UNION ALL SELECT
      C*32+I%5+19,
      C+1,
      I/5
   FROM S
   WHERE C<13
)
SELECT
   *,
   Substring(
      'Capricorn  Aquarius   Pisces     Aries      Taurus     Gemini     Cancer     '
+ 'Leo        Virgo      Libra      Scorpio    SagittariusCapricorn',
      (SELECT Max(C) FROM S WHERE Month(DOB)*32+Day(DOB)>=D)*11-10,
      11
   )
FROM @People[/spoiler]

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]