Meme Monday SQLstrology – Table Valued Zodiac Function

It’s Meme Monday time! Our topic is SQL Horoscopes, or more catchily, SQLStrology.

We’re going to have to figure out what astrological sign our SQL Server instance has – when it was “born” (installed) – before we can apply any kind of horoscope predictions.  I’ll start with the base query to find the installation date:

SELECT  createdate as Sql_Server_Install_Date
FROM    sys.syslogins
where   sid = 0x010100000000000512000000 -- language neutral
        -- loginname = 'NT AUTHORITY\SYSTEM' -- only English language installations

(Thanks to Mladen P for the SQL Server install date query.)

Now let’s turn this into a function we can use on any SQL Server instance.  There are several different ways to code this function; I chose to go with a table variable return, and a table variable to hold the Zodiac fields; I could just have easily accomplished this with a CASE statement to populate the return table, but I think readability would suffer:

CREATE FUNCTION dbo.GetServerHoroscope()
RETURNS @ServerHoroscope TABLE (
    ServerName NVARCHAR(200),
    SqlServerInstallDate DATE,
    ZodiacSign NVARCHAR(20),
    QuickCharacteristics  NVARCHAR(400))
AS
/*=============================================
 Author:  Jennifer McCown Jen@MidnightDBA.com  http://www.MidnightDBA.com/Jen/ 

 Create date: 7/11/2011

 Copyright:
    This work by Jennifer McCown is licensed under a Creative Commons
    Attribution-Noncommercial-No Derivative Works 3.0 United States License.

http://creativecommons.org/licenses/by-nc-nd/3.0/us/

    (Which means, use it all you want, break it up, whatever...as long as you
    attribute me.)

 -------------------------
 Description:
    This function uses the SQL Server installation date to determine the installation's
    zodiac sign, and also returns quick astrological characteristics.

 -------------------------
 Example execution:
    SELECT * FROM dbo.GetServerHoroscope();

=============================================*/
BEGIN
    -----------------------
    -- DECLARE VARIABLES --
    -----------------------
    DECLARE @SqlServerInstallDate DATE;

    DECLARE @SQLStrology TABLE (
        ZodiacSign NVARCHAR(20),
        START  NVARCHAR(20),
        [END]  NVARCHAR(20),
        StartDate DATE,
        EndDate DATE,
        QuickCharacteristics  NVARCHAR(400));

    ------------------------
    -- POPULATE VARIABLES --
    ------------------------

    /*  Thanks to Mladen Pradjic for this SQLServerInstallDate query. See
        http://weblogs.sqlteam.com/mladenp/archive/2009/07/16/How-to-check-when-was-SQL-Server-installed-with-a.aspx 
     */
    SELECT @SqlServerInstallDate = createdate
    FROM    sys.syslogins
    WHERE   sid = 0x010100000000000512000000 -- language neutral
            -- loginname ='NT AUTHORITY\SYSTEM' -- only English language installations

    /* Zodiac dates and characteristics pulled from http://www.whats-your-sign.com/zodiac-sign-dates.html  */
    INSERT INTO     @SQLStrology (ZodiacSign,START, [END], QuickCharacteristics)
    VALUES    ('Aries', '03/21', '04/9', 'Active, Demanding, Determined, Effective, Ambitious'),
            ('Taurus', '04/20', '05/20', 'Security, Subtle strength, Appreciation, Instruction, Patience'),
            ('Gemini', '05/21', '06/20', 'Communication, Indecision, Inquisitive, Intelligent, Changeable'),
            ('Cancer', '06/21', '07/22', 'Emotion, Diplomatic, Intensity, Impulsive, Selective'),
            ('Leo', '07/23', '08/22', 'Ruling, Warmth, Generosity, Faithful, Initiative'),
            ('Virgo', '08/23', '09/22', 'Analyzing, Practical, Reflective, Observation, Thoughtful'),
            ('Libra', '09/23', '10/22', 'Balance, Justice, Truth, Beauty, Perfection'),
            ('Scorpio', '10/23', '11/21', 'Transient, Self-Willed, Purposeful, Unyielding'),
            ('Sagittarius', '11/22', '12/21', 'Philosophical, Motion, Experimentation, Optimism'),
            ('Capricorn', '12/22', '01/19', 'Determination, Dominance, Perservering, Practical, Willful'),
            ('Aquarius', '01/20', '02/18', 'Knowledge, Humanitarian, Serious, Insightful, Duplicitous'),
            ('Pisces', '02/19', '03/20', 'Fluctuation, Depth, Imagination, Reactive, Indecisive');

    -- Use the NVARCHAR start and end date strings and the SQL Server install date to build actual zodiac dates:
    UPDATE @SQLStrology SET StartDate = CAST(START + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE),
        EndDate = CAST([END] + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE);

    -- Insert the one applicable row into the output table:
    INSERT INTO @ServerHoroscope
    SELECT @@ServerName     as ServerName
        , @SqlServerInstallDate  AS SqlServerInstallDate
        , ZodiacSign
        , QuickCharacteristics
    FROM @SQLStrology
    WHERE @SqlServerInstallDate BETWEEN StartDate AND EndDate;

    ------------
    -- RETURN --
    ------------

    RETURN;
END

This was a fun exercise. For more on multi-statement table-valued functions, see the SQL Server Books Online article CREATE FUNCTION, especially example C.

I look forward to updating the function with your SQL-specific SQLstrology characteristics.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

3 thoughts on “Meme Monday SQLstrology – Table Valued Zodiac Function”

  1. Ok, so your post is two years old now, but there’s a bug! I thought you should know. Aries should end on April 19th, not April 9th! Your code excludes dates between April 10th and April 19th. :-)

    I’ve been writing a similar function and this was helpful. I ended up splitting Capricorn into two records to avoid the year boundary, which lets me do a ‘MM/DD’ BETWEEN START AND END style join to the temporary table you build… this way I can do a mass load of Zodiac Signs into a date dimension table I have.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>