SQL Grouping by Year, Week, Month, Day

You've been tasked with writing a query that can slice records based on years, weeks, months and days, now what?


Well, I have a good option for you, the following can be written in a stored function, procedure or just plain SQL query. You can pass y,m,d,w,a (Year, Month, Day, Week, All) and it will group accordingly.


You'd be surprised how fast it is.

declare @GroupByDate char(1) = 'a'; --y,m,d,w,a

select (case when @GroupByDate = 'y' then CONVERT(varchar(4), DATEPART(yyyy, Timestamp))
    when @GroupByDate = 'm' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(mm, Timestamp)),2)
    when @GroupByDate = 'd' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(mm, Timestamp)),2) + '/' + RIGHT('00'+CONVERT(varchar(4),DATEPART(dd,Timestamp)),2)
    when @GroupByDate = 'w' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(wk, Timestamp)),2)
    when @GroupByDate = 'a' then 'All'
    end) as DateGroup,
  sum(Pages) as TotalPages,
  COUNT(*) as NumberOfVisitors  
from Visitors
group by (case when @GroupByDate = 'y' then CONVERT(varchar(4), DATEPART(yyyy, Timestamp))
    when @GroupByDate = 'm' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(mm, Timestamp)),2)
    when @GroupByDate = 'd' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(mm, Timestamp)),2) + '/' + RIGHT('00'+CONVERT(varchar(4),DATEPART(dd,Timestamp)),2)
    when @GroupByDate = 'w' then CONVERT(varchar(4),DATEPART(yyyy, Timestamp )) + '/' + RIGHT('00'+ CONVERT(varchar(4),DATEPART(wk, Timestamp)),2)
    when @GroupByDate = 'a' then 'All'
    end)

If you need a sequence from start to end (to join on for example, so you'll have a complete set), you can use it with a combination of date sequence generating function like this:

declare @fromdate datetime = '2008-01-01'
declare @todate datetime = '2011-01-01'
;
with Dates(MyDate)
AS
(
    Select @fromdate MyDate
    
    UNION ALL
    
    SELECT (MyDate+1) MyDate
    FROM Dates
    WHERE
        MyDate < @todate
)


SELECT MyDate FROM Dates 
OPTION(MAXRECURSION 0)

This is how I created the data for this example:

CREATE TABLE [dbo].[Visitors](
    [VisitId] [int] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Pages] [int] NOT NULL
)

The random date was taken from here and the numbers from here.

WITH Visits(RecordId) AS( 
    select (1) 
    UNION ALL
    SELECT RecordId+1 FROM Visits WHERE RecordId < 100 
)

insert into Visitors(Timestamp, Pages)
select 
 dateadd(month, -1 * abs(convert(varbinary, newid()) % (2 * 12)), getdate()) as timestamp
, ABS(CAST(NEWID() AS binary(6)) %10) + 1 as RandomNumber
from visits

Tags: