Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The fun factor: Magical numbers and other peculiarities


The fun factor: Magical numbers and other peculiarities

Author
Message
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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
graham.smith
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 210
You all have too much time on your hands, Eirik, get back to work....... :-D

Graham
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

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....... :-D

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;-)

Cool
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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 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:-)
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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,' ')
      Wink s
   Wink
SELECT @smiley Quote, Geometry:Tongueoint(BASE * BASEMULTIPLIER, BASE * BASEMULTIPLIER,0).STBuffer(BASE * BASEMULTIPLIER).STDifference((
   SELECT Geometry::UnionAggregate(g)
   FROM (
      SELECT Geometry:Tongueoint(BASE * BASEMULTIPLIER - OFFSET, BASE * BASEMULTIPLIER + OFFSET,0).STBuffer(BASE / BASEDIVISOR) g FROM Params
      UNION ALL
      SELECT Geometry:Tongueoint(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
         Wink a
      Wink) Smiley
FROM Params;
--Now switch to Spatial Results Tab Smile



Edit: Tweaked Slightly
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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 3
Invalid 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
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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 3
Invalid object name 'dbo.DelimitedSplit8K'.

Sorry, this function is from this article by Jeff Moden.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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 3
Invalid 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
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6691 Visits: 17683
If Einstein would have had SQL Server, it would have been so much easier:-D
Cool

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;


graham.smith
graham.smith
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 210
Brilliant Erik, love it. :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search