group / sum data based on period of time in SQL Server

Question

I have a customers table and I want to get the growth broken down by different periods say day, week, or year. To simplify let's assume there are only 2 columns:

CustomerID, CreatedOn

Trying to grasp this concept so I can apply it to several reports I want to make.

Answer 1

group by datepart(wk, CreatedOn),datepart(dd, CreatedOn),datepart(yy, CreatedOn) When doing day or week also use year otherwise it will overlap over several years

More info about datepart here: DATEPART (Transact-SQL)

Answer 2

There are also some shortcut functions for certain common date parts since you're using SQL Server. Some examples:

Day:

SELECT Day(CreatedOn), Count(CustomerID)
FROM MyTable
GROUP BY Day(CreatedOn)

Week:

SELECT Week(CreatedOn), Count(CustomerID)
FROM MyTable
GROUP BY Week(CreatedOn)

Year:

SELECT Year(CreatedOn), Count(CustomerID)
FROM MyTable
GROUP BY Year(CreatedOn)

See MSDN for full reference.

Update

Running total sample:

SELECT Day(CreatedOn), 
       Count(CustomerID), 
       (Select Count(CustomerID) 
        From MyTable mts 
        Where mts.CreatedOn <= CreatedOn) as RunningTotal
FROM MyTable mt
GROUP BY Day(CreatedOn)

Note: the performance of this won't be great, but will be as good as possible if CreatedOn is indexed. Normally nested selects are horrible...but given how the join works in a running total situation, you're hitting the index the same way anyway, so this in this case, it's just more readable.

If it gets to be something more complex, look at a conditional join with a sub select. If a procedure is an option, you can up the performance even more...but if you're not dealing with a ton of data, it won't matter much either way.