Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 20th, 2010, 06:25 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Help with query

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
__________________
Wind is your friend
Matt
Reply With Quote
  #2 (permalink)  
Old September 20th, 2010, 08:26 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old September 20th, 2010, 08:34 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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')

Last edited by Old Pedant; September 20th, 2010 at 08:36 PM..
Reply With Quote
  #4 (permalink)  
Old September 20th, 2010, 08:50 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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...
__________________
Wind is your friend
Matt
Reply With Quote
  #5 (permalink)  
Old September 20th, 2010, 08:57 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Originally Posted by mat41 View Post
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/>

Last edited by Old Pedant; September 20th, 2010 at 08:59 PM..
Reply With Quote
  #6 (permalink)  
Old September 20th, 2010, 09:08 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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) & "')..."
__________________
Wind is your friend
Matt
Reply With Quote
  #7 (permalink)  
Old September 20th, 2010, 09:20 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.)
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
mat41 (September 20th, 2010)
  #8 (permalink)  
Old September 20th, 2010, 09:57 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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!
__________________
Wind is your friend
Matt
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query doesnt run when Select query does (In MS Access) rmccafferty SQL Language 3 February 11th, 2010 04:54 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM



All times are GMT -4. The time now is 01:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.