Tag: Report Usage

Calculating working hours between two dates

As a follow-up to an earlier post (Return a list of all dates between a start and end date), I need to find the number of working hours between two timestamps – in this case, it was to see how long a support ticket had been open before it was initially assigned, but the user didn’t want non-work hours to count against them.

To do this, I used the previous script to generate a list of dates and hours, and then marked the rows as work time or not (based on day of week and hour of day, evaluated together). The result was a table that would effectively let me do a SUM to find the value I was looking for. Once I had that table, I could join to it for rows between the two datetimes in question and SUM up rows that had “WorkTime” marked:

SELECT tt.TicketNumber,
       tt.TicketCreateTime,
       tt.TicketAssignTime,
       SUM(  CONVERT(INT, wh.WorkTime)) as WorkHoursBeforeAssigned
       COUNT(CONVERT(INT, wh.WorkTime)) as TotalHoursBeforeAssigned
  FROM TroubleTickets tt
  JOIN #WorkingHours wh
    ON wh.EvaluateTime BETWEEN tt.TicketCreateTime
                           AND tt.TicketAssignTime
GROUP BY tt.TicketNumber,
         tt.TicketCreateTime,
         tt.TicketAssignTime

In this case, tickets that were created and picked up after hours, without passing any worktime, would show as zero hours old (as they should, since they were interested in working time) – however, I’ve also included COUNT here to show total hours as well as work hours.

Also, this script only counts for raw day-of-week and hour-of-day working time – it ignores holidays and other special circumstances. I have a script that tracks holidays (American ones, at least), and I’ll put that up shortly as well – if you want to take holidays into account, you could incorporate that into your evaluation.

Here’s the script that builds the working time table (you can also download it here):

-- Set things up before we get started
--------------------------------------
DECLARE @WorkTimeStart		TINYINT,
		@WorkTimeEnd		TINYINT,
		@WorkDayOfWeekStart	TINYINT,
		@WorkDayOfWeekEnd	TINYINT

DECLARE @StartDate			DATETIME,
		@EndDate			DATETIME

CREATE TABLE #WorkingHours (
		EvaluateTime	DATETIME,
		IsWorktime		BIT DEFAULT(0)
)

--------------------------------------

	SET @WorkTimeStart = 7  --7AM
	SET @WorkTimeEnd   = 16 --4PM hour (4-5PM count as working)
	SET @WorkDayOfWeekStart = 2 --Monday
	SET @WorkDayOfWeekEnd   = 6 --Friday

	SET @StartDate	= '2000-01-01 00:00:00'
	SET @EndDate	= '2020-12-31 23:59:59'

--------------------------------------


-- Built the list of timestamps we're working with
;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
                                   c1.column_id,
                                   c2.object_id,
                                   c2.column_id)
		 from sys.columns c1
        cross 
         join sys.columns c2)
INSERT INTO #WorkingHours (EvaluateTime)
SELECT DATEADD(hh, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(hh, number-1, @StartDate) <= @EndDate


-- Set the times to worktime if they match criteria
UPDATE #WorkingHours
   SET IsWorktime = CASE WHEN (DATEPART(dw, EvaluateTime)
								BETWEEN @WorkDayOfWeekStart
								AND @WorkDayOfWeekEnd)
							  AND
							  (DATEPART(hh, EvaluateTime)
							   BETWEEN @WorkTimeStart
							   AND @WorkTimeEnd) THEN 1
						 ELSE 0
					END


-- Retun the results
 SELECT * FROM #WorkingHours
 ORDER BY EvaluateTime

 DROP TABLE #WorkingHours

Receiving (or stopping!) SQL Server maintenance plan summary emails

When you use the Maintenance Plan wizard in SQL Management Studio to set up a new plan, you have the option to have the SQL Agent send you a plan execution summary email:

It’s a pretty handy email – not just a success/failure on the SQL Agent job, but details about the actual maintenance plan steps and which ones ran and for how long. The problem we ran into was how to actually turn this email off once it was initially enabled in the wizard! It’s not anywhere in the SQL Agent job or in the properties of the maintenance plan, or even anywhere obvious in the maintenance plan designer. It’s hidden in a tiny button on the toolbar in the designer called “Reporting and Logging”:

Clicking there gets you some logging options, including sending a summary email to an operator:

From there, you can change the operator or stop the email summary (or start it, if that’s what you’re looking for).

SQL Server Contention Monitor (super-alpha) posted to Codeplex

In response to some conversation in an Experts-Exchange question, I decided to post an application I wrote about four years ago and never did anything with (or add much polish, as you’ll see). It’s a small VB.NET application that polls MSSQL servers and displays information about currently blocked SPIDs, as well as the block chain and age of blocked processes.

It’s something I’ve used when I troubleshoot blocking on a SQL Server, and generally leave running in my system tray – it will pop up a balloon when there’s a blocked process that lasts longer than some pre-determined threshold, and then optionally pop another balloon when it’s resolved. It works well as an early warning system for a critical server, and I’ve often used it when I’m performing maintenance on a production server that I’m concerned might block legitimate use – running the application lets me know within a few seconds when I’ve gotten in somebody’s way so I can act accordingly.

It’s still very much a work in progress and is pretty rough around the edges (and well inside the edges), but I’ve posted it to a Codeplex project for anybody else who might find it useful. Though I’m working on a few other things right now and haven’t done any development with this app in almost four years, I’ll keep an eye on the issues list at Codeplex (or feedback here) and try to address anything that comes up.

Without any further ado, here it is!

https://mssqlblockmonitor.codeplex.com/

Export from SQL Server to XLS and email results

Sometimes you want to take some query results and export them directly to an XLS file – here’s how you can set that up in SQL Server. The biggest caveat is that you need to run it from an x86 instance of SQL Server – the x64 instance won’t have access to the Jet driver needed to write the Excel file (Microsoft.Jet.OLEDB.4.0), where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it – x64 is better in almost every case, but we can’t see to completely ditch x86… 🙂

I use a stored proc that I call from a SQL Agent Job, which works great. The actual process is a bit awkward – for starters, you’ll need access to xp_cmdshell. SQL Server can’t create a new Excel file from scratch, so you have to keep a blank Excel file around, make a copy of it, and then insert into the copy to get your final result.

That said, here’s the code to generate the XLS file from your query results:

SELECT Column1, Column2, Column3, Column4
  INTO ##YourTempTable
  FROM SomeOtherTable

SET @Folder = 'C:\Temp\'
SET @DocumentBlank = 'Your Document - Blank'
SET @DocumentLong = 'Your Document - ' + CONVERT(VARCHAR(10), GETDATE(), 120)

DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'COPY "' + @folder + @DocumentBlank + '.xls" "' + @Folder + @DocumentLong + '.xls"'
exec master..xp_cmdshell @CMD

-- Export the Excel sheet
SET @CMD = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
	''Excel 8.0;Database=' + @Folder + @DocumentLong + '.xls;'',
	''SELECT * FROM [Sheet1$]'')
	select Column1, Column2, Column3, Column4 from ##YourTempTable'

exec sp_executesql @CMD

Once that’s exported, you can just set up the email process using sp_send_dbmail and attach the file you just generated:

DECLARE @Body VARCHAR(2000)

SET @Attachments = @Folder + @DocumentLong  + '.xls'
SET @Body = 'Your file has been generated for ' + CONVERT(VARCHAR(10), GETDATE(), 120)

exec msdb..sp_send_dbmail @profile_name = 'YourMailProfile',
	@Recipients = 'Recipients@YourDomain.biz',
	@subject = 'Your file is ready',
	@Body = @Body,
	@file_attachments = @DocumentLong

Return a list of all dates between a start and end date

In some situations, you’ll need to work with a list of numbers or dates that are between some start or end, and you don’t have a complete list handy for joining to. Using the script below, you can create one to use in your query – if you wanted to use this in-line in another SQL Statement (and assuming you’re using SQL 2005+), you could either do a subquery or a WITH CTE clause and then join directly to it.

To do it, I’m using a table that has a large number of rows in it, even in an empty database (sys.columns), and then doing a cross-join to ensure that I’ll have enough rows to satisfy my entire range. This table has about 890 rows in an empty databases (or you can use the “model” database if you don’t have any user databases handy), meaning that the cross join yields about 800,000 rows – enough for almost 2200 years of days, or 100 years of hourly increments (change the “dd” in the “DATEADD” statements below to “hh” or even “mi” to do any increment of time you want).

The code:

DECLARE @StartDate DATETIME,
	    @EndDate   DATETIME

	SET @StartDate = '2012-12-01'
	SET @EndDate   = '2015-12-31'

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT DATEADD(dd, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(dd, number-1, @StartDate) <= @EndDate

I’m using dates above, but if you wanted to use INT instead, it’s pretty straightforward:
DECLARE @Start INT,
		@End   INT

	SET @Start = 1500
	SET @End   = 64000

;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT @Start + number - 1
  FROM numberlist
 WHERE @Start + number - 1 <= @End