Posted by --CELKO-- on 02/02/06 21:33

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.

• Next in forum: Re: Table variables
• Prev in forum: perf
• Thread view: Re: Monthly date range substitution

[Reply to this message]