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
Pingback: Meme Monday: SQL Horoscope: Finding Your Chinese Zodiac | SQLSoldier
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!
Pingback: Adding a quick lunar calendar to the data mart « The Happy Technologist
There are some pay for each lead programs out there, but I do not like those much.
If you don’t have a video camera, you can create dynamic videos by
uploading digital photos onto websites such as animoto.
Visit local photography stores for resources, and perhaps visit
the Center for Creative Photography at the U of A.