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 Monday, June 16, 2014 4:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
J Livingston SQL (6/16/2014)
my effort...too much time on my hands

...


Nice




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 #1581053
Posted Monday, June 16, 2014 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:36 AM
Points: 3, Visits: 158
You all have too much time on your hands, Eirik, get back to work.......

Graham
Post #1581062
Posted Monday, June 16, 2014 1:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,003, Visits: 5,470
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
Please don't stop, I'll contribute when I can

Post #1581568
Posted Monday, June 16, 2014 1:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,040, Visits: 3,106
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
Post #1581581
Posted Monday, June 16, 2014 4:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,040, Visits: 3,106
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
Post #1581956
Posted Tuesday, June 17, 2014 12:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582036
Posted Tuesday, June 17, 2014 12:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,040, Visits: 3,106
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.
Post #1582045
Posted Tuesday, June 17, 2014 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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
(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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582048
Posted Wednesday, June 18, 2014 1:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,003, Visits: 5,470
If Einstein would have had SQL Server, it would have been so much easier


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;

Post #1582713
Posted Wednesday, June 18, 2014 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:36 AM
Points: 3, Visits: 158
Brilliant Erik, love it.
Post #1582732
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse