Quarter Calculation T-SQL


\CREATE TABLE mytable(
   Qtr         VARCHAR(20) NOT NULL  
  ,CurrentQtr  VARCHAR(2) NOT NULL
  ,CurrentYear INTEGER  NOT NULL
INSERT INTO mytable(Qtr,CurrentQtr,CurrentYear) VALUES
 ('CurrentQuarter','Q1',2019 )
,('CurrentQuarterMinus1','Q1',2019 )
,('CurrentQuarterMinus2','Q1',2019 )
,('CurrentQuarterMinus3','Q1',2019 )
,('CurrentQuarterMinus4','Q1',2019 )
,('CurrentQuarterMinus5','Q1',2019 )
,('CurrentQuarterMinus6','Q1',2019 )
,('CurrentQuarterMinus7','Q1',2019 )
,('CurrentQuarterMinus8','Q1',2019 );

;with myctedate as (
SELECT  Qtr,CurrentQtr,CurrentYear,
	Dateadd(Quarter,ISNULL(-try_cast(right(Qtr ,1) as int),0),
	Cast(CAST(CurrentYear AS CHAR(4))+'-'+
CASE WHEN CurrentQtr = 'Q1' THEN '01' 
WHEN CurrentQtr = 'Q2' THEN '04'
WHEN CurrentQtr = 'Q3' THEN '07'
WHEN CurrentQtr = 'Q4' THEN '10' Else NULL END +'-01' as Date) ) QuarterDate
From mytable

 Select Qtr,CurrentQtr,CurrentYear,
 Concat(Format(QuarterDate,'yy'),'Q', Datepart(QUARTER,QuarterDate)) ans
 from myctedate

 drop table mytable



Find Monday’s Date in a Week

declare @dt datetime=getdate()

select DATEADD([day], ((DATEDIFF([day], 0,@dt ) / 7) * 7) , 0) --Monday's Date
,Dateadd(week,datediff(week,0,@dt),0)  --Monday's Date

, DATEADD([day], ((DATEDIFF([day], 0, @dt ) / 7) * 7) +7, 0) --Next Monday's Date
,Dateadd(week,datediff(week,0, @dt)+1,0)  --Next Monday's Date


Multiple Ways to Get End of Month for a Date

declare @dt datetime='2016-03-02'

,Dateadd(month,month(@dt)-12,Datefromparts(Year(@dt),12,31) )
,DateAdd(DAY, -1, dateadd(MONTH, 1, Datefromparts(Year(@dt),Month(@dt),1)))
,DateAdd(DAY, -1, dateadd(MONTH, 1, convert(char(6), @dt, 112) + '01'))
,DateAdd(Month, 1 + DateDiff(month,0,@dt), -1)
,DateAdd(Month,DateDiff(month,0,@dt)+1, 0)-1
when Month(@dt) in (4,6,9,11) then 30  
when Month(@dt) in (1,3,5,7,8,10,12) then 31  
  case when Month(@dt) =2 
 and Try_Parse(Concat('02','/29/',Year(@dt))  as datetime) is not NULL 
 then 29  else 28 
 END ) 


Check the User Who Started a Long Running Job Query


SELECT top 1  msdb.dbo.agent_datetime(run_date, run_time) as 'Job_RunDateTime',
	, sh.run_date, sh.run_duration
	, sh.step_name
	,Substring(sh.message,charindex('The Job was invoked by User',sh.message)
	,charindex('The last step to run',sh.message)-charindex('The Job was invoked by User',sh.message))
	[The Job was invoked by User]
	 , sh.message 
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
join msdb.dbo.sysjobs_view job on job.job_id=sj.job_id
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where activity.run_Requested_date is not null and activity.stop_execution_date is null  
and  sj.name = N'my agent job name'	AND step_id = 0 
Order by   msdb.dbo.agent_datetime(sh.run_date, sh.run_time) desc ;


Import Excel File from SSMS with Missing Provider Error

When you try to import Excel file into your database from SSMS, you may run into one of the following errors:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.14.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.15.0’ provider is not registered
The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered

In order to quickly get the excel data into database instead of trying to jumping around to fix the issue, we can use the 64bit wizard already installed on our machine.

You can use the wizard from any of the following:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\140\DTS\Binn\DTSWizard.exe
C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe

You can create a shortcut on your desktop to use it next time.
The list below if for reference to the version number corresponding to SQL Server version:

110 SQL Server 2012
120 SQL Server 2014
130 SQL Server 2016
140 SQL Server 2017
150 SQL Server 2019


Scrap files from a website with powershell


$url = "https://www.nass.usda.gov/Publications/AgCensus/2017/Online_Resources/County_Profiles/Iowa/"
$targetDir  ="c:\temp\mypdf\"

$WebResponse = Invoke-WebRequest $url
$mySrcs=$WebResponse.AllElements | Where { ($_.href -like 'cp*.pdf*' -and $_.Tagname -eq 'a')}  

function DownloadFile([Object[]] $sourceFiles,[string]$targetDirectory) {            
 $wc = New-Object System.Net.WebClient            
 foreach ($sourceFile in $mySrcs){            
  $sourceFileName = $sourceFile.href            
  $targetFileName = $targetDirectory + $sourceFileName            
  $wc.DownloadFile($url+ $sourceFileName, $targetFileName) 
  Write-Host "Downloaded $sourceFile to file location $targetFileName"             
DownloadFile $mySrcs $targetDir