Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2016
    Posts
    2

    Unequal cross join on 2 different columns

    Hi all,



    First time post. Usually I can work these things out or find a post somewhere that explains an approach I could use. But not this time.

    I have a fact table relating to call that I need to indicate if the duration of a call spans 5 minute intervals and display which 5 minute intervals the call was present in.

    The source data has a aggregate granularity of 15 minutes in a reference table but that is too high level for what we need.

    I have the start and end time of the call and want to show the 5 minute intervals the call was present in. For example A call that began at 12:30:34 and finished at 12:52:12 I want to show as:

    CallID, Start Time, End time, 5 Min Period

    12345, 12:30:34, 12:52:12, 12:30


    12345, 12:30:34, 12:52:12, 12:35


    12345, 12:30:34, 12:52:12, 12:40


    12345, 12:30:34, 12:52:12, 12:45


    12345, 12:30:34, 12:52:12, 12:50

    I thought I could do this using a cross join using a reference table with unequal conditions ('>=' and '<=') but I'm not getting the correct results.

    The reference table I set up has the from and to periods with a period label eg:

    From, To, Period_label

    12:00:00, 12:05:00, 12:00
    12:05:00, 12:10:00, 12:05
    12:10:00, 12:15:00, 12:10
    12:15:00, 12:20:00, 12:15
    etc - for the entire day

    So my query is

    Select Call_data.*, Time_ref. Period_label
    from Call_data, Time_ref
    where Call_data.Time_start >= Time_ref.from and Call_data.Time_end <=Time_ref.To

    This does return results but only about 10% of records it could.

    I have also tried
    Select Call_data.*, Time_ref. Period_label
    from Call_data inner join
    Time_ref on Call_data.Time_start >= Time_ref.from and Call_data.Time_end <=Time_ref.To

    This returns nothing at all.

    Any advice would be greatly appreciated.

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have also tried
    Select Call_data.*, Time_ref. Period_label
    from Call_data inner join
    Time_ref on Call_data.Time_start >= Time_ref.from and Call_data.Time_end <=Time_ref.To

    This returns nothing at all.
    From and End are reserved words so may be causing an issue - recommend change them to something else

    and you want your join slightly different

    Code:
    on  Call_data.Time_start <= Time_ref.to and Call_data.Time.end >=Time_ref.From

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may want to investigate the Partition() function to see if it is applicable to your needs.
    Search for examples using Google/Bing.

  4. #4
    Join Date
    Feb 2016
    Posts
    2
    Thanks Ajax and Orange.

    Ajax - I knew I was missing something obvious and it was the sequence of the joins. It also didn't help that I had some bad entries in my reference table that went backwards in time and not forwards. But now that I e changed both the query works a treat.

    Orange - I've never used the partition function so I'll look into that.

    Thanks to both for providing advice

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-14-2015, 02:53 PM
  2. Replies: 22
    Last Post: 09-16-2013, 08:48 PM
  3. Unequal Join
    By SobJim in forum Queries
    Replies: 2
    Last Post: 05-19-2012, 09:48 AM
  4. Unequal Join or Union?
    By cap.zadi in forum Queries
    Replies: 3
    Last Post: 09-23-2011, 05:57 AM
  5. Inner Join on Sorted Columns
    By Nobody in forum Queries
    Replies: 6
    Last Post: 08-24-2010, 03:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums