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

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5b3dd56-5781-41ad-82e5-89a9ab9d7050/tsql-query-help-to-compute-quarteryear?forum=transactsql

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'

Select 
 Eomonth(@dt)
,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
,Datefromparts(Year(@dt),Month(@dt)
,
Case  
when Month(@dt) in (4,6,9,11) then 30  
when Month(@dt) in (1,3,5,7,8,10,12) then 31  
else 
  case when Month(@dt) =2 
 and Try_Parse(Concat('02','/29/',Year(@dt))  as datetime) is not NULL 
 then 29  else 28 
 end
 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',
	  sj.name
	, 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

https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine

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  

 

http://toreaurstad.blogspot.com/2013/03/using-powershell-to-download-multiple.html