Posted by Hugo Kornelis on 02/02/06 23:29

On 2 Feb 2006 11:33:47 -0800, --CELKO-- wrote:

>Create a report range table:
>
>CREATE TABLE ReportRanges
>(range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);
>
>INSERT INTO ReportRanges
>VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');
>
>INSERT INTO ReportRanges
>VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');
>
>etc.
>
>INSERT INTO ReportRanges
>VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');

Hi Joe,

1. Never omit the column list of an INSERT. THis, like SELECT *, is
extremely bad practice.

2. Please use unambiguous date formats:

* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt for date plus time
(with or without milliseconds).

3. Because SQL Server has datetime precision of 1/300 seecond, the
values for end_date will be rounded UP to 2005-02-01T00:00:00.000,
2005-03-01T00:00:00.000, and 2006-01-01T00:00:00.000. Not the values you
want with the query you propose....

>
>Now use it to drive all of your reports, so they will be consistent.
>
>SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
>WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;

..... however, this query is no good either. Never use BETWEEN for date
comparisons.

You should populate the Reportanges table as follows:

INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Jan', '20050101', '20050201');
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Feb', '20050201', '20050301');
(...)
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Total', '20050101', '20060101');

And change the query to

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
INNER JOIN ReportRanges AS R
ON A.error_time >= R.start_date
AND A.error_time < R.end_date
GROUP BY R.range_name;

(Note the use of greater _OR EQUAL_ for start_date, but lesser (and not
equal) for end_date).
This will always work - both for datetime and smalldatetime, and it will
continue to work if Microsoft ever decides to change the precision of
their datetime datatypes.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]