Feeds:
Posts
Comments

Posts Tagged ‘TSQL’


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 





Read Full Post »


/*
    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom
*/

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
(
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
)
AS 
BEGIN
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
     
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
	
 --  ------------------------ 
 
    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
         
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    end
     
    --select * from @DailyDates -- test ` 
    return 
END


------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year


-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)
						  

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))


---- e.g. ------
/*
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 
    --------------------------------------------
*/
------------ 


Read Full Post »

I have come across this VB code from the below link and thought it was brilliant:

link to the original forum where the module (modFormatSQL.bas) was found


Attribute VB_Name = "modFormatSQL"
Option Explicit
'
'*  SQL "Select" statement formatter/checker
'
'*  Author:  Si_the_geek of VBForums.com
'
'
'* Example usage:
'
'Dim sSQL as String
'  sSQL = "SELECT field1 FROM table1"
'  sSQL = FormatSelect(sSQL)
'  MsgBox sSQL

Public Function FormatSelect(ByVal sInput As String, _
                             Optional ByVal bShowSpacerLines As Boolean = True, _
                             Optional ByVal lIndentSize As Integer = 10, _
                             Optional ByVal lIndentSubquerySize As Integer = 13) _
                             As String
'Format an SQL "select" statement for easier reading

'Also lists a few obvious errors if found, eg:
'  - Sections (eg: FROM, WHERE) in the wrong order;
'  - Wrong number of quotes, ie: a string/date is not closed properly;
'  - Wrong number of brackets, ie: more ( than ) , or vice versa.

'Current known omissions:
'  does not start a new line for CASE WHEN (or other DBMS specific code)

Dim sError As String

  FormatSelect = FormatSelect_Worker(sInput, _
                                     sError, _
                                     bShowSpacerLines, _
                                     lIndentSize, _
                                     lIndentSubquerySize)

  If sError &lt;&gt; "" Then
    MsgBox "The following problems were found:" &amp; vbCrLf &amp; vbCrLf &amp; sError, vbExclamation
  End If

End Function

'* the following are helper functions for FormatSelect *

Private Function FormatSelect_Worker(ByVal sInput As String, _
                                     ByRef ret_sWarnings As String, _
                                     Optional ByVal bSpacerLines As Boolean = True, _
                                     Optional ByVal lIndentSize As Integer = 10, _
                                     Optional ByVal lIndentSubquerySize As Integer = 13) _
                                     As String
'(helper function for FormatSelect)

'Format an SQL "select" statement for easier reading (also lists a few obvious errors)

  'Create keyword list array
Dim aKeywords As Variant
  aKeywords = Array("SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY")

  'Setup variables
Dim sRetString As String        'Text to return
Dim sWarning As String          'Warning messages
Dim sTempWarning As String      'Potential warning messages froom sub queries
Dim sText As String             '"Clean" text to work with
Dim sIndent As String           'Space to indent 'normal' text by
Dim sIndentSub As String        'Space to indent subqueries by
Dim sTemp As String             'Temporary text
Dim aStrings() As String        'Strings within the SQL (temporarily removed, in case keywords found)
Dim bGotStrings As Boolean      'Have we got any strings?
Dim aSubQueries() As String     'Sub-queries within the SQL (temp. removed to aid formatting &amp; simplify code)
Dim bGotSubqueries As Boolean   'Have we got any sub-queries?
Dim lCurrPos As Long            'The current position within sText
Dim lTempPos As Long            'Position of text we just searched for
Dim lTempPos2 As Long           'Position of text we just searched for
Dim lThisSectionType As Long    'The type of section (from aKeywords) we are working with
Dim lLastSectionType As Long    'The type of the previous section (to show warnings)
Dim lThisSectionStart As Long   'The start position of this section
Dim lNextSectionStart As Long   'The start position of the next section (so we stop before it)
Dim lCount As Long              'Loop counter
Dim bInQuotes As Boolean        'Are we currently inside single quotes? (for removing strings)
  sWarning = ""
  sRetString = ""
  sText = sInput
  sIndent = Space(lIndentSize)
  sIndentSub = Space(lIndentSubquerySize)

    'move all strings to array (so we dont find keywords in them)
  lCount = (Len(sText) - Len(Replace(Replace(Replace(sText, "'", ""), """", ""), "#", "")))
  ReDim aStrings(1, lCount \ 2 + 1) As String
  lCount = 0
  lCurrPos = 0
  bInQuotes = False
  lTempPos = InStr(1, sText, "'")
  lTempPos2 = InStr(1, sText, """")
  If (lTempPos = 0) Or (lTempPos2 &gt; 0 And lTempPos2 &lt; lTempPos) Then lTempPos = lTempPos2   lTempPos2 = InStr(1, sText, "#")   If (lTempPos = 0) Or (lTempPos2 &gt; 0 And lTempPos2 &lt; lTempPos) Then lTempPos = lTempPos2   Do While (lTempPos &gt; 0)
    sTemp = Mid$(sText, lTempPos, 1)
    aStrings(0, lCount) = sTemp
    If bInQuotes And (sTemp &lt;&gt; "#") Then
      Do While Mid$(sText, lTempPos, 2) = String(2, sTemp)
        lTempPos = InStr(lTempPos + 2, sText, sTemp)
        If lTempPos = 0 Then Exit Do
      Loop
    End If
    If lTempPos = 0 Then Exit Do
    If bInQuotes Then
      aStrings(1, lCount) = Mid$(sText, lCurrPos, lTempPos - lCurrPos)
      sText = Left$(sText, lCurrPos - 1) &amp; Mid$(sText, lTempPos)
      lCount = lCount + 1
      lTempPos = InStr(lCurrPos + 2, sText, "'")
      lTempPos2 = InStr(lCurrPos + 2, sText, """")
      If (lTempPos = 0) Or (lTempPos2 &gt; 0 And lTempPos2 &lt; lTempPos) Then lTempPos = lTempPos2       lTempPos2 = InStr(lCurrPos + 2, sText, "#")       If (lTempPos = 0) Or (lTempPos2 &gt; 0 And lTempPos2 &lt; lTempPos) Then lTempPos = lTempPos2
    Else
      lCurrPos = lTempPos + 1
      lTempPos = InStr(lTempPos + 1, sText, sTemp)
    End If
    bInQuotes = Not (bInQuotes)
  Loop
  If bInQuotes Then
    sWarning = sWarning _
             &amp; "Incorrect number of quote (" &amp; sTemp &amp; " characters), a string/date is not closed!" _
             &amp; vbCrLf &amp; vbCrLf
  End If
  If lCount = 0 Then
    Erase aStrings
  Else
    ReDim Preserve aStrings(1, lCount - 1) As String
    bGotStrings = True
  End If

    'tidy input text
  sText = Trim$(Replace(Replace(sText, vbCr, " "), vbLf, " "))
  sText = Replace(sText, vbTab, " ")
  sTemp = "=&lt;&gt;+-/*"
  For lCount = 1 To Len(sTemp)
    sText = Replace(sText, Mid$(sTemp, lCount, 1), " " &amp; Mid$(sTemp, lCount, 1) &amp; " ")
  Next lCount
  sText = Replace(sText, ",", ", ")
  sText = Replace(Replace(sText, "(", " ( "), ")", " ) ")
  Do While InStr(sText, "  ")
    sText = Replace(sText, "  ", " ")
  Loop
  sText = Trim$(Replace(sText, " ,", ","))

    'move all subqueries to array (better formatting, and so we dont split on the wrong keywords)
  ReDim aSubQueries((Len(sText) - Len(Replace(sText, "( SELECT ", "", compare:=vbTextCompare))) \ 8 + 1) As String
  lCount = 0
  lCurrPos = InStr(1, sText, "( SELECT ", vbTextCompare)
  Do While (lCurrPos &gt; 0)
    lTempPos = FindCloseBracket(sText, lCurrPos)
    sTemp = FormatSelect_Worker(Mid$(sText, lCurrPos + 1, lTempPos - lCurrPos - 1), _
                                sTempWarning, _
                                bSpacerLines, _
                                lIndentSize, _
                                lIndentSubquerySize)
    If sTempWarning &lt;&gt; "" Then
      sWarning = sWarning _
               &amp; "(in sub-query: " _
               &amp; vbCrLf _
               &amp; "  " &amp; Replace(sTempWarning, vbCrLf, vbCrLf &amp; "  ") _
               &amp; ")" _
               &amp; vbCrLf &amp; vbCrLf
    End If
    sTemp = vbCrLf _
          &amp; sIndentSub _
          &amp; Replace(sTemp, vbCrLf, vbCrLf &amp; sIndentSub)
    aSubQueries(lCount) = sTemp
    sText = Left$(sText, lCurrPos) _
          &amp; vbCrLf _
          &amp; sIndentSub _
          &amp; Mid$(sText, lTempPos)
    lCount = lCount + 1
    lCurrPos = InStr(lCurrPos + 1, sText, "( SELECT ", vbTextCompare)
  Loop
  If lCount = 0 Then
    Erase aSubQueries
  Else
    ReDim Preserve aSubQueries(lCount - 1) As String
    bGotSubqueries = True
  End If

    'count brackets to ensure a match
  lCount = (Len(sText) - Len(Replace(sText, "(", "")))
  lCount = lCount - (Len(sText) - Len(Replace(sText, ")", "")))
  Select Case Sgn(lCount)
  Case 1
    sWarning = sWarning _
             &amp; "You have " &amp; lCount &amp; " more open brackets: ( than close brackets: )  This may make any subqueries be invalid!" _
             &amp; vbCrLf &amp; vbCrLf
  Case -1
    sWarning = sWarning _
             &amp; "You have " &amp; Abs(lCount) &amp; " more close brackets: ) than open brackets: (  This may make any subqueries be invalid!" _
             &amp; vbCrLf &amp; vbCrLf
  End Select

    'work with text...
  lCurrPos = 1
  lThisSectionType = -1
  Do While lCurrPos &lt; Len(sInput)       'find current section type &amp; position     lLastSectionType = lThisSectionType     lThisSectionStart = FindFirstByArray(sText, aKeywords, lCurrPos, lThisSectionType)       'if any "incorrect" text, add it to a new line     If lThisSectionStart &gt; lCurrPos Then
      sRetString = sRetString _
                 &amp; Mid$(sText, lCurrPos, lThisSectionStart - lCurrPos - 1) _
                 &amp; vbCrLf
      sWarning = sWarning _
               &amp; "Unexpected text: " _
               &amp; Mid$(sText, lCurrPos, lThisSectionStart - lCurrPos - 1) _
               &amp; vbCrLf &amp; vbCrLf
    End If
      'if reached end of text, exit!
    If lThisSectionStart &gt;= Len(sText) Then Exit Do
      'if sections ordered wrong, add to warning
    If lLastSectionType &gt; lThisSectionType Then
      sWarning = sWarning _
               &amp; "Incorrect section ordering: " _
               &amp; aKeywords(lThisSectionType) _
               &amp; " should be before " _
               &amp; aKeywords(lLastSectionType) _
               &amp; vbCrLf &amp; vbCrLf
    End If

    If bSpacerLines Then
        'add blank lines between sections
      If (lCurrPos &gt; 1) Then
        sRetString = sRetString _
                   &amp; vbCrLf
      End If
    End If

      'add keyword (Ucase), and spaces to indent
    If Len(aKeywords(lThisSectionType)) &gt;= lIndentSize Then
      sRetString = sRetString &amp; aKeywords(lThisSectionType) &amp; " "
    Else
      sRetString = sRetString &amp; aKeywords(lThisSectionType) &amp; Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
    End If
    lCurrPos = lCurrPos + Len(aKeywords(lThisSectionType)) + 1
    lThisSectionStart = lThisSectionStart + Len(aKeywords(lThisSectionType)) + 1
      'find end of current section
    lNextSectionStart = FindFirstByArray(sText, aKeywords, lCurrPos)

      'work on each section...
Dim lBracketStart As Long
Dim lBracketEnd As Long
Dim aTempArray As Variant
Dim lTempIndex As Long
Dim lTempLen As Long
Dim lTempInc As Long
    Select Case aKeywords(lThisSectionType)

    Case "SELECT", "GROUP BY", "ORDER BY"
      lTempPos = lCurrPos
      Do
        Do
          lBracketStart = InStr(lTempPos, sText, "(")
          lTempPos = InStr(lTempPos, sText, ",")
          If (lBracketStart &gt; 0) And (lBracketStart &lt; lTempPos) And (lBracketStart &lt; lNextSectionStart) Then
            lBracketEnd = FindCloseBracket(sText, lBracketStart)
            If lBracketEnd &lt; lNextSectionStart Then               lTempPos = lBracketEnd - 1             Else               lBracketStart = 0             End If           Else             lBracketStart = 0           End If         Loop While (lBracketStart &gt; 0) And (lTempPos &gt; 0)
        If (lTempPos = 0) Or (lTempPos &gt;= lNextSectionStart) Then lTempPos = lNextSectionStart - 1
        If (lTempPos &gt; lCurrPos) Then
          sRetString = sRetString _
                     &amp; Trim$(Mid$(sText, lCurrPos, lTempPos - lCurrPos + 1)) _
                     &amp; vbCrLf
          If (lTempPos &lt; lNextSectionStart - 1) Then
            sRetString = sRetString _
                       &amp; sIndent
          End If
        End If
        lCurrPos = lTempPos + 1
        lTempPos = lCurrPos
      Loop While (lTempPos &lt; lNextSectionStart) And (lCurrPos &lt; lNextSectionStart)

    Case "FROM"
      aTempArray = Array("INNER JOIN", "LEFT OUTER JOIN", "RIGHT OUTER JOIN", _
                         "LEFT JOIN", "RIGHT JOIN", "OUTER JOIN", "OUTER", _
                         "CROSS JOIN", "JOIN")
      Do
        lTempPos = FindFirstByArray(sText, aTempArray, lCurrPos, lTempIndex)
        If (lTempPos &lt; Len(sText)) And (lTempPos &lt; lNextSectionStart) Then
          lTempLen = Len(aTempArray(lTempIndex))
          sRetString = sRetString _
                     &amp; Trim$(Mid$(sText, lCurrPos, lTempPos - lCurrPos - 1)) _
                     &amp; vbCrLf _
                     &amp; sIndent _
                     &amp; aTempArray(lTempIndex) _
                     &amp; " "
          lCurrPos = lTempPos + lTempLen
        End If
      Loop While (lTempPos &lt; Len(sText)) And (lTempPos &lt; lNextSectionStart)
      If (lCurrPos &lt; lNextSectionStart) Then
        sRetString = sRetString _
                   &amp; Trim$(Mid$(sText, lCurrPos, lNextSectionStart - lCurrPos)) _
                   &amp; vbCrLf
      End If

    Case "WHERE", "HAVING"
      aTempArray = Array("AND", "OR", "BETWEEN")
      Do
        lTempInc = 0
        Do
          lTempPos = FindFirstByArray(sText, aTempArray, lCurrPos + lTempInc, lTempIndex)
            'need to skip the 'And' in a Between clause
          If (lTempIndex = 2) Then
            lTempInc = InStr(lTempPos, sText, "AND", vbTextCompare) + 1 - lCurrPos
            If lTempInc &lt; 0 Then lTempInc = 7
          End If
        Loop While (lTempIndex = 2)
        If (lTempPos &lt; Len(sText)) And (lTempPos &lt; lNextSectionStart) Then
          lTempLen = Len(aTempArray(lTempIndex))
          sRetString = sRetString _
                     &amp; Trim$(Mid$(sText, lCurrPos, lTempPos - lCurrPos)) _
                     &amp; vbCrLf _
                     &amp; sIndent _
                     &amp; aTempArray(lTempIndex) _
                     &amp; " "
          lCurrPos = lTempPos + lTempLen
        End If
      Loop While (lTempPos &lt; Len(sText)) And (lTempPos &lt; lNextSectionStart)
      If (lCurrPos &lt; lNextSectionStart) Then
        sRetString = sRetString _
                   &amp; Trim$(Mid$(sText, lCurrPos, lNextSectionStart - lCurrPos)) _
                   &amp; vbCrLf
      End If

    Case Else     'a section not coded for - just paste the text!
      sRetString = sRetString _
                 &amp; sIndent _
                 &amp; Trim$(Mid$(sText, lCurrPos, lNextSectionStart - lCurrPos)) _
                 &amp; vbCrLf
    End Select

    lCurrPos = lNextSectionStart
  Loop

    '(main statement finished)

    're-insert the sub-queries we removed at the start
  If bGotSubqueries Then
    If bSpacerLines Then
      For lCount = 0 To UBound(aSubQueries)
        sRetString = Replace(sRetString, _
                             "(" &amp; vbCrLf &amp; sIndentSub &amp; ")", _
                             "(" &amp; vbCrLf &amp; aSubQueries(lCount) &amp; vbCrLf &amp; vbCrLf &amp; sIndentSub &amp; ")", _
                             1, _
                             1)
      Next lCount
    Else
      For lCount = 0 To UBound(aSubQueries)
        sRetString = Replace(sRetString, _
                             "(" &amp; vbCrLf &amp; sIndentSub &amp; ")", _
                             "(" &amp; aSubQueries(lCount) &amp; vbCrLf &amp; sIndentSub &amp; ")", _
                             1, _
                             1)
      Next lCount
    End If
  End If

    're-insert the strings we removed at the start
  If bGotStrings Then
    For lCount = 0 To UBound(aStrings, 2)
      sRetString = Replace(sRetString, _
                           String(2, aStrings(0, lCount)), _
                           aStrings(0, lCount) &amp; aStrings(1, lCount) &amp; aStrings(0, lCount), _
                           1, _
                           1)
    Next lCount
  End If

    'return any warning messages
  If sWarning &lt;&gt; "" Then
    ret_sWarnings = sWarning
  End If

    'return formatted text
  FormatSelect_Worker = Left$(sRetString, Len(sRetString) - Len(vbCrLf))

End Function

Private Function FindCloseBracket(sText As String, _
                                  Optional lStartPos As Long = 1) _
                                  As Long
'(helper function for FormatSelect)

'Returns the position of the closing bracket to match the opening bracket at the position 'lStartPos',
'(or length of string+1 if not found)

Dim lCurrPos As Long
Dim lOpenPos As Long
Dim lClosePos As Long
Dim lOpenCount As Long

  lOpenCount = 1
  lCurrPos = lStartPos + 1
  FindCloseBracket = Len(sText) + 1

  lOpenPos = InStr(lCurrPos, sText, "(")
  lClosePos = InStr(lCurrPos, sText, ")")
  Do While (lOpenPos &gt; 0) Or (lClosePos &gt; 0)
    If (lOpenPos &gt; 0 And lOpenPos &lt; lClosePos) Or (lClosePos = 0) Then
      lOpenCount = lOpenCount + 1
      lCurrPos = lOpenPos + 1
      lOpenPos = InStr(lCurrPos, sText, "(")
    Else
      lOpenCount = lOpenCount - 1
      If lOpenCount = 0 Then
        FindCloseBracket = lClosePos
        Exit Do
      End If
      lCurrPos = lClosePos + 1
      lClosePos = InStr(lCurrPos, sText, ")")
    End If
  Loop

End Function

Private Function FindFirstByArray(sText As String, _
                                  vArray As Variant, _
                                  Optional lStartPos As Long = 1, _
                                  Optional ByRef retArrayIndex As Long) _
                                  As Long
'(helper function for FormatSelect)

'Returns the position of first instance in the string of any array item (or length of string+1 if none found)
'nb: must be after some kind of punctuation as listed below

Const csPunctuation = " '""#()=&lt;&gt;[]+-/*"

Dim lFirstPos As Long
Dim lArrayIndex As Long
Dim lTempPos As Long
Dim lCount As Long
Dim bOK As Boolean

  lFirstPos = Len(sText) + 1
  lArrayIndex = 0

  For lCount = 0 To UBound(vArray)
    lTempPos = InStr(lStartPos, sText, vArray(lCount), vbTextCompare)
    Do While (lTempPos &gt;= lStartPos)
      bOK = False    'check prev char is punctuation/start
      If (lTempPos = lStartPos) Then
        bOK = True
      Else
        If InStr(csPunctuation, Mid$(sText, lTempPos - 1, 1)) Then
          bOK = True
        End If
      End If
      If bOK Then    'check next char is punctuation/end
        If lTempPos + Len(vArray(lCount)) &gt; Len(sText) Then Exit Do
        If InStr(csPunctuation, Mid$(sText, lTempPos + Len(vArray(lCount)), 1)) Then Exit Do
      End If
      lTempPos = InStr(lTempPos + 1, sText, vArray(lCount), vbTextCompare)
    Loop
    If (lTempPos &gt; 0) And (lTempPos &lt; lFirstPos) Then
      lFirstPos = lTempPos
      lArrayIndex = lCount
    End If
  Next lCount

  retArrayIndex = IIf(lFirstPos = Len(sText), -1, lArrayIndex)
  FindFirstByArray = lFirstPos

End Function

Read Full Post »


-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null
) 


--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'


/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL

Read Full Post »

/*
 Cross Join: 
 Each row in the first table is paired with all the rows in the second table. 
*/
if OBJECT_ID('tempdb..#a') is not null 
	drop table #a 

if OBJECT_ID('tempdb..#b') is not null 
	drop table #b 

	
create table #a 
(
	acol varchar(10) 
)
insert into #a values 
('a1'),
('a2')

create table #b 
(
	bcol varchar(10) 
	
)
insert into #b values 
('b1'),
('b2')

select * from #a 
select * from #b 


select * from #a cross join #b 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
---------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mySP]]
GO

CREATE PROCEDURE [dbo].[mySP] @var AS VARCHAR(100)
as 
begin 
	...
end 


--For Function:
--------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[myUDF]
GO


create FUNCTION [dbo].[myUDF]
(
	@var AS VARCHAR(100)
)
RETURNS INT
AS 
BEGIN
	...
end 

Read Full Post »

Assume the below scenario:
—————————-

A system checks a stock level for a grocery store that sells 3 items and whenever a certain item runs out it flags up a message.

Say the 3 items are:
Mango
Orange
Apple

To make use of binary masking (also known as bitwise) to be able to track which needs to be highlighted, we need to do the following:

1) We will assign a binary number to each one of the item, 1 for denoting we need to flag a message and 0 f0r doing nothing. Each of the the items in turn will have a fixed position. So in this instance there are three position for three items and we can assign it as below:

Item Position Binary Value
—– ——— ————-
Mango 0 1
Orange 1 0
Apple 2 1

2) Now, we need a single decimal number that represents which items jave a binary 1 to flag a message and which don’t. To do that we use the Position value and treat that as the power of 2.
So, we use Position 1 and 3 (because those have the binary value 1 binded) and it should equate to:

(2^0) = 1
+ (2^2) = 4
————-
Total = 5
————-

3) The value 5 can then be sent to the section of the system which flags messages. It should then have a script to reverse it back to a binary number which is 101.

The position number is fixed and therefore can be easily be deduced as to which binary number is binded to whcih item.

This way by only sending an integer number we can for a group of item attached and interchagne flag data.

>Returns the binary representation of a number:
———————————————–

-- courtesy to a script somewhere in the internet
CREATE FUNCTION [csrt].[udf_ReturnBinaryPattern] 
(
	@Byte INT
) 

RETURNS CHAR(8) 
AS 
BEGIN 

    DECLARE  @Pattern CHAR(8) 
    SET @Pattern = '' 

    SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) + 
    convert(VARCHAR,(@Byte & 2) / 2) + 
    convert(VARCHAR,(@Byte & 4) / 4) + 
    convert(VARCHAR,(@Byte & 8) / 8) + 
    convert(VARCHAR,(@Byte & 16) / 16) + 
    convert(VARCHAR,(@Byte & 32) / 32) + 
    convert(VARCHAR,(@Byte & 64) / 64) + 
    convert(VARCHAR,(@Byte & 128) / 128) 

    RETURN (@Pattern) 

END 
GO

>Optional – Explaning & bitwise AND operator:
————————————-
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.

SELECT 170 & 75

Result is 10 (thisis a decimal number)

The & operator tranforms number or charters into binary and then adds them up:

170 = 0000 0000 1010 1010
75= 0000 0000 0100 1011
10 = 0000 0000 0000 1010

Another artice: http://sqlfool.com/2009/02/bitwise-operations/

Read Full Post »

Older Posts »