Inner Join BETWEEN two values

Question

I'm currently trying to populate my fact table and I'm having trouble populating the required time ID's. The time dimension is fully populated and complete. Basically in the time dimension each row is a 15 minute period. The fact table is populating details about calls. And in a staging call table I have the start and end time of the call.

I'm wanting to populate the fact table by joining the dimension On the staging table by the time start and when the time_Start in the dimension is between the start and end time in the staging.. I've ran the query below using the = operator but it does not seem to work and only pulls out 100 rows when i should be expecting more like 4000. Can i use BETWEEN instead of the = ?

INNER JOIN Time_Dim ON incoming_measure.StartTimeDate = Time_Start
WHERE Time_Start BETWEEN incoming_measure.StartTimeDate AND incoming_measure.EndTimeDate

Thanks for the help.

Answer 1

Edit:

Your join is incorrect. The format of your join should be:

Select column from table A
INNER JOIN table b on
    A.column = B.column
INNER JOIN table c on
    B.column = c.column
Where B.column Between start_date AND end_date

You can use the < and > operators

Also, for sqlserver it is important to use the yyyy-mm-dd format

INNER JOIN Time_Dim ON incoming_measure.StartTimeDate = Time_Start
WHERE Time_Start > incoming_measure.StartTimeDate AND Time_Start < incoming_measure.EndTimeDate

You can also use Between

INNER JOIN Time_Dim ON incoming_measure.StartTimeDate = Time_Start
WHERE Time_Start Between incoming_measure.StartTimeDate AND incoming_measure.EndTimeDate