Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

The fun factor: Magical numbers and other peculiarities Expand / Collapse
Author
Message
Posted Sunday, June 15, 2014 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 2,228, Visits: 6,032
Looking for something that brings a smile, 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

Post #1580933
Posted Sunday, June 15, 2014 1:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 1,057, Visits: 3,127
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))

Post #1580956
Posted Sunday, June 15, 2014 3:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 2,228, Visits: 6,032
mickyT (6/15/2014)
To stick with a theme

Brilliant!
Post #1580968
Posted Sunday, June 15, 2014 4:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 1,057, Visits: 3,127
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

Post #1580969
Posted Sunday, June 15, 2014 4:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 1,796, Visits: 5,801
It's not very fancy, but I have found a query where NOLOCK really is desirable...

SELECT result FROM fifaworldcup2014 WITH(NOLOCK) WHERE team='ENGLAND'

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


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1580971
    Posted Sunday, June 15, 2014 6:10 PM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: 2 days ago @ 7:48 PM
    Points: 1,057, Visits: 3,127
    Promise this will be my last one

    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)

    Post #1580973
    Posted Monday, June 16, 2014 12:02 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 11:42 AM
    Points: 13,295, Visits: 11,086
    SELECT 'Last Time Belgium Won the World Cup' = 1/0;





    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 LessThanDot.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    Post #1580995
    Posted Monday, June 16, 2014 12:48 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 5:59 PM
    Points: 2,228, Visits: 6,032
    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 WITH(NOLOCK) 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.'

    Post #1581003
    Posted Monday, June 16, 2014 1:08 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:32 PM
    Points: 1,796, Visits: 5,801
    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 WITH(NOLOCK) 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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1581008
    Posted Monday, June 16, 2014 4:18 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: 2 days ago @ 4:06 AM
    Points: 1,917, Visits: 19,596
    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
    Post #1581049
    « Prev Topic | Next Topic »

    Add to briefcase 123»»»

    Permissions Expand / Collapse