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.

Posted on 2014-08-30
Last Modified: 2014-09-01
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.
Question by:dgloveruk
    LVL 4

    Accepted Solution

    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
    GROUP BY p.PeriodID

    Open in new window

    LVL 47

    Assisted Solution

    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

    Author Closing Comment

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now