8

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.

Richard C
  • 389
  • 2
  • 5
  • 16
  • 1
    Did you even try it? What errors did you get? – Oded Mar 31 '13 at 17:30
  • I get no errors and it runs the problem is the row count, it makes my row count lower when in theory if its splitting the data up into timeID's it should be increasing the amount not decreasing – Richard C Mar 31 '13 at 17:35

1 Answers1

7

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
What have you tried
  • 11,018
  • 4
  • 31
  • 45
  • Hi thanks for that, The top solution returned no rows at all for some reason and the between is what I've been using. Does the join always have to be a = or can it be BETWEEN? – Richard C Mar 31 '13 at 17:33
  • @RichardC see my latest edit, I think the syntax of your join is incorrect – What have you tried Mar 31 '13 at 17:37
  • Cheers for that makes sense. 1 thing, I've got 4 inner joins, do i place all the joins one after another and then all the WHERE AND at the end? – Richard C Mar 31 '13 at 17:43
  • @RichardC yes that's correct. I updated the answer to demonstrate how that might look – What have you tried Mar 31 '13 at 17:44
  • Hi thanks yeah got it like that, still producing the same row count as before tho – Richard C Mar 31 '13 at 17:47
  • @RichardC May I recommend you open up a new question with the new issues. The scope of this question was the issue of selecting a date between two values, which I think we've covered. So, no harm no foul, open up a new question with complete table definitions and we will help you through your joins. – What have you tried Mar 31 '13 at 17:50
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/27283/discussion-between-richard-c-and-evan) – Richard C Mar 31 '13 at 17:54