SQL Server Performance Forum – Threads Archive

Astrological Signs from Datetime

Hi all, I have a datetime field containing employees’ birthday. How can I use the information to determine how many people belong in each of the astrological sign? Thank you in advance. Roger

from this linkhttp://www.newagedirectory.com/love/astrology.htm, you can find the astrological if you know the birthday —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

I would use an auxiliary help table and do something like:
CREATE TABLE #employees
(
fname VARCHAR(10)
, birthday DATETIME)
INSERT INTO #employees SELECT ‘Frank’, ‘19680723’
INSERT INTO #employees SELECT ‘Julian’, ‘19990829’ CREATE TABLE #astrological_signs
(
a_sign VARCHAR(10)
, start_sign DATETIME
, end_sign DATETIME
)
INSERT INTO #astrological_signs SELECT ‘Leo’, ‘19000723’, ‘19000823’
INSERT INTO #astrological_signs SELECT ‘Virgo’, ‘19000824’, ‘19000922’ DECLARE @year INT
SET @year = YEAR(GETDATE()) SELECT e.fname, DATEADD(yyyy, @year – YEAR(e.birthday), e.birthday), a.a_sign
FROM #employees e
JOIN #astrological_signs a
ON DATEADD(yyyy, @year – YEAR(e.birthday), e.birthday)
BETWEEN DATEADD(yyyy, @year – YEAR(a.start_sign), a.start_sign)
AND DATEADD(yyyy, @year – YEAR(a.end_sign), a.end_sign) DROP TABLE #employees, #astrological_signs fname a_sign
———- —————————————————— ———-
Frank 2006-07-23 00:00:00.000 Leo
Julian 2006-08-29 00:00:00.000 Virgo (2 row(s) affected)
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Sorry, you can simplify this to:
SELECT e.fname, DATEADD(yyyy, @year – YEAR(e.birthday), e.birthday), a.a_sign
FROM #employees e
JOIN #astrological_signs a
ON DATEADD(yyyy, -YEAR(e.birthday) % 100, e.birthday)
BETWEEN a.start_sign
AND a.end_sign
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

…sometimes it really helps to read the questions carefully. If you simply want to determine the COUNT for each group, do:
SELECT a.a_sign, COUNT(a.a_sign) AS No_of_belongers
FROM #employees e
JOIN #astrological_signs a
ON DATEADD(yyyy, -YEAR(e.birthday) % 100, e.birthday)
BETWEEN a.start_sign
AND a.end_sign
GROUP BY a.a_sign —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

LOL, this is a one-man thread. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Supposed you have the hypothetical case that one member on your payroll exhibits the following values: <pre id="code"> INSERT INTO #employees SELECT ‘Julian’, ‘20010829’ </pre id="code"> the % 100 trick won’t work anymore. Which in turn means, you have to resort to the first statement: <pre id="code"> SELECT a.a_sign, COUNT(a.a_sign) AS No_of_belongers FROM #employees e JOIN #astrological_signs a ON DATEADD(yyyy, @year – YEAR(e.birthday), e.birthday) BETWEEN DATEADD(yyyy, @year – YEAR(a.start_sign), a.start_sign) AND DATEADD(yyyy, @year – YEAR(a.end_sign), a.end_sign) GROUP BY a.a_sign </pre id="code"> which adjust everything to the current year, and not to the base year of 1900. – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Frank, LOL. No, it’s not a one-man tread. I’m in California and just woke up and read the posts. Thank you very much for all the pointers. Let me explain what I am trying to do. I have two tables here. I want to use the employee ID and birthday from table_1, and the empolyee ID and attendance records in table_2. The objective is to find out which group of astrological sign miss work the most. I want my final recordset to look like something like this: ================ Employees who missed work for more than 10 days in the last fiscal year.
Aries 21
Leo 5
Gemini 8
… etc ================ I run into problem because I do not know how to convert the birthday into astrological sign.

Hi Frank, PERFECT! I got it to work. Roger

Your explanation makes me curious. What information can someone derive from a report showing how many "Leos" missed more than 10 working days and why? That sounds kinda freaky. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<< LOL, this is a one-man thread. >> As long as you give more informations, we are fine with that [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

ryoh… I live in California too… can you post the results to see the pattern…
Mohammed U.

– must be a psychic insurance company order <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />)

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Hi all, Sorry I have not been back to view the posts on the forums for about a month. I was wondering about the boss’s intent as well. I guess it’s an Asian thing that companies like to categorize employees in certain fashion so they can use the data to deal with certain types of employees. I got the result and the absentee rate is pretty evenly distributed among all the astrological signs. The difference is less than 1% with Cancer being the highest and Sagittarius the lowest. I guess what I learned was, well, NOTHING! LOL Roger

Thanks for your feedback. This is quite interesting. Probably not practical applicable, but nonetheless quite interesting. There is a whole industry that does nothing else but collect data and analyse that data back to front, left to right and back again. And apparently they are doing pretty well. I wouldn’t say you’ve learned nothing, in fact you probably haven’t tried hard enough to find its hidden value. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">The difference is less than 1% with Cancer being the highest and Sagittarius the lowest. I guess what I learned was, well, NOTHING! <hr height="1" noshade id="quote"></blockquote id="quote"> Not true. The lesson to be learn here is not to higher Cancer. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <hr noshade size="1"> KH

Thank you all for the feedback. I have lots to learn!

Finding that there is no variation is still a valid result. It probably seems obvious but it is still useful data.

]]>