Saturday, May 31, 2014

COALESCE sql server

alter proc SP_DTV_SecBal
@division varchar(2), @ownerContact varchar(30)
AS
if @division='' begin set @division=null   end
if @ownerContact='' begin set @ownerContact=null   end



delete from Tbl_DTVSecBal
insert into Tbl_DTVSecBal
SELECT  eDAST, CAST(eDAN81 AS INT)eDAN81, LEFT(S.ABALPH, 25),
    ltrim(rtrim(T.WWGNNM)) + ' ' + ltrim(rtrim(T.WWSRNM)),
 '%' ,  CONVERT(MONEY, EDUNCS / 10000),
tg1.GregDay, tg2.GregDay,
    CAST(AICMC2 AS INT), LEFT(P.ABALPH, 25),
    ltrim(rtrim(U.WWGNNM)) + ' ' +  CASE when ltrim(rtrim(U.WWSRNM)) LIKE '%''%' THEN Replace(ltrim(rtrim(U.WWSRNM)), '''', '`') ELSE ltrim(rtrim(U.WWSRNM)) END
,p.abac19 Division,EDGLC,S.ABAT1,EDDCTO
FROM JDE_PRODUCTION.PRODDTA.F5542845 INNER JOIN JDE_PRODUCTION.PRODDTA.F0111 T
ON  EDAN81 = T.WWAN8  INNER JOIN vAB03012
ON  EDAN81 = AIAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0111 U
ON  AICMC2 = U.WWAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0101 S
ON  EDAN81 = S.ABAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0101 P
ON  AICMC2 = P.ABAN8 INNER JOIN ZDEV.dbo.tblJulian_Gregorian tg1
ON  EDEFTB = tg1.JulianDay INNER JOIN ZDEV.dbo.tblJulian_Gregorian tg2
ON  EDEFTE=tg2.JulianDay --LEFT JOIN JDE_PRODUCTION.PRODDTA.F4094
WHERE EDAST IN ('DC1','DC2','DC3','DC4','DCNC','DCVP' )
AND (ltrim(rtrim(T.WWGNNM)) + ltrim(rtrim(T.WWSRNM)) != '')
AND (ltrim(rtrim(U.WWGNNM)) + ltrim(rtrim(U.WWSRNM)) != '')

--select * from Tbl_DTVSecBal
select distinct
AllOwnerID = z.AllOwnerID ,z.AlphaName,z.OwnerContact,z.division,
z.Adj,
[Ship To] = z.[Ship To] ,
z.SoldToCompany,
z.SalesPerson,
isnull(z.RateMode, '') RM,
cast(z.CommRate as decimal (15,2)) CommRate ,            
CONVERT(VARCHAR(10),z.Start,101)StartDate ,
CONVERT(VARCHAR(10),z.[End],101)EndDate ,Division,GLC,AT1,OrTY,
cast(v.SecBal as decimal (15,2)) SecBal
FROM Tbl_DTVSecBal z INNER JOIN  dbo.vSBL_SEC v ON z.[Ship To] = v.GBSBL
where z.AllOwnerID<>0
and z.division = COALESCE(@division, division) and  z.OwnerContact= = COALESCE(@OwnerContact,OwnerContact)


GO

Tuesday, May 20, 2014

Tic Tac Toe Game in VB Complete Code....


put button names like Button1 Button2........
and two labels Label1 & Label2



Public Class TicTacToeForm
    Dim random As String = 1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub
 

    Public Sub PC()
        If random = 1 And Button1.Enabled = True Then
            Button1.Text = "O"
            Button1.Enabled = False
        End If
        If random = 2 And Button2.Enabled = True Then
            Button2.Text = "O"
            Button2.Enabled = False
        End If
        If random = 3 And Button3.Enabled = True Then
            Button3.Text = "O"
            Button3.Enabled = False
        End If
        If random = 4 And Button4.Enabled = True Then
            Button4.Text = "O"
            Button4.Enabled = False
        End If
        If random = 5 And Button5.Enabled = True Then
            Button5.Text = "O"
            Button5.Enabled = False
        End If
        If random = 6 And Button6.Enabled = True Then
            Button6.Text = "O"
            Button6.Enabled = False
        End If
        If random = 7 And Button7.Enabled = True Then
            Button7.Text = "O"
            Button7.Enabled = False
        End If
        If random = 8 And Button8.Enabled = True Then
            Button8.Text = "O"
            Button8.Enabled = False
        End If
        If random = 9 And Button9.Enabled = True Then
            Button9.Text = "O"
            Button9.Enabled = False
        End If
    End Sub
    Public Sub Options()
        'If user hits 1
        If Label1.Text = 1 Then
            If Button2.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" Or Button6.Text = "X" Then
                hit()
            End If

        End If
        'If user hits 2
        If Label1.Text = 2 Then
            If Button1.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button4.Text = "X" Or Button7.Text = "X" Or Button9.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 3
        If Label1.Text = 3 Then
            If Button1.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 4
        If Label1.Text = 4 Then
            If Button1.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" Or Button3.Text = "X" Or Button8.Text = "X" Or Button9.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 5
        If Label1.Text = 5 Then
            If Button1.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            End If
        End If

        'If user hits 6
        If Label1.Text = 6 Then
            If Button3.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" Or Button2.Text = "X" Or Button7.Text = "X" Or Button8.Text = "X" Then
                hit()
            End If
        End If

        'If user hits 7
        If Label1.Text = 7 Then
            If Button1.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button2.Text = "X" Then
                hit()
            End If
        End If

        'If user hits 8
        If Label1.Text = 8 Then
            If Button2.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button3.Text = "X" Or Button1.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 9
        If Label1.Text = 9 Then
            If Button6.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
    End Sub

    Public Sub CheckIfComputerWins()
        If Button1.Text = "O" And Button2.Text = "O" And Button3.Text = "O" _
        Or Button4.Text = "O" And Button5.Text = "O" And Button6.Text = "O" _
        Or Button7.Text = "O" And Button8.Text = "O" And Button9.Text = "O" _
        Or Button1.Text = "O" And Button4.Text = "O" And Button7.Text = "O" _
        Or Button2.Text = "O" And Button5.Text = "O" And Button8.Text = "O" _
        Or Button3.Text = "O" And Button6.Text = "O" And Button9.Text = "O" _
        Or Button1.Text = "O" And Button5.Text = "O" And Button9.Text = "O" _
        Or Button7.Text = "O" And Button5.Text = "O" And Button3.Text = "O" Then
            MsgBox("Computer Wins")
            Button1.Text = ""
            Button1.Enabled = True
            Button2.Text = ""
            Button2.Enabled = True
            Button3.Text = ""
            Button3.Enabled = True
            Button4.Text = ""
            Button4.Enabled = True
            Button5.Text = ""
            Button5.Enabled = True
            Button6.Text = ""
            Button6.Enabled = True
            Button7.Text = ""
            Button7.Enabled = True
            Button8.Text = ""
            Button8.Enabled = True
            Button9.Text = ""
            Button9.Enabled = True
        ElseIf Button1.Text = "X" And Button2.Text = "X" And Button3.Text = "X" _
        Or Button4.Text = "X" And Button5.Text = "X" And Button6.Text = "X" _
        Or Button7.Text = "X" And Button8.Text = "X" And Button9.Text = "X" _
        Or Button1.Text = "X" And Button4.Text = "X" And Button7.Text = "X" _
        Or Button2.Text = "X" And Button5.Text = "X" And Button8.Text = "X" _
        Or Button3.Text = "X" And Button6.Text = "X" And Button9.Text = "X" _
        Or Button1.Text = "X" And Button5.Text = "X" And Button9.Text = "X" _
        Or Button7.Text = "X" And Button5.Text = "X" And Button3.Text = "X" Then
            MsgBox("Congratulations, You Win")
            Button1.Text = ""
            Button1.Enabled = True
            Button2.Text = ""
            Button2.Enabled = True
            Button3.Text = ""
            Button3.Enabled = True
            Button4.Text = ""
            Button4.Enabled = True
            Button5.Text = ""
            Button5.Enabled = True
            Button6.Text = ""
            Button6.Enabled = True
            Button7.Text = ""
            Button7.Enabled = True
            Button8.Text = ""
            Button8.Enabled = True
            Button9.Text = ""
            Button9.Enabled = True
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Public Function hit()
        For Each ctl As Control In Me.Controls
            If Label1.Text < 9 Then
                If (ctl.Name.StartsWith("Button" & Label1.Text + 1)) Then
                    Dim btn As Button = DirectCast(ctl, Button)
                    If btn.Enabled = True Then
                        btn.Text = "O"
                        btn.Enabled = False
                    Else
                        Label1.Text = Label1.Text + 1
                    End If
                End If
            Else
                If (ctl.Name.StartsWith("Button1")) Then
                    Dim btn As Button = DirectCast(ctl, Button)
                    If btn.Enabled = True Then
                        btn.Text = "O"
                        btn.Enabled = False
                    Else
                        Label1.Text = Label1.Text + 1
                    End If
                End If
            End If
        Next
        Return Label1.Text

    End Function

    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click

        Timer1.Stop()

        Button1.Text = "O"

        Label1.Text = 1

        Button1.Enabled = False

        Button10.Enabled = False

    End Sub


    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        Button1.Text = ""
        Button1.Enabled = True
        Button2.Text = ""
        Button2.Enabled = True
        Button3.Text = ""
        Button3.Enabled = True
        Button4.Text = ""
        Button4.Enabled = True
        Button5.Text = ""
        Button5.Enabled = True
        Button6.Text = ""
        Button6.Enabled = True
        Button7.Text = ""
        Button7.Enabled = True
        Button8.Text = ""
        Button8.Enabled = True
        Button9.Text = ""
        Button9.Enabled = True
        Button10.Enabled = True
        Timer1.Start()
    End Sub



    Private Sub Timer1_Tick_1(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        random += 1

        If random = 10 Then

            random = 1

        End If
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button4_Click_1(sender As System.Object, e As System.EventArgs) Handles Button4.Click

        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub butncode()


    End Sub

End Class

Inspired by: visual-basic-tutorials.com/2162010132827.php


Sunday, May 11, 2014

VB auto Scrollbar move with timer

Public Class Form1
    Shared _timer As Timer
    Shared _list As List(Of String) = New List(Of String)

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
       
        '   HScrollBar1.Value = 50 + Label2.Text




    End Sub

    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        Label1.Text = TimeOfDay()
        Label2.Text = Now.Second

        HScrollBar1.Value = Label2.Text
       
    End Sub


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Timer1.Stop()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Timer1.Start()
    End Sub

   
End Class

Thursday, May 1, 2014

Rolling 12 Months Table header YYYY-MM format

declare @mnth varchar(7),@date as datetime,@vardate as varchar(10), @dateper_01 varchar(7),@period_01 varchar(7),@period_02 varchar(7),@period_03 varchar(7),@period_04 varchar(7)
declare @period_05 varchar(7),@period_06 varchar(7),@period_07 varchar(7),@period_08 varchar(7),@period_09 varchar(7),@period_10 varchar(7),@period_11 varchar(7),@period_12 varchar(7)
declare @Query varchar(4000)

set @mnth='2014-04'
set @vardate=@mnth+'-01'
--select  @vardate
set @date=cast(@vardate as datetime)
set  @period_01=right(convert(varchar,DATEADD(MM,-0,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-0,@date),101),2)
set  @period_02=right(convert(varchar,DATEADD(MM,-1,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-1,@date),101),2)
set  @period_03=right(convert(varchar,DATEADD(MM,-2,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-2,@date),101),2)
set  @period_04=right(convert(varchar,DATEADD(MM,-3,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-3,@date),101),2)
set  @period_05=right(convert(varchar,DATEADD(MM,-4,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-4,@date),101),2)
set  @period_06=right(convert(varchar,DATEADD(MM,-5,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-5,@date),101),2)
set  @period_07=right(convert(varchar,DATEADD(MM,-6,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-6,@date),101),2)
set  @period_08=right(convert(varchar,DATEADD(MM,-7,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-7,@date),101),2)
set  @period_09=right(convert(varchar,DATEADD(MM,-8,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-8,@date),101),2)
set  @period_10=right(convert(varchar,DATEADD(MM,-9,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-9,@date),101),2)
set  @period_11=right(convert(varchar,DATEADD(MM,-10,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-10,@date),101),2)
set  @period_12=right(convert(varchar,DATEADD(MM,-11,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-11,@date),101),2)

drop table ##temp

SET @Query =
'CREATE TABLE [dbo].[##temp](
                [bu] [nvarchar](12) NOT NULL,
                [objsub] [nvarchar](20) NOT NULL,
                [obj_desc] [nchar](130) NULL,'
                +'['+@Period_12+'] [float] NULL,'
                +'['+@Period_11+'] [float] NULL,'
                +'['+@Period_10+'] [float] NULL,'
                +'['+@Period_09+'] [float] NULL,'
                +'['+@Period_08+'] [float] NULL,'
                +'['+@Period_07+'] [float] NULL,'
                +'['+@Period_06+'] [float] NULL,'
                +'['+@Period_05+'] [float] NULL,'
                +'['+@Period_04+'] [float] NULL,'
                +'['+@Period_03+'] [float] NULL,'
                +'['+@Period_02+'] [float] NULL,'
                +'['+@Period_01+'] [float] NULL,
) '

EXECUTE (@query)

-- drop table ##temp
select * from ##temp