Group by Month (and other time periods)

Mon Sep 10, 2007 by Jeff Smith in t-sql, group-by, datetime-data

When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs.

Techniques to Avoid

GROUP BY Month(SomeDate) – or – GROUP BY DatePart(month, SomeDate)

Unless you are constraining the data so that it covers only 1 year, and it will always cover exactly 1 year, you should never just group by the Month of a date since it only returns a number from 1-12, not an actual "month". Thus, data for January 2006 and January 2007 will be consolidated together into "month 1", which is probably not what you want. Overall, I recommend avoiding grouping only on a month number for these reasons.
GROUP BY MonthName(SomeDate)

This has the same problems as grouping on a month number when your data covers more than one year. In addition, you have issues with sorting since "December" will sort before "January". Finally, you are forcing presentation code into your database by returning month names in SQL – you are much better off letting your front end client decide if it wants to display full month names, or abbreviations, or month numbers, rather than forcing a specific format upon them. I highly recommend to avoid using this technique as well.
GROUP BY SUBSTRING(CONVERT(varchar(20), SomeDate, xxx), xxx, xxx)

You can always convert your dates to a string format, then grab some part of that formatted string that contains the month and the year and group on that, right? Yes, you could – but don't. There is no need to introduce formatting and string conversions into your SQL code; keep it short and simple and focus on working with clean, raw data. When you start working with formatted strings, you end up with sorting and comparing issues, you can't do any kind of calculations or "date math" on your data, and you are no longer returning values that your front end clients can work with natively as dates. Avoid converting and parsing strings in SQL unless you have no other choice – and you have lots of other choices in this case.

Techniques to Use
GROUP BY Year(SomeDate), Month(SomeDate)

If you add a Year into your GROUP BY clause, now you can safely group on a month number as well, and you are guaranteed that your results will never combine multiple years into the same month. Use this if you'd like to keep your month and year values in separate columns. Your front end client can easily format these values as "MM-YYYY" or whatever is needed – don't try to convert and combine these two values together in SQL, there is simply no need to. Keep your data raw and simple and efficient; remember that it is always shorter and easier to combine and format data than it is to parse and separate data. It is also nice to have the Month column separate from the Year column if you are ultimately going to pivot or cross tab your results, so this approach can be very useful if it meets your needs.
GROUP BY dateadd(month, datediff(month, 0, SomeDate),0) <– Recommended

This technique will "round" your date to the first day of the month; thus, if you GROUP on those dates, you are grouping on months. This is nice because it combines the year and month together into one column for easy sorting and comparing and joining, if necessary. It also keeps your data as a true DATETIME value so that you can format it any way you'd like at your client, without the need to concatenate or convert to a DATETIME to present the months like "Jan-2007" or whatever you need. I strongly recommend this technique for all of these reasons and think you will find that overall it is the most flexible and easiest to work with.
GROUP BY datediff(month,0,SomeDate)

Ah – now here's an abstract one. This formula just returns the "month offset from the base date", or the number number of months between the "base date" of 0 and the transaction's date, as an integer. If you look at the values returned, they will just be numbers that don't have any meaning. But if you group and sort on these numbers, you will be grouping and sorting by month. At the end of the day, if you take this month offset and add it to the "base date" of 0, you'll get back a DATETIME value as of the first day of the corresponding month.

For example:

SELECT dateadd(month, x.MonthOffset,0) as [Month], SUM(x.val) as Total
FROM
(
SELECT datediff(month,0, trans.trandate) as MonthOffset, trans.val
FROM trans
) x
GROUP BY MonthOffset
ORDER BY MonthOffset

Notice that we are grouping and ordering by our MonthOffset calculation, but in the end we simply add that offset to the "base date" of 0 to get the actual DATETIME value of the first day of the corresponding month.

This technique is actually very fast and efficient, and works well if you need to join multiple derived tables that have all been summarized by month, since you are just joining on integers. But, it can be tricky to work with since it is pretty abstract. If none of this makes sense, don't worry -- just avoid this technique unless you feel it will be useful for you.

Grouping by Other Time Periods

Grouping on Years, Weeks or Quarters follows the same basic idea: simply "round" your DateTime to the resolution that you need, and group on either the resulting DateTime value or by the number of units since the base date. For example, to group by quarter, you can write:

GROUP BY dateadd(quarter, datediff(quarter, 0, SomeDate),0)

That returns the starting date of each quarter; for example, 10/12/2007 will return 9/1/2007. To get the ending date of the quarter, you can use:

dateadd(quarter, datediff(quarter, 0, SomeDate) + 1, 0) -1

As with months, you could just group on the number of quarters since the "base date" as well:

GROUP BY datediff(quarter,0,SomeDate)

Overall, the exact same concepts apply.

Summary

The method to choose for any situation ultimately depends on what you need, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs.

Legacy Comments


Dave May 2007-09-10	re: Grouping by Month In another useful variation of technique 5, you can use integer rounding to produce the effect of grouping by an arbitrary quantity of a given interval. For example, I use the following to group by 15 minute intervals: GROUP BY dateadd(minute, datediff(minute, 0, SomeDate) / 15 * 15, 0) Dave.

Jeff 2007-09-10	re: Grouping by Month Dave -- that's a great point, thank you for brining it up.

David 2007-09-11	re: Grouping by Month Better than all of these techniques is to use a Calendar table, containing all Dates required by the app, and in subsequent columns, all month, year, quarter, fiscal, day, working day values for each date. I use a char(6) column called YearMonth, which contains things like 200709 for Sept, 2007. Inner join your dates onto the Calendar table and group by YearMonth. Staggeringly better performance than calculating in real time with date functions.

Jeff 2007-09-11	re: Grouping by Month David -- Calendar tables are great, I agree that they can be useful, but don't use a CHAR(6) column for your YEARMONTH -- use a datetime! Again, if you use DateTime, you can do date math easily, and let your front end format that YEARMONTH value anyway it wants without the need to convert it to a datetime somehow first. Also, the Calendar table does you no good if the datetime values you are summarizing don't always have a time of midnight ... unless you want an entry for every possible date and time you'd ever store in your calendar table. that would get pretty big pretty quickly!

Flaver 2007-09-11	re: Group by Month Man you don't even know how long I've waited for this since disabling my own Movable Type widget (that doesn't work since Haloscan bypasses that code). THANK YOU!

Robert 2007-09-11	re: Group by Month A mortgage is usually counted from the first of the month (if that is the start date) or the first of the next month if it is any later day of the month, e.g. 9/1/2007 is 9/1/2007 but 9/2/2007 is 10/1/2007. I wonder if you could extend this to do that.

Jeff 2007-09-12	re: Group by Month Robert -- in that case, I'd definitely go with a calendar table, or a table of Periods or Months or whatever you need, with each start date and end date specified. I suggest not trying to use a formula to determine stuff like this, since holidays and weekends and other things come into play. Just create a table once and join to it: create table Months ( Month datetime primary key not null, StartDate datetime not null, EndDate datetime not null ) and then something like this: select Months.Month, sum(yourdata.val) as total from Months inner join yourdata on yourdata.date between Months.StartDate and Months.EndDate group by Months.Month Make that a LEFT OUTER JOIN to show even months with no transactions -- a very common request that can be tricky to do if you don't have a simple Months table to help you out!

Jon 2007-09-12	re: Group by Month Jeff, Excellent post as usual. Most of our internal reports have to deal with month data as well as years (being that we sometimes dont care to see last years data). I've had to do a lot of playing around with GROUP BYs, this would of been helpful years ago :-). Definately another good post by you. Jon

David 2007-09-14	re: Group by Month Jeff, in a general ledser journal of 8 millioin rows doing date calculations on PostDate to see if the journal in a particular month is orders of magnitude slower than joining to a Calendar table and testing if YearMonth = '200709' for example.

Jeff 2007-09-15	re: Group by Month David -- we are talking about grouping transactions, not filtering. You still should use efficient WHERE clauses without wrapping your date columns in expressions to filter your data. You should never write: select * from yourtable where <some month calculation on transdate> = <somemonth> Never did I recommend or demonstrate doing something that.

Dan 2007-09-17	re: Group by Month I tend to do the following GROUP BY CONVERT(CHAR(6), DateField, 112) which gives 200701 200702 ...

Jeff 2007-09-17	re: Group by Month Hi Dan -- be sure to read point #3 if you haven't already. You should not be doing converting or string manipulation on dates if you don't need to.

Rick 2007-09-22	re: Group by Month This is a good post. But I have a bigger challenge. I have multiple date columns which I want to group by Month and Year for in just one view. This is much complex then just doing a GROUP BY DATEPART(Mm, datecolumn field), DATEPART(Yy, datecolumn field) I have something similar to a Calendar table Any ideas??

Joe Celko 2007-09-22	re: Group by Month I prefer to use an auxiliary table with date ranges in it: CREATE TABLE ReportPeriods (period_name CHAR(15) NOT NULL PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL, CHECK (start_date <= end_date)); This lets me set up overlapping and nested report periods and does not depend on a proprietary temporal function library. It also guarantees that all reports use the same definitions.

Jeff 2007-09-22	re: Group by Month Thanks the feedback, Joe, I completely agree -- if you have the ability to create a calendar table and monthly reporting is a common requirement, it is absolutely the way to go, as I wrote when responding to Robert. I would not use a CHAR(15), however, to label each month; again, I would use a DATETIME (or DATE, if available) to allow for clients and reporting tools to format that month ("Jan-2005","2005-01","January 2005", etc) any way they want without the need for string parsing and conversions.

Jim 2007-10-01	re: Group by Month Hello, My problem is that I want to group by month but we use fiscal year. So I need to get number of transactions done in this sequence (for fiscal year 2007): 09/2006 - 10/2006 - 11/2006 /12/2006 - 01/2007 - 02/2007 - ... - 08/2007. On top of this, the user selects which fiscal year report they want. What is the best way to approch this?

Jeff 2007-10-01	re: Group by Month Jim -- if you read the comments, you'll see the calendar/period table suggestion which would work fine for you. Also, there is no difference in the sorting/grouping of your fiscal year versus a calendar year, so everything I've written above still applies to you ... my advice: experiment! Write some sql, use some test data, read up on date functions, play with a calendar table, etc.

Paula 2007-10-12	Group by Quarters I'm reading this and I think you may know how to solve my problem. I need to add some totals by Quarters, of course with different years too. something like this: num total date 1 10 07/17/07 2 5 08/19/07 3 10 10/01/07 4 10 01/05/07 5 5 06/01/07 Quarter Total Jan-Mar 10 Apr-Jun 5 Jul-Sep 15 Oct-Dec 10

Jeff 2007-10-12	re: Group by Month Hi Paula -- all you need is: GROUP BY dateadd(quarter, datediff(quarter, 0, SomeDate),0) All the same concepts and ideas and things to watch out for apply as grouping by month, only instead of using month we use quarter. Also, to get the end date for the quarter, just use: select dateadd(quarter, datediff(quarter, 0, SomeDate)+1,0) -1 for example, if SomeDate is 10/12/2007, that will return 12/31/2007.

Imran 2007-11-02	re: Group by Month (and other time periods) need help of quarter wise data

Sucharitha 2007-11-18	re: Group by Month (and other time periods) Hello All, I am new to Programming in SQL. Need your suggestions. Here is my requirement: Note: I have no front end scripting. Anything needs to be done at the SQL end pnly. Below is the sample data CREATE TABLE Test(TransactionDate DateTime, A INT, B INT) I insert some values like below into the table Test TransactionDate A B ----------------------------------------------------------- 2007-01-21 16:01:00.000 61 23 2007-01-12 07:03:00.000 94 31 2007-01-24 00:13:00.000 71 26 2007-02-08 02:43:00.000 89 27 2007-02-09 20:41:00.000 53 21 2007-02-09 18:43:00.000 58 32 2007-02-22 11:00:00.000 81 19 2007-02-14 07:39:00.000 50 25 2007-03-15 00:17:00.000 45 23 2007-03-03 22:07:00.000 41 18 2007-03-15 09:17:00.000 85 30 2007-03-07 15:51:00.000 34 13 2007-03-07 21:05:00.000 53 18 Out of the above I would want to have the result of a select statement as below ORDER BY MontName Column MonthName Averageof_A Averageof_B ----------------------------------------------------------------- Jan 63 21 Feb 55 20 Mar 61 20 Many Thanks in Advance, Sucharitha

williams 2008-01-06	Displaying date in quarter wise I need to display sum of outstanding balances by Quarterswise, Quarter is Jan-Mar 1st quarter Apr-Jun 2nd quarter Jul-Sep 3rd quarter Oct-Dec 4th quarter i want to display outstanding balance in quarterly ex: if i give today date 01/06/2008 means it is falling in first quarter so the sum of balances from jan 1 st to jan 6th must be displayed if i am giving 05/05/2008 it is falling in second quarter so the second quarter starts at 1st april so from first april to 05/05/2008 should be displayed .

Jeff 2008-01-06	re: Group by Month (and other time periods) williams -- read the article.

Neil Franken 2008-01-17	re: Group by Month (and other time periods) Thanks for this article. The logic is clean and the recommended tecnique is extremely powerfull. I recently had to draw reports after a long time of focussing on optimization of some legacy code(6 months) and got rather stuck with the grouping by date thing. Your solution is the best way!

Oscar 2008-02-01	re: Group by Month (and other time periods) Hi Jeff, first off - great article! You wrote "You still should use efficient WHERE clauses without wrapping your date columns in expressions to filter your data." I am grouping hours to days, days to months, days to quarters/years and so forth. And many times I need to group a certain date in the WHERE clause and compare it shame on me. After reading your article I know I've got some changes to do. How can I avoid expressions on the date columns in the WHERE clause? I don't see how a calendar table could help since we work with timespans several years back and in the future. Thanks!

Jeff 2008-02-29	re: Group by Month (and other time periods) I hope you can help me with the problem I've been having. I've tried every combination that I can think of to group a sum of values based on "Production Day" instead of a standard calendar day. Our facility's day goes from 06:00am to 06:00am. Using GROUP BY DATEPART(day,tblDeviceData.TimeStamp) works like it should but returns the sum of records from 12:00am to 12:00am. I've tried using GROUP BY DATEPART(day,tblDeviceData.TimeStamp) + (6/24) thinking that it would add a 6 hour offset to the group but it just returns an error. I sure would like to know what I need to use to return the correct group. Right now we are using a query for each day and looping to generate a report for every day in the month but it is very slow.

Jeff 2008-02-29	re: Group by Month (and other time periods) Jeff -- use the DateAdd function to substract 6 hours from the date before rounding it to the nearest day. And you should not use DatePart(day) unless you are only selecting at most one month at a time.

Chris 2008-04-07	re: Group by Month (and other time periods) I have tried this and keep getting "my date" is invvalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I am new to SQL (how many times have you heard that one!) Does this function require the "date" field to be declare as a certain date type? thanks

Mark 2008-06-02	re: Group by Month (and other time periods) I'm so close! I've tried all the things in this article, but can't seem to do what I want to do. I've been tearing my hair out for days! Here's what I'm trying to do. Basically I need a sql procedure that looks at an invoicing table that totals amounts by month/year and quarter at the same time. Here's how my table looks: Project ID Date Amount 1 3/11/08 10.00 1 4/18/08 10.00 1 6/22/08 10.00 2 3/01/08 10.00 2 9/15/08 10.00 I would like the output to have dynamic columns, so an output may look like: Project ID Jan'08 Feb'08 Mar'08 Q1'08 Apr'08 May'08 Jun'08 Q2'08 Jul'08 Aug'08 Sep'08 Q3'08 1 0.00 0.00 10.00 10.00 10.00 0.00 10.00 20.00 0.00 0.00 0.00 0.00 2 0.00 0.00 10.00 10.00 0.00 0.00 0.00 0.00 0.00 0.00 10.00 10.00 I would like the query to know if there was no value in Jan & Feb'08, but still list all the months in Q1. I'm not opposed to using a calendar table, but would like to try to avoid it if possible. Any help would be greatly appreciated!

jeff 2008-07-17	re: Group by Month (and other time periods) Mark -- answered here.

Nikki 2008-08-21	re: Group by Month (and other time periods) Great post..Got what I needed..Thank You...:)

Ryan Florence 2008-09-29	re: Group by Month (and other time periods) Great stuff, I got what I needed. However, I can't get #5 to work, I keep getting a syntax error. (i'm a designer trying to be a programmer, so I'm pretty limited in SQL) SELECT monthname(payments.date), sum(payments.amount) FROM payments GROUP BY dateadd(month, datediff(month, 0, payments.date),0) What's wrong with that? I also thought I could do this for the select as well SELECT dateadd(month, datediff(month, 0, payments.date),0), sum(payments.amount) Not a huge deal since I'm able to pull the data I needed (though I ended up using 3 queries, I'm certain I can use #5 to only use 1) Thanks!

Maksuda 2008-10-03	re: Group by Month (and other time periods) I am new in Sql Server. I read the article written here and it's really helpful. But I have some confusion about Sum-Group by: In my table I have a date field and event field which I need to sum by each quarter of year, I have several years data so it's important in my query data should not sum up with other year's quarter. I mean 2007-Q1 data should not sum up with 2008-Q1 data. So I need Quarter-Year in group by. Which query is perfect for my requirement? Thanks in advance Maksuda...

shax 2008-10-17	re: Group by Month (and other time periods) Thank you very much

ally_ratna 2008-10-17	re: Group by Month (and other time periods) A really great article. I have a problem. how can i extract data based on different date eg: date1 date2 total ------- ------- ------- 15 jan 2007 15 feb 2007 10 15 feb 2007 15 march 2007 15 15 march 2007 15 apr 2007 5 these data is a daily transaction and i need to grab monthly report based on the daily transaction to get result like above PLEAZZZZE help me. thank you in advance ^_^

vidhya 2008-10-23	re: Group by Month (and other time periods) how to display a record one hour o nce using sql query 2005

Danish Khan 2009-01-05	re: Group by Month (and other time periods) 1 2059 Alvira DCosta 2008-12-01 12:04:03.000 2 2059 Alvira DCosta 2008-12-01 12:04:36.000 3 2059 Alvira DCosta 2008-11-21 14:07:48.000 4 2059 Alvira DCosta 2008-11-22 15:13:30.000 The above is my data. It is generated by the machine for the nos of time the door is opened by a employee. Now my problem is i want the number of days in a month the door was open by the employee. The Output should be Emp_CD Mth no of days 2059 12 1 2059 11 2

M Kashif Ikram 2009-02-19	re: Group by Month (and other time periods) Excellent Article, I was searching for this kind of help for so many hours and you finally solved my problem in a very useful article. GOD BLESS YOU

Stumped SQL Novice 2009-03-21	re: Group by Month (and other time periods) I need for this query to "roll" from month to month. I've tried some of your suggestions, but I've not been successful. Since I don't know what else to do, I am just using literal >= and <= dates for now. What I need is for the query to return only Feb data as long as it's March and then only March data as the calender changes to April. Here is the query I'm using: SELECT AGT, COUNT(JOBS) AS 1mthPrior FROM dbo.ALL_JOBS WHERE (SCHED_DTE >= CONVERT(DATETIME, '2009-02-01 00:00:00', 102)) AND (SCHED_DTE <= CONVERT(DATETIME, '2009-02-28 00:00:00', 102)) GROUP BY AGT I am a novice, so any and all help is greatly appreciated.

Waseem Raja 2009-04-10	re: Group by Month (and other time periods) This group by months number code is really helpful for me. Great work n Thanks

saranya 2009-07-08	re: Group by Month (and other time periods) From a Date given as param, it should display: - Yesterday 9 pm - The same day 8 am Sample: SP DisplayMyDate(‘2009-07-07’) then it must display: - 2009-07-06 9 pm - 2009-07-07 8 am some one help me find the soln.

mysql is better 2009-07-12	re: Group by Month (and other time periods) Stupid, as all that's coming from Microsoft ! MS=SQL SUCKS ! Try mySQL, idiots.

kish 2009-07-15	Need Query to get Count of records month and Year wise Hi, I have a column named posted date, Depending on the above column I need to count No of records per year and month wise asc. I done by using group by function but am not able to get jan --- desc order. Result ie:MonthName,Year,Count columns

vicky 2009-07-30	Need last total of the month hi, i have one date field and one product field. i need the last amount entry ...if i have a amount entry on the last day of the month or before or on 10 of the next month i have to show that value in last month. e.g for product A i have entry 100 on 26 jan and 450 on 5 feb so i have to show 450 for the month of jan for product A.

MrKoty 2009-09-11	re: Group by Month (and other time periods) Thanks for the tutorial. Excellent help to accomplish a basic task. Saved me a lot of time.

Ian 2009-11-10	re: Group bi-weekly I still have yet to find an easy way to group bi-weekly. Any ideas?

Ian 2009-11-10	re: Group by Month (and other time periods) I wrote one for now. . . not pretty but it works. TO_DATE('04.05.2008','mm-dd-YYYY') + ((floor((days("DATE FIELD) - days(TO_DATE('04.05.2008','mm-dd-YYYY')))/14)+1) *14) DAYS Like I said. Not pretty, but it gives me a group for bi-weekly pay period of the specific date in DB2

Alain 2009-12-03	re: Group by Month (and other time periods) Hi there, Now, what if I need to group by let's say 7 days from a startdate that may not correspond to the beginning of a week? For exemple, if my "startdate" is Wednesday, Jan 06, I want to calculate the number of events till the "enddate" Monday, March 23, but for each period of 7 days between "startdate" and "enddate"? Any ideas??? Alain P.S.: MS SQL Server 2005

angela 2009-12-03	re: Group by Month (and other time periods) I have two charts. Each chart displays the current month and the last 11 months. (Rolling 12 months.) The second chart needs to have not just the totals for that month but the totals for the month + the past 11. Basically is is double rolling. Each point on the line graph has dollars for the last 12 and the chart itself is dynamic when pulling in the months to display on the axis. The trouble I am having is that I am determined to do all of this with one select stmt. I have tried various date parameters but no matter what I do, when I group in the month for the axis it reduces the dollar value to that month only. I need the same results to come through but with a higher dollar value: Value Units Month Manufacturer Year 1981110 38 2 All 2008 2976187 75 3 All 2008 5621069 110 4 All 2008 5551028 74 5 All 2008 2385059 54 6 All 2008 4882640 63 7 All 2008 3004074 52 8 All 2008 3096454 58 9 All 2008 4245657 64 10 All 2008 1475482 48 11 All 2008 3891363 74 12 All 2008 1422636 28 1 All 2009 I am trying to use this and I get the same results with the inner sum as I do with only one query. The date grouping is screwing it up but I cannot think of a simple way to do it without another stmt.

angela 2009-12-03	re: Group by Month (and other time periods) My query didn't come through.... SELECT SUM(Value) AS Value, SUM(Unit) AS Units, DATEPART(month, someDate) AS Month, 'All' as Manufacturer, DATEPART(year, someDate) AS Year FROM sometable inner join sometableloclink on sometableloclink.sometableid = sometable.sometableid inner join sometable2 on sometable2.sometableid = sometable.sometableid WHERE (IsNew = 'N') and someDate >= @TempSdate and somedate <= @somedate AND sometable2.DealerShipId=@dealershipId and sometableLocLink.districtId = @districtId and sometableLocLink.industryId = @industryid GROUP BY DATEPART(year, someDate), DATEPART(month, someDate) ORDER BY Year ASC, Month ASC

angela 2009-12-03	re: Group by Month (and other time periods) Sorry for spamming this!! that one had the wrong date info on the where line SELECT SUM(Value) AS Value, SUM(Unit) AS Units, DATEPART(month, someDate) AS Month, 'All' as Manufacturer, DATEPART(year, someDate) AS Year FROM sometable inner join sometableloclink on sometableloclink.sometableid = sometable.sometableid inner join sometable2 on sometable2.sometableid = sometable.sometableid WHERE (IsNew = 'N') and AND (DATEPART(year, someDate)=@year AND DATEPART(month, someDate)<=@month or DATEPART(year, someDate)+1=@year AND DATEPART(month, someDate)>@month) AND sometable2.DealerShipId=@dealershipId and sometableLocLink.districtId = @districtId and sometableLocLink.industryId = @industryid GROUP BY DATEPART(year, someDate), DATEPART(month, someDate) ORDER BY Year ASC, Month ASC

Dave 2009-12-16	re: Group by Month (and other time periods) Thanks a lot for the concise article. Not sure how this fits in performance-wise, but I had alot of speed success using an Access Front end and SQL Server backend using Year(SomeDate)*100+Month(SomeDate).

Brandon 2010-03-04	re: Group by Month (and other time periods) Just want to say thanks for this article. I took over a project where the previous programmer did exactly what you said not to do and one of our customers was trying to figure out why their register productivity report didn't make sense when they selected a time period greater than a day (it was grouped by the hour). I saw right away what the problem was but as I've never needed to group by time periods before I wanted to see what the best methods were and this is by far the best answer I've found on the net. Thanks!

Stephanie 2010-05-04	HELP! group by first day of bi-weekly period This is a very useful article. Thank you! I am now trying to not only group bi-weekly, but display the first day of the bi-weekly period. Instead of pay period 1,2,3. I want it to be 12/13/2009, 12/27/2009, etc.. Anyone? Thanks in advance!

Another Dave 2010-06-07	re: Group by Month (and other time periods) Thanks Jeff. Well presented + very useful article.

bob 2010-07-10	re: Group by Month (and other time periods) Hope someone can help, looking for a query that will give me the # of payperiods between a start date (dcd) and the current date with the payperids ending on the 15th and the last day of every month. Any help is appreciated. Thanks

VM Ware 2010-08-16	re: Group by Month (and other time periods) Wow! There are so many methods here to make your data sortable in various options. I have a large inventory and would definitely try various methods out of this to see the stock in my inventory. I think year to year option will definitely is more easy. where you need to check from any particular month of year to the same month of next year.

hilongos 2010-08-19	re: Group by Month (and other time periods) Very carefully filtered.. more example codes please