## The fun factor: Magical numbers and other peculiarities

 Author Message Koen Verbeeck SSCoach Group: General Forum Members Points: 16380 Visits: 13199 J Livingston SQL (6/16/2014)my effort...too much time on my hands :-D...Nice :-D How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence graham.smith Forum Newbie Group: General Forum Members Points: 7 Visits: 210 You all have too much time on your hands, Eirik, get back to work....... :-DGraham Eirikur Eiriksson SSCertifiable Group: General Forum Members Points: 6691 Visits: 17683 graham.smith (6/16/2014)You all have too much time on your hands, Eirik, get back to work....... :-DGrahamDon't worry my friend, this is a key to enjoying work! I have been laughing all the way to work and back, thank you guys! You would all do good in the Office of National Statistics:-PPlease don't stop, I'll contribute when I can;-) mickyT Ten Centuries Group: General Forum Members Points: 1253 Visits: 3309 J Livingston SQL (6/16/2014)my effort...too much time on my hands :-D`DECLARE @Question VARCHAR(100) = 'The Answer to the Ultimate Question of Life, the Universe, and Everything'DECLARE @Source VARCHAR(100) ='The Hitchhikers Guide to the Galaxy'DECLARE @Answer VARCHAR(10)SET NOCOUNT ONIF object_id('tempdb..#ParallelUniverse') IS NOT NULLDROP TABLE #ParallelUniverseSELECT top (len(@Source)) IDENTITY(INT, 1, 1) nINTO #ParallelUniverse FROM master.sys.all_columns SET @Answer = (select len(@Question) - (SELECT MAX(n) from ((SELECT n FROM #ParallelUniverse )EXCEPT(SELECT (PU1.n * PU2.n) AS cn FROM #ParallelUniverse AS PU1, #ParallelUniverse AS PU2 WHERE PU1.n BETWEEN 2 AND CEILING (SQRT (1000)) AND PU2.n BETWEEN 2 AND CEILING (SQRT (1000)) AND PU1.n <= PU2.n AND (PU1.n * PU2.n) <= 1000) )x)) PRINT @Question + ' is.. ' + @AnswerSET NOCOUNT OFF/*code to find primes courtesy of J Celko in this discussion:http://social.msdn.microsoft.com/Forums/sqlserver/en-US/90a58cc7-dada-476a-8d27-f744c71940e6/how-to-find-whether-is-a-prime-or-non-prime-with-one-select-statement?forum=transactsql*/`Awesome:-) mickyT Ten Centuries Group: General Forum Members Points: 1253 Visits: 3309 One to make you smile if you have 2012:-)`DECLARE @smiley varchar(20) = 'Dont Worry Be Happy';WITH Params AS (   SELECT MAX(CASE WHEN ItemNumber = 1 THEN Val END) OFFSET,      MAX(CASE WHEN ItemNumber = 2 THEN Val END) BASE,      MAX(CASE WHEN ItemNumber = 3 THEN Val END) BASEMULTIPLIER,      MAX(CASE WHEN ItemNumber = 4 THEN Val END) BASEDIVISOR   FROM (      SELECT ItemNumber, Item, LEN(Item) Val      FROM [dbo].[DelimitedSplit8K](@smiley,' ')       s   SELECT @smiley Quote, Geometry:oint(BASE * BASEMULTIPLIER, BASE * BASEMULTIPLIER,0).STBuffer(BASE * BASEMULTIPLIER).STDifference((   SELECT Geometry::UnionAggregate(g)   FROM (      SELECT Geometry:oint(BASE * BASEMULTIPLIER - OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params      UNION ALL      SELECT Geometry:oint(BASE * BASEMULTIPLIER + OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params      UNION ALL      SELECT Geometry::STGeomFromText(         CONCAT('CIRCULARSTRING(',             BASE * BASEMULTIPLIER - OFFSET, ' ', BASE * BASEMULTIPLIER - OFFSET, ', ',            BASE * BASEMULTIPLIER , ' ', (BASE * BASEMULTIPLIER - OFFSET) - ((BASE * BASEMULTIPLIER) / (BASE * BASEMULTIPLIER - OFFSET)), ', ',             BASE * BASEMULTIPLIER + OFFSET, ' ', BASE * BASEMULTIPLIER - OFFSET, ')'),0).STBuffer(BASE / BASEDIVISOR) g FROM Params          a      ) Smiley FROM Params;--Now switch to Spatial Results Tab `Edit: Tweaked Slightly Koen Verbeeck SSCoach Group: General Forum Members Points: 16380 Visits: 13199 mickyT (6/16/2014)One to make you smile if you have 2012:-)...Msg 208, Level 16, State 1, Line 3Invalid object name 'dbo.DelimitedSplit8K'. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence mickyT Ten Centuries Group: General Forum Members Points: 1253 Visits: 3309 Koen Verbeeck (6/17/2014)mickyT (6/16/2014)One to make you smile if you have 2012:-)...Msg 208, Level 16, State 1, Line 3Invalid object name 'dbo.DelimitedSplit8K'.Sorry, this function is from this article by Jeff Moden. Koen Verbeeck SSCoach Group: General Forum Members Points: 16380 Visits: 13199 mickyT (6/17/2014)Koen Verbeeck (6/17/2014)mickyT (6/16/2014)One to make you smile if you have 2012:-)...Msg 208, Level 16, State 1, Line 3Invalid object name 'dbo.DelimitedSplit8K'.Sorry, this function is from this article by Jeff Moden.I know, and it should be standard on every database :-D(but apparently not on my test database) How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence Eirikur Eiriksson SSCertifiable Group: General Forum Members Points: 6691 Visits: 17683 If Einstein would have had SQL Server, it would have been so much easier:-D`USE tempdb;GO;WITH SQL_VISDOM AS( SELECT ROW_NUMBER() OVER (ORDER BY (SAO.object_id ^ -1)) AS SCHOOL_YEARS ,SAO.object_id + ROW_NUMBER() OVER (ORDER BY (SAO.object_id ^ -1)) AS SCHOOL_ATTEMPTS ,SAO.object_id ^ -1 SCHOOL_FAILURES FROM sys.all_objects SAO WHERE SAO.object_id < 0),DOING_LEARNING AS( SELECT SV.SCHOOL_YEARS ,SV.SCHOOL_ATTEMPTS ,SV.SCHOOL_FAILURES ,DENSE_RANK() OVER (ORDER BY (SV.SCHOOL_ATTEMPTS ^ -1)) AS TEST_RATING ,RANK() OVER (ORDER BY (SV.SCHOOL_ATTEMPTS ^ -1)) AS TEST_GROUP ,SV.SCHOOL_YEARS * DENSE_RANK() OVER (ORDER BY (SV.SCHOOL_ATTEMPTS ^ -1)) + (SV.SCHOOL_ATTEMPTS % 100) AS YEARS_LEFT FROM SQL_VISDOM SV)SELECT CHAR( DL.TEST_GROUP * DL.TEST_RATING) + CHAR((DL.TEST_GROUP * DL.TEST_RATING) + POWER(DL.SCHOOL_FAILURES + DL.SCHOOL_ATTEMPTS - (DL.SCHOOL_YEARS - DL.TEST_RATING),DL.TEST_RATING) * SIGN(DL.SCHOOL_ATTEMPTS)) + CHAR((DL.TEST_GROUP * DL.TEST_RATING) + POWER(DL.SCHOOL_FAILURES + DL.SCHOOL_ATTEMPTS - (DL.SCHOOL_YEARS - DL.TEST_RATING),DL.TEST_RATING) * SIGN(ABS(DL.SCHOOL_ATTEMPTS))) + CHAR(ABS((DL.SCHOOL_ATTEMPTS + DL.SCHOOL_YEARS) + CEILING(TEST_GROUP / 2)) / 2) + CHAR((DL.TEST_GROUP * 2 + POWER(DL.SCHOOL_ATTEMPTS,DL.YEARS_LEFT)) * 2) + CHAR(((DL.TEST_GROUP * (CEILING(DL.TEST_RATING / 2)) * 2) + FLOOR(SQRT(DL.SCHOOL_YEARS))))FROM DOING_LEARNING DLWHERE DL.YEARS_LEFT = 0;` graham.smith Forum Newbie Group: General Forum Members Points: 7 Visits: 210 Brilliant Erik, love it. :-D