I'm working on some "star rating" code and I really wanted to round numbers of to the nearest fraction. While the math is really basic, my math skills are rusty and this took me longer to solve than I care to admit. So I thought I'd just blog the solution for anyone else that might find it useful:

round( value/fraction ) * fraction

Like I said, it's very basic math but is very handy when you want to round to something other than to the closest integer.

I was working on some code today where I wanted to output a time span in the format 1d 4h 36m (i.e. "1 day, 4 hours and 36 minutes.") I wanted a pure SQL solution so that I didn't have to worry about formatting later. Here's some example SQL that will format a time span by days, hours and minutes.

declare @startTime datetime
set @startTime = '2009-07-11 14:19:40.000'

declare @endTime datetime
set @endTime = getUtcDate()

select 
    convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd ' 
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm' as Format1
  , case
      when (((dateDiff(mi, @startTime, @endTime)/(24*60))) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd ' 
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
      when (((dateDiff(mi, @startTime, @endTime)%(24*60)/60)) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
      else
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
    end as Format2
  , convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + ':' 
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60), 2) + ':'
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60), 2) as Format3

NOTE:

Make sure to keep the dateDiff() time in minutes. If you switch to hours (or days) you'll have rounding issues you'll have to work around. By sticking to using minutes for the equations, you avoid the rounding issues.

When you run this query in Microsoft SQL Server, you'll see that it outputs 3 columns—Format1, Format2 and Format3.

Format1 always outputs the time span in format 0d 0h 0m, even when the days or hours are 0. In my case, I really wanted to ignore days if no days have passed and hours if we're still under 60 minutes. This lead me to create Format2.

Format2 still uses the 0d 0h 0m format, but it will drop off days if less than 24 hours old and drop hours if less than 60 minutes has passed. This leaves you strings like "6d 4h 52m", "4h 10m" or "3m". In my case, this was the best formatting for my uses.

Format3 is an alternative format that places the time span in the format d:hh:mm. While I'm not using that formatting currently, some people may find it useful. This also shows off how you can force the hours and minutes to output as a digit value (i.e. 0:02:01 = 0 days, 2 hours and 1 minute.)

Hopefully this will prove useful to some of you.

I was working on restructuring some old code that needed some pagination. The query in question used a one-to-many join the required information together. Imagine a search engine where you're wanting to search over orders, but want to group the results by customer. The output might look something like:

So, in my situation I want to also paginate by the customers. When SQL Server 2005 was introduced, it added a new feature called Common Table Expressions (CTEs.) One of the most useful features of CTEs is to paginate recordsets. Typically when paginating results, you will use the row_number() function—which creates a new unique row number for each row in your recordset:

I finally got tired enough of having to click the "Connect" button at SQL Server Management Studio startup, that I decided to look for a way to bypass this prompt. Since in development, I typically am only ever dealing with the same server, the connection dialog always added an extra step to the process of getting up and running.

So, a quick search on Google turned up a list of the following command prompt switches you can use to specify a server (and database) to use when starting up:

Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P
password] [-E] [file_name[, file_name]] [/?]

[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information

So, all you need to do to get SQL Server Management Studio to automatically log in to a specific server is to right-click on your SQL Server Management Studio icon, and change the "Target" to something like:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S SERVERNAME -d DATABASENAME

I've come across several clever and convenient tricks for dealing with dates recently in SQL Server. I previously blogged about how to remove time from date/time stamp in SQL Server, but I've come across a group of other really useful date/time manipulations that can be very useful when dealing with SQL Server.

The basic idea behind most of these tricks is that by using a known base datetime stamp, you can then use simple addition and subtraction to calculate the new dates. For example, if I calculate the difference in days between Jan 9, 2008 at 08:00 Jan 1, 2000 at 00:00, I'll end up with a whole number of days which I can then and add back to Jan 1, 2000 at 00:00 and I'll end up with Jan 9, 2008 at 00:00.

Take the tricks below and play around to see what other useful combinations you can develop. If you have any other useful date/time tricks, let me know!

Yesterday I wrote a blog entry talking about dynamically generating a table of dates in MSSQL 2005. My original solution involved common table expressions (CTE) and involved recursion. While there's nothing wrong with using recursion this way, I mainly was using recursion to simplify the UDF code into a single SQL expression.

However, the more I started thinking about the problem last night, I decided I really needed to revise things a bit further. I decided I really needed to have the query return both a start and end date for each row in the recordset—as this would make it much easier to things such as find all the orders placed during each hour of the day.

So, this morning I came in and re-wrote my query and optimized a few things. The first thing I did was remove the recursion, so this UDF should now work in SQL Server 2000 as well as SQL Server 2005. It's important to note that you could do the same thing with a physical table, but for my uses my date range table generally would only include at most a few dozen rows and there's not enough of a performance difference to warrant the pain of maintaining a table of every date and possible combination you may want to return aggregate results over.

The new code looks like this:

[UPDATED: Thursday, January 08, 2009 at 11:04:25 AM]

UPDATE:

I've revised this concept, so please see the new version: Dynamically generating a table of dates in Microsoft SQL (Part Deux!)

I'm working on some code where I need to show some trend date over various periods of time. One of the issues with working with SQL and aggregate values is that it'll only show you values where there was some data, so if you want to print the number of orders on each day and some days had no orders, they wouldn't show up in the results.

For what I was working on, I needed to ensure that even missing days would show up in my query results. This got me thinking about the best way to accomplish this task. After thinking about the problem for a while, I decided the ideal method would be if I could dynamically generate a table of dates that I could then use in a LEFT OUTER JOIN.

[UPDATED: Wednesday, January 21, 2009 at 8:19:25 AM]

I was reading the SQL SERVER - 2008 - 2005 - Find Longest Running Query - TSQL on Pinal Dave's blog and thought I'd run the query on my development machine to see what interesting things I'd fine.

However, right off the bat I was seeing something I thought was odd—my execution times looked way too high. After some quick searching, I confirmed my initial thought that MSSQL reports time in microseconds (which is one millionth of a second or one thousandth of a millisecond.)

For my needs reporting in milliseconds is more than enough, so I revised the query to report the time in milliseconds instead of microseconds. I also made some other modifications so that the query breaks the actual SQL statement and extra parameters into 2 columns (I derived this code from the an article on MSSQLTips.com.) I also added last execution time and minimum elapsed times for the query. I also changed the query to show the results by the average elapsed time—since I occasionally you'll see a single query spike with a high maximum elapsed time that might be due to external circumstances (although this is good to know too.)

Here's my revised version:

UPDATE:

I added another nullif() statement to further prevent divide by zero errors.

One thing to keep in mind is that the DBCC FREEPROCCACHE will clear the cache and reset the log (obviously a reboot of the server or restart of the services would do the same thing.) You may have some maintenance scripts that might issue the DBCC FREEPROCCACHE command, so just keep this in mind when looking at the results.

You can use the query above for lots of purposes. With a little refactoring, you could change the ORDER BY clause to return queries that had executed recently—which can be useful if you're trying to debug a slow running template.

I thought I've blogged this tip before, but I saw someone else mention it on a mailing list this morning. If you ever want to remove the time portion in a MSSQL Server, you can do this by removing the decimal portion of the date/time stamp.

SQL Server stores date/times as a numeric value where the integer portion of the value represents the number of days since epoch and the time is represented by the decimal portion of the value. This allows us to cast a datetime field as a float and then round down to the nearest integer:

We first convert the datetime to a float:

We next use the floor() function to round down to the nearest integer:

Yesterday I was trying to divide two values to calculate a percentage, but the value was always coming up zero. What really threw me off was I had a query similar to the following and that was working fine:

The above query was properly returning the percentages I needed, but when I had a query like the following, all of the sudden it was just returning 0 (zero) as the result—which I knew was wrong:

The reason the above query kept returning zero, was because the SQL COUNT() function returns an INT. When MS SQL Server performs an operation on INT values, it returns an INT value. This means the division of two INT values will always return an INT.

The solution is to cast at least one of the two values to a FLOAT. The reason my first query was working fine is because the Rating column was a FLOAT column, which meant the division would return a FLOAT value.  The following is the same query, but will correctly return a FLOAT.

Take a look at the following in Query Analyzer to see how MS SQL Server handles the division operation:

Just something to keep in mind when using division operations in SQL Server. I thought I could simple cast my expression to a FLOAT to fix the problem, but since the division operation has already converted the value to an INT the resulting value will still be based on the original INT value.

NOTE:

Always remember to watch out for potential "Divide by zero error encountered" errors when performing division operations in SQL server. This can easily crop up, so make sure your query accounts for the possibility. An easy workaround is do something like:

ISNULL(dividend / NULLIF(expression, 0), 0)

The NULLIF(expression, 0) will make the value return null if the expression ends up being 0. This will then make the division operation return null as the result. The ISNULL() will then convert the resulting null value so that it actually returns 0 instead of null.

Paginating data is one of those commonly required tasks. In previous version of MS SQL Server it's been tricky to handle, but made easy via the use of stored procedures that handle the pagination logic for you. However, if you're using SQL Server 2005 there's a much easier way to handle pagination and that's to use a Common Expression Table (CTE.)

Microsoft created the CTE syntax to make solving complicated tasks easier (such as returning a query of hierarchical tree data.) When you create a CTE, you're essentially creating a virtual table you can query against. CTEs generally perform very well and can often replace the need for creating temp tables.

Let's look at an example of query out records 11-20 from a Employee database:

-- create the Common Table Expression, which is a table called "pagination"
with pagination as
(
    -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
    select
        row_number() over (order by department, employee) as rowNo,
        -- a list of the column you want to retrieve
        employeeId, employee, department 
    from 
        Employee
    where 
        disabled = 0
)
-- we now query the CTE table
select 
    -- add an additional column which contains the total number of records in the query
    *, (select count(*) from pagination) as totalResults
from
    pagination
where 
    RowNo between 11 and 20     
order by
    rowNo

The first part of the query creates the table expression we'll actually query against when we grab the actual results from the database. In the CTE you're going to write the SQL you'd normally write to grab all the records with one exception—you use the row_number() function to generate a virtual column that orders your result rows for you. The actual "order by" clause you want to use will actually go in the row_number() declaration—this makes SQL Server assign the correct row number for each record.

It's important to remember that a CTE can be as complex as you need it to be. You can do joins, pivots, etc—whatever you need.

The next step is to actually query against the CTE you created. This query will virtually be the same for all pagination queries you write. You are simply grabbing all of the columns and then limiting the returned data to just the rows you want to display (in this case rows 11 - 20.)

In this example, I added an additional column that contains the total results that were returned from the CTE. This is not needed, but it's handy if you need to do something like "Results 11 - 20 of 4,567 records." There are many ways you could generate the total records, this is just one method.

If you've upgraded to SQL Server 2005 and haven't started using CTEs yet, you really need to start getting familiar with them as they really simplify many tasks in SQL Server that previously were very difficult to solve.

Someone asked me this morning how they could determine distinct relationships. They wanted to be able to retrieve all the distinct rows where someone had defined another user as a friend or any users who had defined them as a friend. The table schema looked like this:

As you can see friend 1 is pretty popular amongst other users. However, he only has 1 person defined as friend of his—friend 10. What we need to do is to find all distinct cross-column relationships in the table—which means only one row should be returned for the relationship between friend 1 and friend 10.

As I mentioned early I've been working on a SVN post-commit script. We've got a SVN repository that will be modified by several remote developers and I really need to keep an eye on this repository and I need to closely monitor changes to this repository.

There are two major functions that I needed in my post-commit script:

1. I needed to update the working copy on my server
2. I needed to e-mail the changes to myself, so I know when developers are making changes

There are an abundant of examples showing off how to do this in various *nix flavors, but I couldn't find any good Windows-based solutions that didn't require Perl to be installed on the server. That led me to create the following post-commit.bat script.

I've been working on a post-commit hook for our Subversion install and was running in to a number of issues. The post-commit.bat file would run fine from command line, but I just could get things to work as I expected from SVN. After much debugging and scouring Google for answers, I've found a few tips that will hopefully help you to troubleshoot your own SVN repository hooks.

1) Subversion executes all hook programs with an empty environment

This was the biggest issue I was running in to, because I was expecting the my script to be able to find any programs in my %PATH% statement. That's the main reason my scripts were working fine from command line, but were breaking when executing from my SVN hook.