Help with query

mat41 · September 20th, 2010, 06:25 PM

I do not understand why i am getting the result difference when the date range in the following query is changed. Please note:

> the query works without issue. The results get passed into a graphing object and produces a bar graph.
> the date format is intentional dd/mm/yyyy

Here is the query with a date range (which is user defined) of one month, therefore it will only return one bar on the graph of course:

SELECT DATENAME(MONTH,osirSignalDateTime) AS Expr1 ,COUNT(distinct osirReport.osirId) AS Numincidents ,MONTH(osirSignalDateTime),YEAR(osirSignalDateTime )
FROM osirReport
WHERE (osirReport.osirSignalDateTime >= '8/1/2009 12:00:00 AM' AND osirReport.osirSignalDateTime <= '8/31/2009 12:00:00 AM')
AND (osirReport.osirTypeId IN (2))
GROUP BY DATENAME(MONTH,osirSignalDateTime),MONTH(osirSigna lDateTime),YEAR(osirSignalDateTime)
ORDER BY YEAR(osirSignalDateTime),MONTH(osirSignalDateTime) ,DATENAME(MONTH,osirSignalDateTime);

The result set it returns is:

August 83 8 2009

AND the same query with the â2009â year part of the date above bold and red changed to 2010 gives me:

August 85 8 2009
September 78 9 2009
October 75 10 2009
November 89 11 2009
December 52 12 2009
January 40 1 2010
February 118 2 2010
March 128 3 2010
April 106 4 2010
May 97 5 2010
June 91 6 2010
July 97 7 2010
August 89 8 2010

For the life of me I can not figure out why. Why does the total number of incidents change from 83 to 85 for august when asking for 12 months worth of data???????

NOTE - it does give me the same results if I remove the '12:00:00 AM' from the date values.

Completely confused......TYIA

Old Pedant · September 20th, 2010, 08:26 PM

Ready to kick yourself?

Try this query:

Code:

SELECT osirSignalDateTime FROM osirReport 
WHERE YEAR(osirSignalDateTime) = 2009
AND MONTH(osirSignalDateTime) = 8
WHERE DAY(osirSignalDateTime) = 31

I will bet you a hazelnut latte that you will get 2 records from that.

Try that before looking at the next post.

Old Pedant · September 20th, 2010, 08:34 PM

So the answer is easy.

I'll bet that those two records you got showed values such as

Code:

8/31/2009 4:30:22 AM
8/31/2009 8:22:19 PM

Right?

Okay. Now look at the condition in your query:

Code:

 AND osirReport.osirSignalDateTime <= '8/31/2009 12:00:00 AM')

Oh...and one more fact: The datetime values '8/31/2009' and '8/31/2009 12:00:00 AM' are 100% *IDENTICAL*. That is, when you omit the time from a date+time value, the time is automatically set to 0:00:00, midnight (which is of course the same as 12:00:00 AM).

So *NOW* try replacing that condition with

Code:

 AND osirReport.osirSignalDateTime < '9/1/2009')

mat41 · September 20th, 2010, 08:50 PM

hi there - two hazelnuts.....your on. That didnt run in Query Analyser. I did this to make it run:

SELECT osirSignalDateTime FROM osirReport
WHERE YEAR(osirSignalDateTime) = '2009'
AND MONTH(osirSignalDateTime) = '8'
AND DAY(osirSignalDateTime) = '31'

Should I have???? Anyhow I got four results. They are:

2009-08-31 04:02:00.000
2009-08-31 04:52:00.000
2009-08-31 08:45:00.000
2009-08-31 09:27:00.000

reading your second post now...

Old Pedant · September 20th, 2010, 08:57 PM

Quote:

Originally Posted by mat41

That didnt run in Query Analyser. I did this to make it run:

SELECT osirSignalDateTime FROM osirReport
WHERE YEAR(osirSignalDateTime) = '2009'
AND MONTH(osirSignalDateTime) = '8'
AND DAY(osirSignalDateTime) = '31'

????????????

I do *NOT* get that at all! The YEAR(), MONTH(), and DAY() functions in T-SQL return *INTEGERS* Why would you need to compare them to *strings*???

But whatever ...

Quote:

Anyhow I got four results.

I didn't look closely at the rest of your query. So I omitted this part of it:

Code:

AND (osirReport.osirTypeId IN (2))

I'll bet that two of those records have TypeId of 2 and two do not. So, yeah, I'll still take the latte. <grin/>

mat41 · September 20th, 2010, 09:08 PM

Yes of course no need to compare to strings. When that code didnt run the first thing I did was to convert them to strings. I then saw the extra Where which was the issue. I should have removed the single quotes after that......

Yes you are 100% correct including 'AND (osirReport.osirTypeId IN (2))' does give me two results.

;;;;;The datetime values '8/31/2009' and '8/31/2009 12:00:00 AM' are 100% *IDENTICAL*.
Wow I did not know that!! I assume the date with no time at all is the better option to use in future? This would mean its good practice to add one day to the user defined finish date EG:

"...WHERE (osirReport.osirSignalDateTime >= '" & request.form("fromDate") & "' AND osirReport.osirSignalDateTime < '" & (request.form("ToDate")+1) & "')..."

Old Pedant · September 20th, 2010, 09:20 PM

Quote:

AND osirReport.osirSignalDateTime < '" & (request.form("ToDate")+1) & "')..."

That's *exactly* what I do in my ASP code.

You can also do it in the T-SQL code, of course:

Code:

AND osirReport.osirSignalDateTime < DATEADD(d,1,'" & request.form("ToDate") & "')..."

In Access, you can use "... AND DATEVALUE(somedatetimefield) <= #8/31/2009#..."
And in MySQL, you can use " ... AND DATE(somedatetimefield) <= '2009-8-31' ..."

And, actually, in TSQL you *can* do

Code:

... AND CONVERT(DATETIME,CONVERT(VARCHAR,field,112),112) <= '8/31/2009' ...

But none of those are as efficient as what you used there.

Especially if the field in question is indexed.

Reason: When you use a function (or pair of functions) to convert a date+time to date-only, the DB engine has to hit every single record, doing the conversion.

With the code you used, if the field is indexed then the search can be done entirely in the index. In some cases this could offer orders of magnitude better performance. (Of course, if the field isn't indexed, the difference is minimal.)

mat41 · September 20th, 2010, 09:57 PM

As always your advice exceeds all expectations. Thankyou for your time!

S#&*T so my graphs have bee out for a fair while, wow its only just been picked up....

Have a nice day!