Link Tables within a Date and Time Range

[smfd]

Here is my problem. I am trying to associate an alarm with the shift that the alarm occurred on. I have two tables for alarm and shift. The dates are actually date/time fields, but I have dropped off the 00:00:00.000 to make it fit.

The times are integer fields in the database. The first two numbers are the hour, followed by the minutes, and then the seconds. Notice that the times don't match since one field has seconds and the times n the shift do not.

The alarm occurs at a specific date and that time falls within a shift.

***Alarm Table***
alarm_number alarm_date alarm_time
200911020034 2009-11-02 011322
200911020036 2009-11-02 012255
200911020102 2009-11-02 035045
200911020326 2009-11-02 092716
200911030038 2009-11-03 013511

***Shift Table***
shift date_on date_off time_on time_off
A 2009-11-01 2009-11-02 0700 0700
C 2009-11-02 2009-11-03 0700 0700
A 2009-11-03 2009-11-04 0700 0700
B 2009-11-04 2009-11-05 0700 0700

In the end, I need to know an alarm_number and shift as below.

200911020034 A
200911020036 A
200911020102 A
200911020326 C
200911030038 C

I am using Crystal Reports 2008 and MS SQL Database. I can either create a view in SQL or do via Crystal Reports.

Thanks.