(RESOLVED) Query has unexpected results.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

(RESOLVED) Query has unexpected results.

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 12:36:17

I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.

SELECT TOP 1000
	   o.[ID]
      ,o.[TimeOfOrder]
      ,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
  FULL JOIN SC.dbo.xmlAddressRead x
  ON o.ID= x.id	
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour,  -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1

Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?

SELECT TOP 1000
	   o.[ID]
      ,o.[TimeOfOrder]
      ,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
  FULL JOIN SC.dbo.xmlAddressRead x
  ON o.ID= x.id	
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour,  -48, GETDATE()))) < 48
  AND x.[StreeLine1]='599 Ships Landing Way'

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-12 : 12:49:28

Why did you use a FULL JOIN? How about just INNER JOIN? You'll need to explain in more detail...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-12 : 12:51:49

quote:
Originally posted by SergioM

I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1

Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'

-Sergio
I use Microsoft SQL 2008

What are the values of o.ID and o.TimeOfOrder in the second query? Are they all the same, or all nulls?

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 12:57:03

quote:
Originally posted by tkizer

Why did you use a FULL JOIN? How about just INNER JOIN? You'll need to explain in more detail...

Sure. In this case xmlAddressRead is just a view. The creator of the db left the address in XML form (in nText, no less!). The original is a column (ShippingAddress) that's within bvc_Order. So, the ID in this case comes from the same place. Any/all of the Joins would have exactly the same results because the ID is always present and equal to itself.

So there are 3 key fields
- TimeOfOrder tells me that the order came in the past 2 days
- StreetLine1 allows me to see if the order came from the same place
- ID tells me which order it is that I need to look at further (assuming it's caught by the first two)

I think that clears it all up. Let me know what you think.

-Sergio
I use Microsoft SQL 2008

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 12:59:03

quote:
Originally posted by James K
What are the values of o.ID and o.TimeOfOrder in the second query? Are they all the same, or all nulls?

They are all unique and exaclty the data I want to find. I've narrowed it down to 10 for simplicity

ID	TimeOfOrder	StreeLine1
1199251	2013-09-10 01:19:00.000	599 Ships Landing Way
1199253	2013-09-10 01:27:00.000	599 Ships Landing Way
1199254	2013-09-10 01:29:00.000	599 Ships Landing Way
1199255	2013-09-10 01:38:00.000	599 Ships Landing Way
1199256	2013-09-10 01:31:00.000	599 Ships Landing Way
1199257	2013-09-10 01:36:00.000	599 Ships Landing Way
1199258	2013-09-10 01:42:00.000	599 Ships Landing Way
1199259	2013-09-10 01:22:00.000	599 Ships Landing Way
1199260	2013-09-10 01:25:00.000	599 Ships Landing Way
1199261	2013-09-10 01:40:00.000	599 Ships Landing Way

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-12 : 12:59:04

You'll need to show us some sample data, showing the extra rows and showing what you actually want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 13:01:25

quote:
Originally posted by tkizer

You'll need to show us some sample data, showing the extra rows and showing what you actually want.

Sample data is above. I either want a StreetLine1 plus the count (if greater than 1) or the IDs & streetline1 (if greater than 1)

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-12 : 13:13:01

This is to be expected. Because you are grouping by ID and TimeOrder also, and since the IDs and TimeOrder seem to be unique for each row, the count will be 1. So your HAVING COUNT(x.[StreeLine1])>1 will eliminiate all rows. Even though you are requesting COUNT(x.[StreeLine1]), it still will be grouped by all three columns. What you perhaps want is the following, or something similar where you are not grouping by IDs and TimeOfOrder

SELECT TOP 1000
        MAX(o.[ID]) ,
        MAX(o.[TimeOfOrder]) ,
        x.[StreeLine1]
FROM    [SC].[dbo].[bvc_Order] o
        FULL JOIN SC.dbo.xmlAddressRead x ON o.ID = x.id
WHERE   DATEDIFF(HOUR, o.[TimeOfOrder], ( DATEADD(Hour, -48, GETDATE()) )) < 48
GROUP BY x.[StreeLine1]
HAVING  COUNT(*) > 1

IF that is not it, what is the output you are looking for?

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 13:26:28

quote:
Originally posted by James K

This is to be expected. Because you are grouping by ID and TimeOrder also, and since the IDs and TimeOrder seem to be unique for each row, the count will be 1. So your HAVING COUNT(x.[StreeLine1])>1 will eliminiate all rows. Even though you are requesting COUNT(x.[StreeLine1]), it still will be grouped by all three columns.

D'oh! That does make perfect sense though. Thank you

quote:

Originally posted by James K
What you perhaps want is the following, or something similar where you are not grouping by IDs and TimeOfOrder

SELECT TOP 1000
        MAX(o.[ID]) ,
        MAX(o.[TimeOfOrder]) ,
        x.[StreeLine1]
FROM    [SC].[dbo].[bvc_Order] o
        FULL JOIN SC.dbo.xmlAddressRead x ON o.ID = x.id
WHERE   DATEDIFF(HOUR, o.[TimeOfOrder], ( DATEADD(Hour, -48, GETDATE()) )) < 48
GROUP BY x.[StreeLine1]
HAVING  COUNT(*) > 1

This is exactly what I was trying to create! Thanks!

-Sergio
I use Microsoft SQL 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-12 : 13:48:53

Please note that MAX(ID) and MAX(TimeOfOrder) may not reference the same row!

WITH cte
AS (
	SELECT		o.ID,
			o.TimeOfOrder,
			x.StreeLine1,
			ROW_NUMBER() OVER (PARTITION BY x.StreeLine1 ORDER BY o.ID DESC) AS rn
	FROM		SC.dbo.bvc_Order AS o
        INNER JOIN	SC.dbo.xmlAddressRead AS x ON x.ID = o.ID
	WHERE		o.TimeOfOrder >= DATEADD(HOUR, -48, GETDATE())
)
SELECT	ID,
	TimeOfOrder,
	StreetLine1
FROM    cte
WHERE	rn = 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 13:59:16

quote:
Originally posted by SwePeso

Please note that MAX(ID) and MAX(TimeOfOrder) may not reference the same row!

Indeed! Thanks!

I just stripped it down to ID, street address & QTY (knowing that ID is only one of many).

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-12 : 16:04:53

So really the solution Mumu88 posted here should have worked for you all along - I thought that didn't give you what you wanted, and hence my confusion.

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 16:19:22

quote:
Originally posted by James K

So really the solution Mumu88 posted here should have worked for you all along - I thought that didn't give you what you wanted, and hence my confusion.

I see. His response was posted 3 minutes after the new thread was started. I received responses here quickly so I didn't go back to the old thread. I just checked it and it does the job :)

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources