SQL: Group datetime by an arbitrary time interval

Yesterday, a co-worker came to me with a puzzle, he wanted to write a query that would group records by intervals of 5, 10, 15 minutes, ideally just passing a parameter to the query to specify the time interval, I sat down and after 5 minutes I came up with the solution:

declare @interval int

set @interval = 5

select datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

, count(*)

from thetable

group by datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

The trick is highlighted, though it looks like the division and multiplication eliminate each other, what is really happening is an integer division, which, multiplied by the same number, gives you the right intervals:

here's the (minutes) values pattern for an interval of 5


minute divided by 5 multiplied by 5
0 0 0
1 0 0
...
5 1 5
6 1 5
...
10 2 10
11 2 10
...
from there you can include more fields in your select criteria, make it prettier, etc

declare @interval int

set @interval = 5

select convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

, count(*)

from the_table

group by convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

which produces something like:

11/12/10 10:10    28

11/12/10 10:15    11

11/12/10 10:20    57

hope this is useful

SQL: You can't update a function while debugging it

article applies to: SQL debugging
keywords: SQL, debugging, VS2005, functions, stored procedures

I learned this the hard way yesterday while I was RDing (Remote Desktop) into my computer, debugging a SQL function from VS2005; I then, found a bug on the function and proceeded to update the function externally using Microsoft SQL Server Management Express (uff, that was tiring), however when I would execute the alter function it would take forever and never actually come back, I thought it was something with my RD session, so I logged in to the SQL server directly and tried there with same results, after a few minutes of waiting for SQL to commit my changes I realized I was debugging, and had stopped on a breakpoint on that function, I then exited out of VS debugging, tried again and voila it worked instantly.

So, yes, it was my bad, but I never got a timeout or any message indicating what the problem was, what if two developers are working on the same thing?
perhaps some work could be done to fix that, I can't imagine that being too hard, it DOES know that is locked, why not just give the user a message?

anyway, just sharing my experience with you, hoping it helps

No anchor member was specified for recursive query *

I got this error while working on a recursive CTE query, I entered the error message in google/live and got zero results so I had to do some more study on CTEs and I thought I'd share the answer to this problem.

From the MS documentation we read:
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.
CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

Basically you cannot make a recursive call before you have defined the initial data set, let's see with an example:

This would be valid:

with example(col1, col2, iteration) as
(
--first, we defined the base set
select col1, col2 from someTable where col1 is null
union all
--then we can make the recursive call
select t1.col1, t1.col2
from example t1 inner join SomeTable t2 on t1.Col2 = t2.Col1
)
select * from example

and this would generate the error

with example(col1, col2, iteration) as
(
select t1.col1, t1.col2
from example t1 inner join SomeOtherTable t2 on t1.Col2 = t2.Col1
--ERROR:we have not defined a base set before making the recursive call
union all
select col1, col2 from someTable where col1 is null
)
select * from example

Hope this helps someone out there

find all tables with column name *

Applies to: SQL
Tested on: SQL 2005
Keywords: SQL, column, all, tables

I'm going to need this quite often...

just execute these queries on the same database where the tables reside

--get all table names for a specific column name
SELECT o.name as TableName
FROM sysobjects o inner join syscolumns c on o.id=c.id
WHERE c.name = 'YOURCOLUMNNAMEHERE'

you can also perform like queries

--get all columns and table names for a 'like column' query
SELECT c.[name] as ColumnName, o.name as TableName FROM sysobjects o inner join syscolumns c on o.id=c.id
WHERE c.name like 'COLUMN%'