SQL Server Innovations

 Dynamic Filtering Power BI Report based on Current Month I had a client requirement to filter Power BI report dynamically based on current month.   And for which, I have created calculated column,which can do the trick as below IsCurrentMonth = IF(YEAR('rpt_BaseModel vw_AssetMonthly'[LastDayOfMonth]) = Year(TODAY()) && MONTH('rpt_BaseModel vw_AssetMonthly'[LastDayOfMonth]) = Month(TODAY()),"Yes","No") Note: You will need to tweak DAX as per your scenario

   Command Prompt Script to Append Flat Files There was a requirement to merge \ append multiple flat files within one folder and in addition - it should also be able to read header file from 1st file but then remove header row from other subsequent flat files  Below Command Prompt script  will be able to resolve this issue: @echo off set "skip=" >summary.txt (   for %%F in (*.csv) do if defined skip (     more +1 "%%F"   ) else (     more "%%F"     set skip=1   ) )

 Powershell Script to Append Flat Files There was a requirement to merge \ append multiple flat files within one folder and in addition - it should also be able to read header file from 1st file but then remove header row from other subsequent flat files  Below Power shell script  will be able to resolve this issue: $getFirstLine = $true get-childItem "C:\temp\Mergefiles\LS_CTD_PYMTHIST_NF*.txt" | foreach {     $filePath = $_     $lines =  $lines = Get-Content $filePath       $linesToWrite = switch($getFirstLine) {            $true  {$lines}            $false {$lines | Select -Skip 1}     }     $getFirstLine = $false     Add-Content "LS_CTD_PYMTHIST_NF.txt" $linesToWrite     }

How to Transform String in SQL Server Say for example if you have column which is returning values such as 401*1 and you want to extract number from string T-SQL Code:  Select '401*1' AS ALTERNATE_ID, REVERSE(PARSENAME(REPLACE(REVERSE('401*1'), '*', '.'), 1)) As TransformedAlternateID

There was a requirement to stop a SQL Server Agent job if any of the Staging Tables are not loaded in to Data warehouse And to keep it simple and perform this action - I have added a step in SQL Server Agent Job to check Audit table to ensure all the Staging tables are loaded before loading them in to Data warehouse   DECLARE @dbState INT; SELECT @dbState = RowCountCheck FROM [StagingDatabase].[vwEtlRowCountCheck]  WHERE RowCountCheck = 0 --AND TableName NOT IN ('Audit_Log', 'Config','CHARGES_DUE_PER_ASSET') ; IF @dbState = 0 BEGIN   RAISERROR('All Staging tables are not loaded correctly', 11, 1); END