[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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.
2 Solutions
Randy Knight, MCMCommented:
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

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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now