The fun factor: Magical numbers and other peculiarities

  • Looking for something that brings a smile:-D, here's the first contribution

    😎

    DECLARE @QUESTION VARCHAR(50) = 'Last time England won the World Cup?';

    SELECT CONVERT(BIGINT,CONVERT(VARBINARY(50),@QUESTION,0),1) ^ 0x726C642043757791

  • To stick with a theme 🙂

    DECLARE @Team1 VARCHAR(10) = 'Italy'

    DECLARE @Team2 VARCHAR(10) = 'England'

    SELECT @Team1 + ': ' + CAST(LEN(@Team1) % 3 AS VARCHAR(2)),

    @Team2 + ': ' + CAST(LEN(@Team2) % 3 AS VARCHAR(2))

  • mickyT (6/15/2014)


    To stick with a theme 🙂

    :-DBrilliant!

    😎

  • Without wanting to pick on any nation in particular;-)

    SELECT TeamName + ': '+ CAST(Score AS VARCHAR(2))

    FROM (VALUES ('England'), ('All Blacks')) team (TeamName)

    CROSS APPLY (

    SELECT 4 * LEN(REPLACE(REPLACE(TeamName,SUBSTRING(TeamName,1,1),''),' ','')) +

    (LEN(TeamName) % 4) * (LEN(TeamName) % 2)

    ) result(Score)

    ORDER BY TeamName

  • It's not very fancy, but I have found a query where NOLOCK really is desirable...

    SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'

    Here's hoping for that "incorrect data" we are constantly warned about 😛

    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]

  • Promise this will be my last one:Whistling:

    DECLARE @Question VARCHAR(50) = 'Year that England last beat Italy?'

    SELECT CAST(

    POWER(LEN(Italy),ItalyLastScore) *

    POWER(LEN(England),EnglandLastScore) *

    (LEN(@Question) / (ItalyLastScore + EnglandLastScore))

    - (ItalyTotalWins - EnglandTotalWins)

    - (TotalDraws / 2)

    AS INT

    )

    FROM (VALUES ('Italy',2.0,'England',1.0,11.0,8.0,6.0))

    Stats(Italy, ItalyLastScore,

    England, EnglandLastScore,

    ItalyTotalWins, EnglandTotalWins, TotalDraws)

  • SELECT 'Last Time Belgium Won the World Cup' = 1/0;

    🙁

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

  • mister.magoo (6/15/2014)


    It's not very fancy, but I have found a query where NOLOCK really is desirable...

    SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'

    Here's hoping for that "incorrect data" we are constantly warned about 😛

    Tried the code but something is wrong, constantly got the same error message

    😎

    'Transaction (Process ID 2014) was lost on resources with another process

    and has been chosen as the tournament victim. Rerun tournament.'

  • Eirikur Eiriksson (6/16/2014)


    mister.magoo (6/15/2014)


    It's not very fancy, but I have found a query where NOLOCK really is desirable...

    SELECT result FROM fifaworldcup2014 [highlight="#ffff11"]WITH(NOLOCK)[/highlight] WHERE team='ENGLAND'

    Here's hoping for that "incorrect data" we are constantly warned about 😛

    Tried the code but something is wrong, constantly got the same error message

    😎

    'Transaction (Process ID 2014) was lost on resources with another process

    and has been chosen as the tournament victim. Rerun tournament.'

    Use trace flag 1966

    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]

  • my effort...too much time on my hands 😀

    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 ON

    IF object_id('tempdb..#ParallelUniverse') IS NOT NULL

    DROP TABLE #ParallelUniverse

    SELECT top (len(@Source)) IDENTITY(INT, 1, 1) n

    INTO #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.. ' + @Answer

    SET 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

    */

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (6/16/2014)


    my effort...too much time on my hands 😀

    ...

    Nice 😀

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

  • You all have too much time on your hands, Eirik, get back to work....... 😀

    Graham

  • graham.smith (6/16/2014)


    You all have too much time on your hands, Eirik, get back to work....... 😀

    Graham

    Don'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:-P

    Please don't stop, I'll contribute when I can;-)

    😎

  • J Livingston SQL (6/16/2014)


    my effort...too much time on my hands 😀

    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 ON

    IF object_id('tempdb..#ParallelUniverse') IS NOT NULL

    DROP TABLE #ParallelUniverse

    SELECT top (len(@Source)) IDENTITY(INT, 1, 1) n

    INTO #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.. ' + @Answer

    SET 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:-)

  • 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

  • Viewing 15 posts - 1 through 15 (of 24 total)

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