locked
Christmas SQL RRS feed

  • General discussion

  • Some graphics with T-SQL (must view Spatial results).

    USE tempdb
    GO
    	 
    	 
    -- Prepare the scene 
     
    CREATE TABLE #ChristmasScene 
     
        ( 
    
         item VARCHAR(32) 
     
        ,shape GEOMETRY 
        ) ; 
    
    --Put up the tree and star 
     
    INSERT  INTO #ChristmasScene 
     
    	VALUES  ( 'Tree', 
    	 
             'POLYGON((4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6, 4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0))' ), 
     
            ( 'Base', 'POLYGON((2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0))' ), 
     
            ( 'Star', 
     
             'POLYGON((4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2, 4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5))' ) 
    	 
    --Decorate the tree 
     
    DECLARE @i INT = 0 
     
       ,@x INT
     
       ,@y INT ; 
    
    WHILE ( @i < 20 ) 
    	 
        BEGIN
     
            INSERT  INTO #ChristmasScene 
    	 
            VALUES  ( 'Bauble' + CAST(@i AS VARCHAR(8)), 
     
                      GEOMETRY::Point(RAND() * 5 + 1.5, RAND() * 6, 0).STBuffer(0.3) ) 
    	 
            SET @i = @i + 1 ; 
    
        END
     
     --Christmas Greeting 
    	 
    INSERT  INTO #ChristmasScene 
    
    VALUES  ( 'M', 
     
             'POLYGON((0 10, 0 11, 0.25 11, 0.5 10.5, 0.75 11, 1 11, 1 10, 0.75 10, 0.75 10.7, 0.5 10.2, 0.25 10.7, 0.25 10, 0 10))' ), 
     
            ( 'E', 
     
             'POLYGON((1 10, 1 11, 2 11, 2 10.8, 1.25 10.8, 1.25 10.6, 1.75 10.6, 1.75 10.4, 1.25 10.4, 1.25 10.2, 2 10.2, 2 10, 1 10))' ), 
     
            ( 'R', 
    
             'POLYGON((2 10, 2 11, 3 11, 3 10.5, 2.4 10.5, 3 10, 2.7 10, 2.2 10.4, 2.2 10, 2 10),(2.2 10.8, 2.8 10.8, 2.8 10.7, 2.2 10.7, 2.2 10.8))' ), 
     
            ( 'R', 
     
             'POLYGON((3 10, 3 11, 4 11, 4 10.5, 3.4 10.5, 4 10, 3.7 10, 3.2 10.4, 3.2 10, 3 10),(3.2 10.8, 3.8 10.8, 3.8 10.7, 3.2 10.7, 3.2 10.8))' ), 
     
            ( 'Y', 
     
             'POLYGON((4 11, 4.2 11, 4.5 10.6, 4.8 11, 5 11, 4.6 10.5, 4.6 10, 4.4 10, 4.4 10.5, 4 11))' ), 
     
            ( 'C', 
     
    	         'POLYGON((0 9, 0 10, 1 10, 1 9.8, 0.2 9.8, 0.2 9.2, 1 9.2, 1 9, 0 9))' ), 
     
           ( 'H', 
     
             'POLYGON((1 9, 1 10, 1.2 10, 1.2 9.6, 1.8 9.6, 1.8 10, 2 10, 2 9, 1.8 9, 1.8 9.4, 1.2 9.4, 1.2 9, 1 9))' ), 
     
    	        ( 'R', 
     
             'POLYGON((2 9, 2 10, 3 10, 3 9.5, 2.4 9.5, 3 9, 2.7 9, 2.2 9.4, 2.2 9, 2 9),(2.2 9.8, 2.8 9.8, 2.8 9.7, 2.2 9.7, 2.2 9.8))' ), 
     
            ( 'I', 
     
             'POLYGON((3.2 9, 3.2 9.2, 3.4 9.2, 3.4 9.8, 3.2 9.8, 3.2 10, 3.8 10, 3.8 9.8, 3.6 9.8, 3.6 9.2, 3.8 9.2, 3.8 9, 3.2 9))' ), 
     
            ( 'S', 
     
             'POLYGON((4 9, 4 9.2, 4.8 9.2, 4.8 9.4, 4 9.4, 4 10, 5 10, 5 9.8, 4.2 9.8, 4.2 9.6, 5 9.6, 5 9, 4 9))' ), 
     
           ( 'T', 
     
             'POLYGON((5 9.8, 5 10, 6 10, 6 9.8, 5.6 9.8, 5.6 9, 5.4 9, 5.4 9.8, 5 9.8))' ), 
     
            ( 'M', 
     
             'POLYGON((6 9, 6 10, 6.25 10, 6.5 9.5, 6.75 10, 7 10, 7 9, 6.75 9, 6.75 9.7, 6.5 9.2, 6.25 9.7, 6.25 9, 6 9))' ), 
    	 
            ( 'A', 
     
             'POLYGON((7 9, 7 10, 8 10, 8 9, 7.75 9, 7.75 9.3, 7.25 9.3, 7.25 9, 7 9),(7.25 9.5, 7.25 9.8, 7.75 9.8, 7.75 9.5, 7.25 9.5))' ), 
     
            ( 'S', 
     
             'POLYGON((8 9, 8 9.2, 8.8 9.2, 8.8 9.4, 8 9.4, 8 10, 9 10, 9 9.8, 8.2 9.8, 8.2 9.6, 9 9.6, 9 9, 8 9))' ) ; 
     
    --Admire the scene 
     
    SELECT  * 
     
    FROM    #ChristmasScene  -- Tidy up the pine needles and put away the decorations 
     
    DROP TABLE #ChristmasScene

    How many days until Christmas

    DECLARE @DateToCheck DATETIME; 
     SET @DateToCheck = GETDATE();
    
     SELECT CASE WHEN DATEDIFF(dd, @DateToCheck,
                               DATEADD(dd, 24,
                                       DATEADD(mm, 11,
                                               DATEADD(yy,
                                                       DATEDIFF(yy, 0,
                                                                @DateToCheck), 0)))) < 0
                 THEN DATEDIFF(dd, @DateToCheck,
                               DATEADD(dd, 24,
                                       DATEADD(mm, 11,
                                               DATEADD(yy,
                                                       DATEDIFF(yy, 0,
                                                                @DateToCheck) + 1,
                                                       0))))
                 ELSE DATEDIFF(dd, @DateToCheck,
                               DATEADD(dd, 24,
                                       DATEADD(mm, 11,
                                               DATEADD(yy,
                                                       DATEDIFF(yy, 0,
                                                                @DateToCheck), 0))))
            END AS DaysTilComingChristmas;



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 22, 2017 9:53 PM

All replies

  • Way to go Karen. Wish I knew more about what it is.  :)

    In the mean time some vb code will have to do? 

    Merry Christmas Everyone.

    Imports System.Drawing.Drawing2D
    
    Public Class Form9
        Private WithEvents timer1 As New Timer With {.Enabled = True, .Interval = 100}
        Private flames As New List(Of GraphicsPath)
        Private Flame As Integer
        Private Rand As New Random
    
        Private Sub Form9_Load(sender As Object, e As EventArgs) Handles Me.Load
            DoubleBuffered = True
    
            'make 3 flame paths straight, bent, more bent
            'add each to list of flames
            Dim pth1 As New GraphicsPath
            Dim rect1 As New RectangleF(-1, -1, 2, 2)
            pth1.AddArc(rect1, -20, 221)
            pth1.AddLine(-0.94, -0.35, 0, -2.85)
            pth1.AddLine(0, -2.85, 0.94, -0.35)
            flames.Add(pth1)
    
            Dim pth2 As New GraphicsPath
            rect1 = New RectangleF(-1, -1, 2, 2)
            pth2.AddArc(rect1, -5, 215)
            rect1 = New RectangleF(-6.13, -3.37, 7.14, 7.14)
            pth2.AddArc(rect1, -49, 45)
            pth2.StartFigure()
            rect1 = New RectangleF(-10.09, -7.96, 9.91, 9.91)
            pth2.AddArc(rect1, 6, 25)
            flames.Add(pth2)
    
            Dim pth3 As New GraphicsPath
            rect1 = New RectangleF(-1, -1, 2, 2)
            pth3.AddArc(rect1, -6.2, 221)
            rect1 = New RectangleF(-4.09, -2.38, 5.1, 5.1)
            pth3.AddArc(rect1, -65, 58.8)
            pth3.StartFigure()
            rect1 = New RectangleF(-4.52, -3.81, 4.09, 4.09)
            pth3.AddArc(rect1, -10.6, 47)
            flames.Add(pth3)
        End Sub
    
        Private Sub Form9_Paint(sender As Object, e As PaintEventArgs) Handles Me.Paint
            With e.Graphics
                .Clear(Color.Black)
    
                'create and position the scene rectangle frame based on form size
                Dim r As Integer
                If ClientRectangle.Width > ClientRectangle.Height Then
                    r = CInt(ClientRectangle.Height / 2)
                Else
                    r = CInt(ClientRectangle.Width / 2)
                End If
                Dim rectf As New RectangleF(CSng(0.7 * r), CSng(0.4 * r), r, CSng(1.2 * r))
    
                'draw the candle scene within the frame
                RenderScene(e.Graphics, rectf)
    
                'draw frame
                .ResetTransform()
                Dim border As Integer = CInt(r / 10)
                rectf.Inflate(CSng(border / 2), CSng(border / 2))
                .DrawRectangle(New Pen(Color.Sienna, border), Rectangle.Round(rectf))
    
            End With
        End Sub
    
        Private Sub Form9_Resize(sender As Object, e As EventArgs) Handles Me.Resize
            Invalidate()
        End Sub
    
        Private Sub timer1_Tick(sender As Object, e As EventArgs) Handles timer1.Tick
            'set random to make candle flicker between 3 states
            Flame = Rand.Next(0, 100)
            Invalidate()
        End Sub
    
        Private Sub RenderScene(g As Graphics, rectf As RectangleF)
            'draw table and candle within the frame rectf
            Dim r As Single = CSng(rectf.Height / 2)
    
            With g
                .SmoothingMode = Drawing2D.SmoothingMode.AntiAlias
    
                Using pthTable As New GraphicsPath,
                        pthClip As New GraphicsPath
    
                    'clip scene within frame
                    pthClip.AddRectangle(rectf)
                    Dim pthRg As New [Region](pthClip)
                    .SetClip(pthRg, CombineMode.Replace)
    
                    'background color
                    .Clear(Color.FromArgb(150, Color.DarkSlateGray))
    
                    'draw table
                    Dim pts As PointF() = {New PointF(-r + rectf.X, rectf.Y + 3 * r),
                                New PointF(CSng(rectf.X + 0.5 * r), rectf.Y + CSng(0.9 * r)),
                                New PointF(rectf.X + 3 * r, rectf.Y + CSng(r)),
                                New PointF(rectf.X + 3 * r, rectf.Y + 3 * r)}
                    pthTable.AddLines(pts)
                    .FillPath(Brushes.Maroon, pthTable)
    
                    'signature
                    .DrawString("tommytwotrain", New Font("tahoma", CSng(r / 20)), Brushes.AntiqueWhite, CSng(rectf.X + r / 16), CSng(rectf.Y + rectf.Height - r / 7))
    
                    'add candle 
                    DrawCandle(g, New RectangleF(rectf.X + CSng(0.8 * r), rectf.Y + CSng(0.3 * r), r, r), 12)
    
                    .ResetClip()
                End Using
            End With
    
        End Sub
    
        Private Sub DrawCandle(g As Graphics, rectf As RectangleF, scale As Single)
            'draws candle within rectf, adjust candle size within rectf with scale
            Dim r As Double = rectf.Width / rectf.Height
            Dim dy As Single = CSng(0.25 * rectf.Height)
            scale *= CSng(r)   'fit to smallest form size
    
            With g
                .ResetTransform()
                .SmoothingMode = SmoothingMode.AntiAlias
                .TranslateTransform(CSng(rectf.X + rectf.Width / 2), rectf.Y + dy)
                .ScaleTransform(rectf.Width / scale, CSng(1.5 * rectf.Width / scale))
    
                'base
                DrawCylinder(g, New PointF(-4, 7.5), 0.3, 4, Color.DarkGoldenrod)
    
                'candle
                DrawCylinder(g, New PointF(-1.5, 1.5), 6, 1.5, Color.DarkGreen)
    
                'wick
                Using p As New Pen(Color.Black, CSng(r / 4))
                    p.StartCap = LineCap.Round
                    .DrawLine(p, 0, 1.3, 0, 0.5)
                End Using
    
                'background shadow
                Dim t As Integer = CInt(Flame / 150)
                Dim r1 As Integer = CInt(0.8 * scale + t)
                Dim colors As Color() = {Color.FromArgb(15, Color.Black)}
                Using pthShadow As New GraphicsPath()
                    pthShadow.AddEllipse(-r1, 0, 2 * r1, CSng(1.5 * r1))
                    Using pthGlowBr As New PathGradientBrush(pthShadow)
                        pthGlowBr.CenterColor = Color.FromArgb(5, Color.Gray)
                        pthGlowBr.SurroundColors = colors
                        .FillPath(pthGlowBr, pthShadow)
                    End Using
                End Using
    
                'background glow
                colors = {Color.FromArgb(CInt(t), Color.Black)}
                r1 = CInt(1 * scale + t)
                Using pthGlow As New GraphicsPath()
                    pthGlow.AddEllipse(-r1, -r1, 2 * r1, 2 * r1)
                    Using pthGlowBr As New PathGradientBrush(pthGlow)
                        pthGlowBr.CenterColor = Color.FromArgb(100 + t, Color.AliceBlue)
                        pthGlowBr.SurroundColors = colors
                        .FillPath(pthGlowBr, pthGlow)
                    End Using
                End Using
    
                'flame
                Using pthFlame As New GraphicsPath()
                    pthFlame.AddEllipse(-1.5, -4, 3, 8)
                    colors = {Color.FromArgb(150, Color.Orange)}
                    Using pthFlameBr As New PathGradientBrush(pthFlame)
                        pthFlameBr.SurroundColors = colors
                        Select Case Flame 'choose flame shape path based on random number set in timer tick
                            Case > 85 'dont show this shape as much
                                pthFlameBr.CenterColor = Color.FromArgb(200, Color.PaleGoldenrod)
                                .FillPath(pthFlameBr, flames(1))
                            Case > 40
                                pthFlameBr.CenterColor = Color.FromArgb(230, Color.PaleGoldenrod)
                                .FillPath(pthFlameBr, flames(2))
                            Case Else 'show this state the most
                                pthFlameBr.CenterColor = Color.FromArgb(250, Color.LightGoldenrodYellow)
                                .FillPath(pthFlameBr, flames(0))
                                .DrawPath(New Pen(Color.FromArgb(40, Color.LightGoldenrodYellow), CSng(r / 40)), flames(0))
                        End Select
                    End Using
                End Using
    
                'flame highlight
                colors = {Color.FromArgb(55, Color.AntiqueWhite)}
                Using pthHighlight As New GraphicsPath()
                    pthHighlight.AddEllipse(-0.5, 0, 1, CSng(1.2 - (Flame / 500)))
                    Using pthHighlightBr As New PathGradientBrush(pthHighlight)
                        pthHighlightBr.CenterColor = Color.FromArgb(CInt(150 + Flame / 3), 20, 20, 20)
                        pthHighlightBr.SurroundColors = colors
                        .FillPath(pthHighlightBr, pthHighlight)
                    End Using
                End Using
    
                'text on candle
                .ResetTransform()
                .TranslateTransform(rectf.X + CSng(rectf.Width / 2), rectf.Y + CSng(rectf.Height / 2))
                .RotateTransform(-90)
                .DrawString("Peace", New Font("Blackadder ITC", CSng(0.7 * dy)), New SolidBrush(Color.FromArgb(200, Color.Gold)), CSng(-2.5 * dy), CSng(-0.45 * dy))
            End With
        End Sub
    
        Private Sub DrawCylinder(g As Graphics, xy1 As PointF, h As Single, r As Single, clr As Color)
            'draws vertical cylinder with xy1 = location, h = height, r = radius, clr = color
            Dim rectf As RectangleF = New RectangleF(xy1.X, CSng((xy1.Y - (r / 1.9) + h)), 2 * r, r)
            Using path1 As New GraphicsPath()
                path1.AddArc(rectf, 180, -180)
                Using lgbr As New LinearGradientBrush(rectf, Color.FromArgb(255, Color.Black), clr, 180)
    
                    'bottom arc
                    g.FillPath(lgbr, path1)
    
                    'cylinder body
                    path1.Reset()
                    path1.AddRectangle(New RectangleF(xy1.X, xy1.Y, 2 * r, h))
                    g.FillPath(lgbr, path1)
                End Using
    
                'top of cylinder
                path1.Reset()
                rectf.Offset(0, -h)
                path1.AddEllipse(rectf)
                Using pgbr As New Drawing2D.PathGradientBrush(path1)
                    pgbr.CenterPoint = New PointF(xy1.X + r, r)
                    pgbr.CenterColor = Color.FromArgb(255, 25, 25, 25)
                    Dim colors As Color() = {Color.FromArgb(255, clr)}
                    pgbr.SurroundColors = colors
                    g.FillPath(pgbr, path1)
                End Using
            End Using
        End Sub
    End Class

    Sunday, December 24, 2017 8:20 PM
  • Very nice :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, December 24, 2017 8:38 PM