What is the best way to count rows from a table with a datetime column where the counts need grouping by specified date periods from another table.

SQL 2000 compatible solutions required.
Consider :

CREATE TABLE [dbo].[Periods] (
      [PeriodID] [int] IDENTITY (1, 1) NOT NULL ,
      [StartDate] [datetime] NOT NULL ,
      [EndDate] [datetime] NOT NULL

CREATE TABLE [dbo].[Events] (
      [EventID] [int] IDENTITY (1, 1) NOT NULL ,
      [EventDate] [datetime] NOT NULL ,
      [EventTypeID] [int] NOT NULL

I would like to return rows such that the eventTypeID occurences are counted into the corresponding Periods where the EventDate falls between the startdate/enddate and the EventTypeIDs are grouped upon.
Assumptions : I only have 5 different EventTypeIDs so crosstabbing these in some way is ok since they will not change.
The startdate-enddate rows form Periods will not be expected to cross one another so your solution does not need to consider this possibility.  I am not interested in Events that fall outside of the periods.

Performance is important in that the Events table holds millions of records although the Periods table will contain a small number of rows of short date ranges with which to provide results against.

If this isn't clear enough l me know and I'll do some examples.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy Knight, MCMPrincipal ConsultantCommented:
This should work.  As long as there is an index on EvenDate in the Events table it should perform just fine.

	,COUNT(*) AS Events
FROM dbo.Periods p
	LEFT JOIN dbo.[Events] e ON e.EventDate BETWEEN p.StartDate AND p.EndDate

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
be very very very careful about using between here. please see: "Beware of Between"

If your events table has time references other than midnight you could easily end-up with gaps or overlaps. Plus how you have actually set-up the periods table, particularly the period end dates, will all affect the best way to get accurate date ranges.

Does Events.EventDate have times like 12:34:56 ?

Do your periods dates look like this?
July 2014 = 2014-07-01 & 2014-07-31 -- potential 24 hour gap, be very wary of that and of datetime rounding (see below)

or like this?
July 2014 = 2014-07-01 & 2014-08-01 -- use less than, not between

--- --- --- --- ---
Be careful about rounding errors. Suppose you're using the DATETIME data type for your column, and your data has both date and time values that aren't necessarily midnight. You want to filter a period of date and time data such as January 2012. Some people use the following filter form:

WHERE col BETWEEN '20120101' AND '20120131 23:59:59.999'
The problem is that 999 as the millisecond unit isn't a multiplication of the precision unit for DATETIME, which is three and a third milliseconds. Therefore, the value gets rounded to the next midnight, and your range might end up including rows it isn't supposed to include. Some people "fix" this problem by using 997 in the millisecond unit, but what if at some point in the future you alter the type to one with finer precision? Therefore, the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
dgloverukAuthor Commented:
Thank you Randy and Paul,
I have made some adjustments such that the Periods are stored always with start date time 00:00:00 time and end dates 23:59:59.  The events are created only the day so there isn't any real scope for events to be created not between these times (i.e the limits of the day).  If by chance somebody is logged in working from home and does, it will not be a major problem if the event is not included since this is employee performance data for the most part.
Thank you Randy for the sql example, it is working very nicely.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.