Monthly date range substitution

[Robert]

I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How can I script this so I can
programmatically perform the substitution in a loop. Thanx in advance.
select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Apr2004' and error_time < '1May2004'

[Erland Sommarskog]

Robert (ro***********@boeing.com) writes:

Maybe this is a lot easier to do than I first thought:

select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004' and error_time < '1Jan2005'
group by month (error_time)

This saves me a lot of work. Now, if I could figure out how to span years
and still group by months...

select convert(char(6), error_time, 112), count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004'
group by convert(char(6), error_time, 112)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Robert]

Thanx Erland, I am not worthy!!
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Robert (ro***********@boeing.com) writes:

Maybe this is a lot easier to do than I first thought:

select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004' and error_time < '1Jan2005'
group by month (error_time)

This saves me a lot of work. Now, if I could figure out how to span
years
and still group by months...

select convert(char(6), error_time, 112), count(*) from
application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004'
group by convert(char(6), error_time, 112)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Robert Klemme]

Robert wrote:

Thanx Erland, I am not worthy!!
You can as well use DATEPART to extract year and month from the timestamp
column.

robert

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Robert (ro***********@boeing.com) writes:

Maybe this is a lot easier to do than I first thought:

select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004' and error_time < '1Jan2005'
group by month (error_time)

This saves me a lot of work. Now, if I could figure out how to span
years
and still group by months...

select convert(char(6), error_time, 112), count(*) from
application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004'
group by convert(char(6), error_time, 112)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

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.

[Tony Rogerson]

> 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--" <jc*******@earthlink.net> wrote in message
news:11********************@g47g2000cwa.googlegrou ps.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.

[Hugo Kornelis]

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

[--CELKO--]

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

Good point. I keep forgetting that SQL Server does not follow the
FIPS-127 rules about keeping at least five decimal places of seconds
like other products. Generally goiing to 1/100 of a second has worked
for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
DATETIME).

If we had the OVERLAPS predicate, we could use that, but I prefer the
BETWEEN with adjusted times in the non-conformng SQLs I use. I can
move the code with a text change.

[Hugo Kornelis]

On 3 Feb 2006 17:38:40 -0800, --CELKO-- wrote:

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

Good point. I keep forgetting that SQL Server does not follow the
FIPS-127 rules about keeping at least five decimal places of seconds
like other products. Generally goiing to 1/100 of a second has worked
for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
DATETIME).

Hi Joe,

This will still bite you if smalldatetime is used. Or if ever an entry
makes it into the datebase with a 23:59:99.993 timestamp.

What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval
(with EndOfInterval actually being equal to the first fraction of a
second after the end of the interval, or the start of the next interval
if there are consecutive intervals)

AFAICT, this will work on ALL products, regardless of the precision of
the date and time datatypes used in the product. Am I wrong?

--
Hugo Kornelis, SQL Server MVP

[--CELKO--]

>> his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<

I never use SMALLDATETIME because it is soooo proprietary and does not
match the FIPS-127 requirements.

What is your objection to SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval <<

Mostly style and portable code. The BETWEEN predicate reads so much
better to a human. I would prefer OVERLAPS and some of Rick
Snodgrass's operators if i coudl get them.
FAICT, this will work on ALL products, regardless of the precision of the date and time datatypes used in the product. Am I wrong? <<

Yeah, yeah!! But I hate 5to split a single concept (between-ness) into
muliple predicates. I also hate a change of ORs when I can use IN(),
etc.
--

[Hugo Kornelis]

On 4 Feb 2006 14:22:07 -0800, --CELKO-- wrote:

his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<
I never use SMALLDATETIME because it is soooo proprietary and does not
match the FIPS-127 requirements.
Hi Joe,

So instead, you use DATETIME, which also is proprieatary, which also
doesn't match FIPS-127, and which takes twice the space. Good job. For a
table with mostly date columns, your performance will now be about twice
as slow.
What is your objection to

SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval <<

Mostly style and portable code.

Style, like beauty, is in the eye of the beholder. So I won't comment on
that.

But "portable code"??? <Cough!> Please tell me: what part of the code
above is not portable, and why???

The BETWEEN predicate reads so much
better to a human.

Maybe. But does '2006-02-28T23:59:59.997' also read better to a human
than '2006-03-01'???

SomeDate >= '2006-02-01'
AND SomeDate < '2006-03-01'

or

SomeDate BETWEEN '2006-02-01' AND '2006-02-28T23:59:59.997'

Are you really going to tell me that the latter reads better to a human?

--
Hugo Kornelis, SQL Server MVP