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. Rogerfrom 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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
<<<br />LOL, this is a one-man thread. <br />>><br /><br />As long as you give more informations, we are fine with that [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
[<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
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. <br /><br />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=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" 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"></font id="quote"></blockquote id="quote"><br />Not true. The lesson to be learn here is not to higher Cancer. [<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
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.
]]>