Using BETWEEN operator in an inner join?

[Sweenster]

Hey SQL Guru's

My new approach to working out NETSALES uses a VAT table rather than CASE statements and is based on a BETWEEN operator with an INNER JOIN to match transaction dates to the correct VATRATE.

It will be the first time I have used a BETWEEN operator with an INNER JOIN so I wanted to check if this approach has any hidden dangers?

VAT TABLE
StartDate EndDate VATRATE
1994-01-01 00:00:00 2008-11-30 00:00:00 1.175
2008-12-01 00:00:00 2009-12-31 00:00:00 1.15
2010-01-01 00:00:00 NULL 1.175

e.g. 
SELECT
<COLUMNS>
FROM tbl_Sales_Staging s
[blue]INNER JOIN [tbl_VAT] v ON 
s.DATE BETWEEN v.StartDate and v.EndDate [/blue]

I have tested it and I am happy with the results but as I could not see much on Google etc I wanted to check with the experts just to reassure myself!

 

[bborissov]

The only danger I see here is IF you have a date that fall in two or more periods. But that is not possible in your case.
So, imagine this:
[tt]
VAT TABLE
----------------------------
StartDate EndDate VATRATE
------------------------------------------------------
1994-01-01 00:00:00 2008-11-30 00:00:00 1.175
1994-05-01 00:00:00 2008-07-31 00:00:00 1.8
[/tt]
And you want to check this date:
2008-06-01
You will get two records for that period.
(As I said that is not a case here)
Also, when you have a NULL in some of the datefield, NO records will be selected for that period, no matther what value you have in the other (I just saw your last record.)


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NiceArms]

There are hidden issues with BETWEEN. I had issues with DATETIMES:

(BETWEEN 01/01/2009 AND 01/01/2009)
Is how you would write the query in the hope that you would pick up data from both dates but BETWEEN assumes the following if no time is specified:
(BETWEEN 01/01/2009 00:00:000 AND 01/01/2009 00:00:000)

So you will not pick up any data as midnight of 01/01/2009 to midnight 01/01/2009 is the same thing

I would suggest using >= and <=

I hope i have explained this clearly, however I found the following very useful:

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p2.aspx

 

[Sweenster]

many thanks for your replies..Appreciated

MadSween