SQL Clone
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63632 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
graham.smith
graham.smith
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

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

Graham
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41304 Visits: 19504
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 3318
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 3318
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: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
) a
)) Smiley
FROM Params;
--Now switch to Spatial Results Tab Smile



Edit: Tweaked Slightly
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63632 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 3318
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63632 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41304 Visits: 19504
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
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 232
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