-4

Table A Transaction details

TTime (datetime), TableAValues, TableAValues, <etc>...

Table B related table

ID, DateTimeStart (datetime), DateTimeEnd (datetime), TableBValues, TableBValues, <etc>...

I need a way to return a row of information which includes All TableAValues as well as all TableBValues where TTime Table A falls between DateTimeStart and DateTimeEnd of table B.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user6168179
  • 1
  • 1
    Have you looked at existing question on SO like http://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers? – Charlie Apr 06 '16 at 19:48
  • Duplicate question. Do not ask same question which has been asked before by any other user. – Shyam Bhimani Apr 07 '16 at 20:24

1 Answers1

0
SELECT  * 
FROM    TableA AS A
        JOIN TableB AS B
            ON A.TTime BETWEEN B.DateTimeStart AND B.DateTimeEnd

Be aware that BETWEEN is inclusive, meaning that it will return true if A.TTime is the same as either B.StartDateTime or B.EndDateTime. If you want to get just the ones that truly fall between start and end you should use greater than > and less than < operators.

You should also be aware of accuracy on SQL Server, per MSDN, DateTime is

Rounded to increments of .000, .003, or .007 seconds

Running the following queries can help illustrate: 

DECLARE @StartTime DATETIME = '2016-01-01 00:00:00.000',
        @TestTime DATETIME = '2016-01-01 08:00:00.008', -- rounds down to .007      
        @EndTime1 DATETIME = '2016-01-01 08:00:00.007', -- stays the same
        @EndTime2 DATETIME = '2016-01-01 08:00:00.006'; -- rounds up to .007            

SELECT 'Eh? What just happened?'
WHERE   @TestTime BETWEEN @Starttime AND @EndTime1;

SELECT 'What is going on!'
WHERE   @TestTime BETWEEN @Starttime AND @EndTime2;
Stainy
  • 450
  • 2
  • 8