SQL Server Performance

Astrological Signs from Datetime

Discussion in 'SQL Server 2005 General Developer Questions' started by ryoh, Dec 14, 2006.

  1. ryoh New Member

    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
  2. dineshasanka Moderator

  3. FrankKalis Moderator

    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
  4. FrankKalis Moderator

    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
  5. FrankKalis Moderator

    ...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
  6. FrankKalis Moderator

    LOL, this is a one-man thread. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Supposed you have the hypothetical case that one member on your payroll exhibits the following values:<br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #employees SELECT 'Julian', '20010829'<br /></font id="code"></pre id="code"><br />the % 100 trick won't work anymore. Which in turn means, you have to resort to the first statement:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT a.a_sign, COUNT(a.a_sign) AS No_of_belongers<br /> FROM #employees e<br /> JOIN #astrological_signs a<br /> ON DATEADD(yyyy, @year - YEAR(e.birthday), e.birthday) <br /> BETWEEN DATEADD(yyyy, @year - YEAR(a.start_sign), a.start_sign)<br /> AND DATEADD(yyyy, @year - YEAR(a.end_sign), a.end_sign)<br /> GROUP BY a.a_sign<br /></font id="code"></pre id="code"><br />which adjust everything to the current year, and not to the base year of 1900.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. ryoh New Member

    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.
  8. ryoh New Member

    Hi Frank,

    PERFECT! I got it to work.

    Roger
  9. FrankKalis Moderator

    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=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. Madhivanan Moderator

    &lt;&lt;<br />LOL, this is a one-man thread. <br />&gt;&gt;<br /><br />As long as you give more informations, we are fine with that [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. MohammedU New Member

    ryoh...

    I live in California too... can you post the results to see the pattern...


    Mohammed U.
  12. alzdba Member

    - must be a psychic insurance company order <img src='/community/emoticons/emotion-1.gif' alt=':)' />)<br /><br />
  13. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  14. ryoh New Member

    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
  15. FrankKalis Moderator

    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=':D' />] <br /><br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. khtan New Member

    <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=':D' />]<br /><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">
  17. ryoh New Member

    Thank you all for the feedback. I have lots to learn!
  18. jdx New Member

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

Share This Page