×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using BETWEEN operator in an inner join?

Using BETWEEN operator in an inner join?

Using BETWEEN operator in an inner join?

(OP)
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

CODE

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

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!

RE: Using BETWEEN operator in an inner join?

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:

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

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.

RE: Using BETWEEN operator in an inner join?

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


 

RE: Using BETWEEN operator in an inner join?

(OP)
many thanks for your replies..Appreciated

MadSween

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close