## The fun factor: Magical numbers and other peculiarities

 Author Message Eirikur Eiriksson SSC Guru Group: General Forum Members Points: 93485 Visits: 20649 mickyT (6/16/2014)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 SlightlyThat is smoooth! mickyT SSCertifiable Group: General Forum Members Points: 5752 Visits: 3320 Eirikur Eiriksson (6/18/2014)If Einstein would have had SQL Server, it would have been so much easier:-DAwesome mister.magoo One Orange Chip Group: General Forum Members Points: 26350 Visits: 7939 mickyT (6/16/2014)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 SlightlyThat is VERY nice :-)Now I have to learn how to do that! MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw mickyT SSCertifiable Group: General Forum Members Points: 5752 Visits: 3320 mister.magoo (6/19/2014)That is VERY nice :-)Now I have to learn how to do that!Aw shucks .. thank you Ed Wagner SSC Guru Group: General Forum Members Points: 156365 Visits: 11650 Eirikur Eiriksson (6/18/2014)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;`That is sweet! Now where's the proof? ;-) Tally Tables - Performance PersonifiedString Splitting with True PerformanceBest practices on how to ask questions