Home » sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

Meme Monday SQLstrology – Table Valued Zodiac Function

July 11, 2011 sql server, sqlserverpedia-syndication, SSC, TSQL 3 Comments

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

Currently there are "3 comments" on this Article:

  1. [...] time for July’s Meme Monday post, and this month’s topic is SQL Horoscopes. I read Jen McCown’s post, Meme Monday SQLstrology – Table Valued Zodiac Function, and it inspired me to write a simple function for finding the Chinese Zodiac animal for any given [...]

  2. Chip Lynch says:

    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!

  3. [...] Just for more fun, I did something similar with Zodiac Sign… I'll leave the actual analysis up to you, but here's some code that will help generate the zodiac signs in your date dimension (Transact SQL syntax, but that's easily repaired… thanks to http://www.midnightdba.com/Jen/2011/07/meme-monday-sqlstrology-table-valued-zodiac-function/): [...]

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!




Monday, Oct 27 12:00PM CDT: Attend the Minion Reindex Intro Webinar.

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/