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

MissingManifestResourceException was unhandled

Could not find any resources appropriate for the specified culture or the neutral culture.  Make sure "PostProcessing.ConfigurationForm.resources" was correctly embedded or linked into assembly "PostProcessing" at compile time, or that all the satellite assemblies required are loadable and fully signed.

There are many articles related to this one but they all point to a different solution than the problem I had, so I figured someone else might run into this

you can get this exception if you declare a class before the code of one of your forms

This will generate the error:

public class SomeTestClass {...}
...
public partial class ConfigurationForm : Form ...


The fix is simple, either move your class after the Form class, or (better yet) move it out to it's own file

my solution to Eric Lippert's quiz

Eric posted a quiz a few days ago, it generated quite a number of responses, so I thought I would answer with the shortest possible C# answer (with my previous team we used to kinda compete on refactoring), so here it is (Justin and Paul, bring it on!):
The problem:
Write me a function that takes a non-null IEnumerable and returns a string with the following characteristics:

(1) If the sequence is empty then the resulting string is "{}".
(2) If the sequence is a single item "ABC" then the resulting string is "{ABC}".
(3) If the sequence is the two item sequence "ABC", "DEF" then the resulting string is "{ABC and DEF}".
(4) If the sequence has more than two items, say, "ABC", "DEF", "G", "H" then the resulting string is "{ABC, DEF, G and H}". (Note: no Oxford comma!)

My solution:

static string JoinStrings(IEnumerable<string> strings) {

int len = strings.Count();

return "{"+(

(len > 1) ?

strings.Take(len - 1)

.Aggregate((string head, string tail) => head+", "+tail)+

" and " +strings.Last()

: (len == 1) ?

strings.First()

: "")+

"}";

}

Did you know? Interface members are allowed to be private

This one comes as a surprise to most people, but it is possible to have a private implementation for an interface member, let's look at a very simple example, just for purposes of the ilustration.

public interface ITest {

void Test();

}

public class Test : ITest {

void ITest.Test() {

Console.WriteLine("test");

}

public void Test2() {

Console.WriteLine("test2");

}

}

class Program {

static void Main(string[] args) {

ITest t = new Test();

t.Test();

Test t2 = new Test();

//t2.Test(); //<<=== doesn't compile

If you don't believe it you can try it of course.

But why is this useful or how do you use this?

The technique allows you to ensure that the method is only visible to those who are using a variable of the interface type. All this does is to force the use of the member through an instance of the interface, meaning, for this example, if you want to get access to the .Test method, you can only do so through a variable of type ITest.

The only trick to make this work is to precede the member declaration with the Interface type as in:

void ITest.Test();

Not the most useful of tricks, but something to have on the bag of tricks, or maybe something to make you win a bet ;)

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%'

Get last day of month

applies to: C#, .NET
In the spirit of sharing code, and I think this is the second time in this week that I need this function

DateTime GetLastDayOf(DateTime date) {
return new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month));
}

C# Quiz #1: overloads, strings, nullable types

*This is also a test of the latest Windows Live Writer

given this class definition:

public class Foo {
public void Bar(string x) {
Console.WriteLine("string x was called");
}
public void Bar(int? x) {
Console.WriteLine("int? x was called");
}
}

what will be output to the console with the following code:

Foo f = new Foo();
try {
f.Bar(null);
}
catch {
Console.WriteLine("no method was called");
}

*update: I messed up the code while trying to upload another post, I recovered the original post from my RSS feed

protected, internal, protected internal... are you sure you know them?

the code for this article applies to C#

We all take access modifiers for granted, but can you tell me what protected internal does?

I bet more than half .NET developers will get that wrong, let's review:

1. private:
   
1. This is the least permissive access level.
2. Accessible only within the body of the class or the struct in which they are declared.
3. Nested types in the same body can also access those private members
2. public:
1. This is the most permissive access level.
2. There are no restrictions on accessing public members, they are visible anywhere the class is visible
3. protected:
1. Access is limited to the containing class or types derived from the containing class.
2. which means you cannot create an instance of a class and have access to protected members.
   
4. internal:
1. Access is limited to the current assembly.
5. protected internal:
1. Access is limited to the current assembly or types derived from the containing class.

that last one is kinda tricky, it kinda "makes sense" that it allows access only in the current assembly and the current or derived classes, but the "or" there is what makes a difference, if you mark a property protected internal, it can actually be accessed outside the assembly. basically protected overrides internal and so I'm not even sure when it makes sense to use such modifier.

If you don't believe me go check it out your self:
Assembly1:
public class Test {
protected internal string prop1;
}

Assembly2://after adding a reference to Assembly 1
public class Test2: Test {
public string prop2;
public Test2() {
prop2 = prop1; //came from the other assembly's class protected internal member
}
}

if this is news for you, you might think, that's stupid, how do you get a property to be visible only the the current assembly, current class and derived classes?

quite simple, you mark your class as internal, so your class can only be accessed from the current assembly, and you mark your property as protected, thus giving access to the derived classes only in the current assembly:

internal class Test {
protected string prop1; //accessible only in this assembly in this class and derived classes
}

formatting 0, 1 as yes, no or whatever

This is something we all have done at some point, convert a 0,1 value into yes, no, on, off, etc string

turns out there is a function in the BCL that can do this, string.Format will do the job

Console.WriteLine(string.Format("{0:yes;;no}", 0)); //will output "no"
Console.WriteLine(string.Format("{0:yes;;no}", 1)); //will ouput "yes"


and just for the heck of it
Console.WriteLine(string.Format("{0:yes;;no}", -1)); //will output "-yes"

...one of those little thingies

function to generate random numbers, without repeating digits

The code for this article applies to C#

Reading blogs, I found this post (in italian), where Marco wants to write a function to generate random numbers with the following rules:

• 5 digits long
• result should be returned in a string
• all the digits should be different (on each result you can't have two of the same digit)

The function he came up with was the following:

private string GetRandom()
{
     Random rn = new Random();
     string resultnum=string.Empty;
     do
         {
              string a = rn.Next(0, 9).ToString();
              if (resultnum.Contains(a)!=true)
              resultnum = resultnum + a;
         }
      while (resultnum.Length<5);
      return resultnum;
}

I felt curious to see what areas I could improve, and I started writing a function to get the same result, but in a more optimized way

A couple things that jump out to my mind are:

1. string concatenation
2. the loop and comparing each time to see if the digit is in the result already

So I wanted to write a function that: would avoid concatenation and would execute the loop exactly 5 times, this is the result:

static char[] allNumbers = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
Random r = new Random();
public string GetRandom2() {
  
   char[] result = new char[5];
   int max = 9, pos=0;
   for (int i=1; i<6; i++) {
       pos = r.Next(0, max);
       result[i - 1] = allNumbers[pos];
   //*** swap positions
       allNumbers[pos] ^= allNumbers[max];
       allNumbers[max] ^= allNumbers[pos];
       allNumbers[pos] ^= allNumbers[max--];
   }
   return new string(result);
}

The technique I used was:

• Have a predefined array with all possible characters
• I have a variable max that I use to call the method Random.Next(0, max)
• the variable is decremented on each iteration
• I swap the digit from the result to the last position, with this I leave this digit out of the posibilities for the next call

The performance gain from these changes was minimal (3 ms per 1000 calls), then I thought about moving the Random variable declaration outside the function, so it could be reused between calls, this gave me the performance gain I was looking for, calling the function 10,000 times takes ~114ms using the old method and only ~6ms using the new one, that's where the problem was, the rest was pretty much insignificant =o(

I'm sure someone else can write a faster version, but I accomplished my goal =o)