Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Inconsistent Query Returns

Unanswered Inconsistent Query Returns

  • Friday, February 15, 2013 11:11 PM
     
     
    Sign In to Vote
    0

    Hello!

    I've mostly spent my years using MySQL and recently making a switch to utilizing SQL 2008 more frequently. So, bear with me on this issue.

    I have a table with multiple columns that store various integers with dates. I wanted to select 3 rows in particular within the past 3 days. When I select those dates to return their values using the simple point and click query features within the management tool, it returns one set of values. However, if I select them separately, it returns a different set of values. 

    From my SQL experience, the query itself is simply selecting them with no mathmatical functions or anything being inserted into the query. It's simply using SHORTDATE in one query between a date range where the other is just selecting one date. Yet, the values are returning something different in terms of being off by 10 or 20, but nothing major.

    Any idea why the returns from simple select statements are being inconsistent?


     

All Replies

  • Friday, February 15, 2013 11:47 PM
     
     
    Sign In to Vote
    0

    A) you don't say what set of values is correct?

    B) what does being of by 10 or 20 mean?  Are you talking about rows?

    c) you did not post your select statements.....please post.

     
  • Friday, February 15, 2013 11:56 PM
     
     
    Sign In to Vote
    0

    A) you don't say what set of values is correct?

    B) what does being of by 10 or 20 mean?  Are you talking about rows?

    c) you did not post your select statements.....please post.

    A) The single select of just one date is right. The range select is wrong.

    B) The numbers only represent one field where it's being inconsistent with one another. All other fields are correct.

    C) Will have to get back to that, but it's just a simple select statement of date, rows and the tables. I will post shortly.

     
  • Sunday, February 17, 2013 9:17 PM
     
     
    Sign In to Vote
    0

    I am not sure if I understand correctly.

    Do you have more than 3 rows that match the date range? If so, then if you are using TOP 3 or SET ROWCOUNT 3 to limit the number of results, then you must use ORDER BY with an expression that makes each row unique if you want to get the same result for every time you run the query.

    When you execute SQL, you should get results that match your query. If you only select a subset (as could be the case if you use TOP), then the optimizer makes no effort to return a consistent set. That is, unless criteria are provided in the query (with the ORDER BY clause).

    Gert-Jan

     
  • Tuesday, February 19, 2013 3:40 PM
     
      Has Code
    Sign In to Vote
    0

    Ahh, good point. I will check for matching dates. That could be the reason.

    Here is the base select query.

    SELECT [ShortDate]
      ,[ActivityNm]
      ,[Market]
  FROM [example_data].[dbo].[t_CSV]
  where ClientCode = 'Test' and ShortDate between '12/31/2012' and '2/1/2013'

    This is the base of the query. So, some columns seem to be inconsistent when selected with the date range above, but return differently when just selected separately. It's rather strange, but it could be cause of the same records being recorded multiple times and I'm not specifying to only count it once.

     
  • Tuesday, February 19, 2013 4:42 PM
    Moderator
     
     
    Sign In to Vote
    0
    If your column ShortDate's data type is datetime, you should conside the time part of  the value. In your sample query, you will not get back any rows on 2/1/2013 other that the start of the day. You can try  ShortDate >='12/31/2012' and ShortDate < '2/2/2013'
     
  • Tuesday, February 19, 2013 5:24 PM
     
     
    Sign In to Vote
    0

    though the BETWEEN statement is correct, i typically use DATEDIFF() because what occurs when you run the query in japan or australia where the month/day are swapped?

    http://msdn.microsoft.com/en-us/library/ms187347.aspx

     
  • Tuesday, February 26, 2013 9:27 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    >where ClientCode = 'Test' and ShortDate between '12/31/2012' and '2/1/2013'

    Use ANSI date literals to increase database developer productivity:

    where ClientCode = 'Test' and ShortDate between '2012-12-31' and '2013-02-01'

    Safe programming for datetime date ranges:

    where ClientCode = 'Test' and ShortDate >= '2013-01-01' and  ShortDate < '2013-02-01'

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Tuesday, February 26, 2013 10:05 PM
    Moderator
     
     
    Sign In to Vote
    0
    Use ISO dates, e.g. dates that don't include any -, e.g. yyyymmdd format.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog