Difference using between or > <

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

Difference using between or > <

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-13 : 15:41:07

Why am I getting different results with this query?


SELECT t.fo, Isnull(COUNT(t.cossn), 0) AS [Pend300] 
    FROM t16pendall t
    WHERE (mft_POSN1_CD in('b','d')  OR  (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
    AND ( Datediff(DAY, flg_cdt, Getdate()) between 300 and 351) 
    
    GROUP BY t.fo

and this one

SELECT t.fo, Isnull(COUNT(t.cossn), 0) AS [Pend300] 
    FROM t16pendall t
    WHERE (mft_POSN1_CD in('b','d')  OR  (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
    AND ( Datediff(DAY, flg_cdt, Getdate()) > 300 ) 
    AND ( Datediff(DAY, flg_cdt, Getdate()) < 351 )                                                
    GROUP BY t.fo

I have more records using between. Which one is correct? or

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-13 : 15:44:43

Between is inclusive. So the following two are equivalent:


    AND ( Datediff(DAY, flg_cdt, Getdate()) between 300 and 351)


    AND ( Datediff(DAY, flg_cdt, Getdate()) >= 300 ) 
    AND ( Datediff(DAY, flg_cdt, Getdate()) <= 351 )

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-13 : 15:47:25

Ok I see I have the same number when I added the = to the > <

So why was this one wrong?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-13 : 15:55:12

quote:
Originally posted by JJ297

Ok I see I have the same number when I added the = to the > <

So why was this one wrong?

Same number as what you get when you use between, or same number regardless of whether you use <= or < ?

Not sure what you are referring to when you ask "why as this one wrong". Can you elaborate?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-13 : 16:13:09

Sorry about that...

When I used yours...
(Datediff(DAY, flg_cdt, Getdate()) between 300 and 351)

or

AND ( Datediff(DAY, flg_cdt, Getdate()) >= 300 )
AND ( Datediff(DAY, flg_cdt, Getdate()) <= 351 )

I get the same number which is 760 records.

I wanted to know why was my orginial one giving me less records? Yours is right as I was missing records.

AND ( Datediff(DAY, flg_cdt, Getdate()) > 300 )
AND ( Datediff(DAY, flg_cdt, Getdate()) < 351 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources