Posted by Tony Rogerson on 02/02/06 23:03

> CREATE TABLE ReportRanges
> (range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);

This non-table is unusable. It has no key and cannot have a key because
range_name is NULLable.

> INSERT INTO ReportRanges
> VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');

Do you really think it a good idea to use the month name in the data like
this? What about other languages - French, Italian etc...

> 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;

You are still using the 89 syntax and should be using the more recent 92
syntax.

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
CROSS JOIN ReportRanges AS R
WHERE A.error_time BETWEEN R.start_date AND R.end_date
GROUP BY R.range_name;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1138908826.954654.5580@g47g2000cwa.googlegroups.com...
> 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');
>
> 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;
>
> You are making a classic newbie design flaw. You still think of
> programming with procedural code and functions, but not with relational
> operators.
>

[Back to original message]