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 Wednesday, June 18, 2014 10:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 1,914, Visits: 5,276
mickyT (6/16/2014)
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


That is smoooth!
Post #1583126
Posted Wednesday, June 18, 2014 1:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:08 PM
Points: 1,027, Visits: 3,085
Eirikur Eiriksson (6/18/2014)
If Einstein would have had SQL Server, it would have been so much easier



Awesome
Post #1583393
Posted Thursday, June 19, 2014 2:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:47 PM
Points: 1,778, Visits: 5,730
mickyT (6/16/2014)
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


That is VERY nice

Now I have to learn how to do that!


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 #1583629
    Posted Thursday, June 19, 2014 2:35 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Yesterday @ 9:08 PM
    Points: 1,027, Visits: 3,085
    mister.magoo (6/19/2014)

    That is VERY nice

    Now I have to learn how to do that!

    Aw shucks .. thank you
    Post #1583633
    Posted Tuesday, June 24, 2014 9:54 AM
    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 6:49 AM
    Points: 3,991, Visits: 3,428
    Eirikur Eiriksson (6/18/2014)
    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;


    That is sweet! Now where's the proof?



    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions
    Post #1585576
    « Prev Topic | Next Topic »

    Add to briefcase «««123

    Permissions Expand / Collapse