Post reply

The fun factor: Magical numbers and other peculiarities

  • June 17, 2014 at 12:10 am

    #1722131

    mickyT (6/16/2014)

    One to make you smile if you have 2012:-)

    ...

    Msg 208, Level 16, State 1, Line 3

    Invalid object name 'dbo.DelimitedSplit8K'.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 17, 2014 at 12:24 am

    #1722136

    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 3

    Invalid object name 'dbo.DelimitedSplit8K'.

    Sorry, this function is from this article[/url] by Jeff Moden.

  • June 17, 2014 at 12:29 am

    #1722137

    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 3

    Invalid object name 'dbo.DelimitedSplit8K'.

    Sorry, this function is from this article[/url] by Jeff Moden.

    I know, and it should be standard on every database 😀

    (but apparently not on my test database)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 18, 2014 at 1:23 am

    #1722611

    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 DL

    WHERE DL.YEARS_LEFT = 0;

  • June 18, 2014 at 2:16 am

    #1722628

    Brilliant Erik, love it. 😀

  • June 18, 2014 at 10:17 am

    #1722870

    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::Point(BASE * BASEMULTIPLIER, BASE * BASEMULTIPLIER,0).STBuffer(BASE * BASEMULTIPLIER).STDifference((

    SELECT Geometry::UnionAggregate(g)

    FROM (

    SELECT Geometry::Point(BASE * BASEMULTIPLIER - OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params

    UNION ALL

    SELECT Geometry::Point(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

    That is smoooth!

    😎

  • June 18, 2014 at 1:53 pm

    #1722976

    Eirikur Eiriksson (6/18/2014)

    If Einstein would have had SQL Server, it would have been so much easier:-D

    😎

    Awesome:cool:

  • June 19, 2014 at 2:20 am

    #1723093

    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::Point(BASE * BASEMULTIPLIER, BASE * BASEMULTIPLIER,0).STBuffer(BASE * BASEMULTIPLIER).STDifference((

    SELECT Geometry::UnionAggregate(g)

    FROM (

    SELECT Geometry::Point(BASE * BASEMULTIPLIER - OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params

    UNION ALL

    SELECT Geometry::Point(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

    That 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 problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • June 19, 2014 at 2:35 am

    #1723095

    mister.magoo (6/19/2014)

    That is VERY nice 🙂

    Now I have to learn how to do that!

    Aw shucks .. thank you:blush:

  • June 24, 2014 at 9:54 am

    #1724389

    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 DL

    WHERE DL.YEARS_LEFT = 0;

    That is sweet! Now where's the proof? 😉

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

    • Viewing 10 posts - 16 through 24 (of 24 total)

    You must be logged in to reply to this topic. Login to reply