Get Every 3rd Monday Date T-SQL Query

Here is a simple T-SQL script which does the job.

    SELECT Every3rdMondayDate = Date
    FROM (    SELECT date                                                 
            ,      ROW_NUMBER() OVER(PARTITION BY ym ORDER BY date DESC) AS RowNumber
            FROM (
            SELECT *                                 
            ,      LEFT(CONVERT(varchar, Date,112),6) ym
            FROM (SELECT DATE
                  ,      (SELECT (DATENAME(dw,
                                 CAST(DATEPART(m, DATE) AS VARCHAR)
                                 + '/'
                                 + CAST(DATEPART(d, DATE) AS VARCHAR)
                                 + '/'
                                 + CAST(DATEPART(yy, DATE) AS VARCHAR))
                         ))          DayOfWeek
                         FROM (SELECT Row#
                                     ,CAST('20000101' AS DATETIME) + Row# [DATE]
                                FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Row#
                  FROM sys.all_columns) t1) t2) tbl1
                  WHERE DayOfWeek = 'Monday') tbl2
        ) tbl3
    WHERE RowNumber = 3

If you find this script useful please donate generously.

Passing Pamertrised Query from Excel to SQL Server using Password Protected VBA

Passing Dynamic Query Values from Excel to SQL Server describes step by step solution of this use case.  The disadvantage of this approach is that the connection string is accessible by the end user.  The way around of this issue is to create password protected VBA as follows:

Sub btnRefresh_Click()
On Error GoTo Error_Handler
    Dim SellStartDate As String _
    , SellEndDate As String _
    , strSQL As Variant _
    , sSQLQry As String _
    , Conn As New ADODB.Connection _
    , mrs As New ADODB.Recordset _
    , DBPath As String _
    , sconnect As String

        Call ClearWKSData(ThisWorkbook.Worksheets("Your Sheet Long Name"), 2, 1)
        sconnect = "WSID=*;DRIVER=SQL Server;APP=Microsoft Office 2013;SERVER=Your Server Name

;UID=Your SQL Account Name;PWD=Your Password;DATABASE=Your Database Name;"
        SellStartDate = Sheets("Change Parameters").Range("C3").Value
        SellEndDate = Sheets("Change Parameters").Range("C4").Value
        strSQL = strSQL + " SELECT * FROM dbo.YourUserDefinedFunction('" & SellStartDate & "','" & SellEndDate & "')"
        Conn.Open sconnect
        mrs.Open strSQL, Conn
        Sheets("Purchase Order Spend").Range("A2").CopyFromRecordset mrs
        mrs.Close
        Conn.Close
        ThisWorkbook.Worksheets("Your Sheet Long Name").Activate
Exit_Procedure:
        Exit Sub

Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Sub ClearWKSData(wksCur As Worksheet, iFirstRow As Integer, iFirstCol As Integer)

On Error GoTo Error_Handler
    Dim iUsedCols As Variant, iUsedRows As Variant
        iUsedRows = wksCur.UsedRange.Row + wksCur.UsedRange.Rows.Count - 1
        iUsedCols = wksCur.UsedRange.Column + wksCur.UsedRange.Columns.Count - 1
        If iUsedRows > iFirstRow And iUsedCols > iFirstCol Then
            wksCur.Range(wksCur.Cells(iFirstRow, iFirstCol), wksCur.Cells(iUsedRows, iUsedCols)).Clear
        End If
Exit_Procedure:
        Exit Sub

Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Change Parameters").Activate
End Sub

The .xlsm sample file is available for download. To lock the VBA please press Alt+F11 

-> VBA Project Properties -> Protection Tab -> Lock project for viewing -> Enter your password.

If you find this script useful please donate generously.

Replace String In File

The following PowerShell script replaces all occurrences of the string in file.

#Usage powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 "c:\Temp\DirlistDEV.txt" '.' '`,'
#Calling from VBA: Call Shell("powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 'c:\Temp\DirlistDEV.txt' '.' ','")

    Clear-Host;
    $inputFile = $($args[0]);
    if ($inputFile) {
        Write-Host "Passed: $inputFile`n";
            $index = $inputFile.IndexOf(".");
            if ($index -eq 0) {
                Write-Host "Input file does not have any extensions";
                break;
            }
    } else {
        Write-Host "No file passed";
        break;
    }
    $findString = $($args[1]);
    if ($findString) {
        Write-Host "Passed string to find: $findString`n";
    } else {
        Write-Host "No string to find passed";
        break;
    }
    $findString = $findString.Replace(".","\.");
    $findString;
    $Replacement = $($args[2]);
    if ($Replacement) {
        Write-Host "Passed replacement string : $Replacement`n";
    } else {
        Write-Host "No replacement string passed";
        break;
    }
    $outputFile = $inputFile.SubString(0,$index) + ".tmp"
    $outputFile;
    Get-Content $inputfile | ForEach-Object { $_ -replace $findString, $Replacement } | Set-Content ($outputFile)
    Remove-Item $inputfile
    Rename-Item $outputFile $inputfile 

If you find this script useful please donate generously.

MS SSRS Reporting Management vs TFS Branches Reconcilation

The MS Access database attached allows to reconcile DEV, UAT and PROD TFS Branches with SSRS reports deployed to the Reporting Management Service.  It requires to create a ODBC connection to ReportServer SQL Server PROD database and uses the Catalog table records to compare against.
The ReconcilePROD_ReportingServerManagementWithTFSBranches.accdb database availlable for download.

If you find this script useful please donate generously.

Extracting MS Active Directory Users

The following user defined function returns query against MS Active Directory:

USE active_directory
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT * 
          FROM sys.objects
          WHERE name = 'implementation.ADusers')
        DROP FUNCTION implementation.ADusers
        PRINT 'DROP FUNCTION implementation.ADusers'
GO

PRINT 'CREATE FUNCTION implementation.ADusers'
GO

CREATE FUNCTION implementation.ADusers()
/************************************************************************************************************************************
AD users

Usage: EXEC sp_executesql @strSQL

Change Log
 #    Date       Who  Change
*************************************************************************************************************************************/ 
 RETURNS nvarchar(max)
 BEGIN
    DECLARE @strSQL1 AS varchar(600)
    ,       @strSQL2 AS varchar(max) 
    ,       @strSQL3 AS varchar(100)
    ,       @strSQL  AS nvarchar(max) 

    SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
    ,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
                    ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
                END AS datetime
            ) FROM (
    '

    SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
    FROM (
    SELECT char(decimal#) alpha
    ,'SELECT  samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint) 
        FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
        FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
        WHERE    objectCategory = ''''Person'''' and    objectClass = ''''USER'''' and samaccountname = '
     + '''''' + CHAR(decimal#) + '*' + ''''''''
     + IIF(CHAR(decimal#) <> 'z',' 
    ) UNION ALL 
     ','')    strSQL
    FROM (select row_number() over (order by (select 1)) as decimal#
    FROM sys.all_columns) cte_tally
    WHERE decimal# > 96 and decimal# < 123) alpha_union

    SET @strSQL3 = (SELECT ')) t    
    WHERE t.distinguishedName LIKE ''%OU=Users%''')

    SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3
    RETURN @strSQL
END

If you find this script useful please donate generously.

Extract SSRS reports objects

 The script below is:

·       Retrieving object names from Data Sources of SSRS reports stored as XML on Report Server database

·       Selecting these objects definitions from information schema of the desirable database

·       Searching a keyword in the report definition

;WITH
     XMLNAMESPACES
         (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
                 ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
          AS rd)
    ,DEF AS
        (SELECT RPT.ItemID
                ,RPT.Name
                ,RPT.ReportPath
                ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
                ,CommandText = REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))),'&gt;', '>'),'&lt;', '<')
         FROM (SELECT RPT.ItemID
                        ,RPT.Name
                        ,RPT.Path AS ReportPath
                     ,RPT.name AS ReportName
                     ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
               FROM ReportServer.dbo.[Catalog] AS RPT
               WHERE RPT.Type in (2, 5, 7, 8)
             ) AS RPT
         CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode))
    SELECT   DEF.Name ReportName
            ,DEF.ReportPath
            ,DEF.DataSetName
            ,DEF.CommandText
            ,o.OBJECT_NAME
            ,o.OBJECT_DEFINITION
    FROM DEF
        ,(select o.name as OBJECT_NAME
                , m.definition as OBJECT_DEFINITION
            from sys.sql_modules m
            inner join sys.objects o on o.object_id = m.object_id
            WHERE (o.type in ('P','V','TF','IF','FN')
            AND (m.definition Like '%fld_current%'
            OR m.definition Like '%originalcost%')
            )
    ) 

    WHERE DEF.CommandText like '%' + o.object_name + '%'

If you find this script useful please donate generously.

Parsing MDX by Dynamic Query

The following script is an example of parsing MDX by Dynamic query and storing the result in a temporary table.

USE MyDataBase
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET FMTONLY OFF
GO

SET NOCOUNT ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename;
GO

IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Last_Period_Revenue_MA')
DROP PROC dbo.Last_Period_Revenue_MA;

PRINT 'DROP PROC dbo.Last_Period_Revenue_MA';
GO

PRINT 'CREATE PROC dbo.Last_Period_Revenue_MA';
GO

CREATE PROC dbo.Last_Period_Revenue_MA
/************************************************************************************************************************************
Description:
Change Log

# Date Who Change
*************************************************************************************************************************************/
@CustomerCode NVARCHAR(255)
,@Period INT = -12
AS
BEGIN
BEGIN TRY

DECLARE @StrMDX AS NVARCHAR(MAX)
,@BOMDate AS SMALLDATETIME
,@DateFr AS SMALLDATETIME
,@DateTo AS SMALLDATETIME
,@StrSQL AS NVARCHAR(MAX)
,@Amount FLOAT;

SET @BOMDate = DATEADD(d,-DAY(GETDATE())+1, GETDATE());
SET @DateFr = DATEADD(m, @Period, @BOMDate);
SET @DateTo = DATEADD (d, -1, @BOMDate);

CREATE TABLE #MSCRM_Roll_DataSet1 (Amount FLOAT NULL);

CREATE TABLE #MSCRM_Roll_DataSet2 ( CustomerType NVARCHAR(255) NULL
,CustomerName VARCHAR(255) NULL
,CustomerSite VARCHAR(255) NULL
,FinancialYearDaily NVARCHAR(255) NULL
,FinancialPeriodDaily NVARCHAR(255) NULL
,CalendarDateDaily NVARCHAR(255) NULL
,ExclAmount FLOAT NULL);

CREATE TABLE #MSCRM_Roll_DataSet3 ( MthName NVARCHAR(20) NULL
,YrMth NVARCHAR(10) NULL
,ExclAmount FLOAT NULL);

SET @StrSQL = '([Calendar].[' +
right('0'+CAST(DAY(@DateFr) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateFr) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateFr) AS nvarchar) + '] : [Calendar].['
+ right('0'+CAST(DAY(@DateTo) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateTo) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateTo) AS nvarchar) + '])';

SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet1 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT { [Measures].[Exclusive Amount] } ON COLUMNS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';

EXEC sp_executesql @StrMDX;

SET @Amount = (SELECT Amount FROM #MSCRM_Roll_DataSet1)
SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet2 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT NON EMPTY { [Measures].[Exclusive Amount] } ON COLUMNS
,NON EMPTY { ([Customer].[Customers].[Ref Customer Site].ALLMEMBERS
* [Calendar].[Calendar Date Daily].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].&[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';

EXEC sp_executesql @StrMDX;

INSERT INTO #MSCRM_Roll_DataSet3

SELECT MAX(DATENAME(m, CAST(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2)
+ '-' + SUBSTRING(CalendarDateDaily,1,2) AS SMALLDATETIME)))
, MAX(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2))
, SUM (ExclAmount) from #MSCRM_Roll_DataSet2
GROUP BY SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2);

WHILE @DateFr < @DateTo
BEGIN

INSERT INTO #MSCRM_Roll_DataSet3
SELECT DATENAME(m,@DateFr)
, DATENAME(year,@DateFr)
+ '-' + right('0'+CAST(DATEPART(MONTH,@DateFr)as NVARCHAR(2)),2) , 0;

SET @DateFr = DATEADD(m,1,@DateFr);

END;

SELECT MthName
,MthName + ' ' + SUBSTRING(YrMth,3 , 2) MthYr
,YrMth
,ExclAmount
FROM #MSCRM_Roll_DataSet3;

DROP TABLE #MSCRM_Roll_DataSet1;
DROP TABLE #MSCRM_Roll_DataSet2;
DROP TABLE #MSCRM_Roll_DataSet3;

END TRY
BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

END
GO
/***************************************************************************************
Call Sample:

EXEC dbo.Last_Period_Revenue_MA
'BAUL4021 - Baulderstone Queensland Pty Ltd'
,-24
****************************************************************************************/

If you find this script useful please donate generously.