Month: January 2016

Date Range along with Week Number in SQL

I needed to get work week date range along with corresponding week number in sql and below is the TSQL code I came up with.
NOTE: I had to do this on SQL 2008, so, I could not use window functions available in SQL 2012 and later.

declare @StartDate date
declare @EndDate date
set @StartDate ='12/31/2015'
Set @EndDate='12/31/2016'

;with CTE as (Select datename(dw,dateadd(day,number,@StartDate)) as [Day],
cast(datename(week,dateadd(day,number,@StartDate)) as int) as [WeekNo],
dateadd(day,number,@StartDate) as [Date],number
from master.dbo.spt_values
where type='P' and number>0
and datename(dw,dateadd(day,number,@StartDate)) not in ('Saturday','Sunday')
and dateadd(day,number,@StartDate)<=@EndDate)

Select convert(varchar(20),min(Date),106)+ ' - ' +convert(varchar(20),max(Date),106) as [Date Range],
'Week '+cast(Weekno as varchar) as [WeekNo]
from Cte
group by Weekno
order by cast(Weekno as int)

Advertisement

Disk information using powershell

One of the things DBA’s need to make sure is that database server disks do not run out of space. So, the disk free space should be monitored, however, if you manage multiple servers, it is not always convenient to log into every server. The below powershell code will return the disk information including available free space.

$ServerList ="ServerA","ServerB"
foreach ($Server in $ServerList)
{
#Eliminates System Volume and DriveType=5 (Media Drive).
get-wmiobject "win32_volume" -computerName $Server | Where-Object {$_.SystemVolume -like "False" -and $_.DriveType -ne 5}|
Select-Object SystemName,Name,Label,Capacity,FreeSpace,BlockSize|Format-Table -AutoSize
}