SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 (RESOLVED) Query has unexpected results.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  12:36:17  Show Profile  Reply with Quote
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

Edited by - SergioM on 09/12/2013 13:26:58

tkizer
Almighty SQL Goddess

USA
36832 Posts

Posted - 09/12/2013 :  12:49:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 09/12/2013 :  12:51:49  Show Profile  Reply with Quote
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?
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  12:57:03  Show Profile  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  12:59:03  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36832 Posts

Posted - 09/12/2013 :  12:59:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  13:01:25  Show Profile  Reply with Quote
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

Edited by - SergioM on 09/12/2013 13:04:34
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 09/12/2013 :  13:13:01  Show Profile  Reply with Quote
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?

Edited by - James K on 09/12/2013 13:14:01
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  13:26:28  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30207 Posts

Posted - 09/12/2013 :  13:48:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  13:59:16  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 09/12/2013 :  16:04:53  Show Profile  Reply with Quote
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.
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 09/12/2013 :  16:19:22  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000