# 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,
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([day], ((DATEDIFF([day], 0, @dt ) / 7) * 7) +7, 0) --Next Monday's Date

```

# Multiple Ways to Get End of Month for a Date

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

Select
Eomonth(@dt)
,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')}

\$wc = New-Object System.Net.WebClient

foreach (\$sourceFile in \$mySrcs){
\$sourceFileName = \$sourceFile.href
\$targetFileName = \$targetDirectory + \$sourceFileName