Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Overlapping tv programmes

    This ought to be straightforward. But as per usual I'm stuck with 1 hour till the day is over, low on coffeine and simply cant get it to work.

    Even been looking through a ton of posts here and cant seem to find something like it... most likely because its bl.... obvious

    Code:
    Select T.* 
    from tv_programs T1 inner join tv_program T2
    on T1.tv_program_date = T2.tv_program_date AND (T1.tv_program_endtime between T2.tv_program_starttime and T2.tv_program_endtime)
    So basically this is one single table with tv programmes.

    Every programme has a scheduled transmission date, starting time and ending time.

    Basically I want to check if any one programme ends within any other programmes transmission timeframe, effectively spotting overlaps between programmes.

    Any help would be more than helpful,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    After a quick look it appears you've forgotten your "WHERE" before the "BETWEEN". Looks like you're trying to join two tables with a condition.

    Sam, hth
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can actually join like that, but typically you would move the BETWEEN to the WHERE clause.
    You would want to ensure T1.PK <> T2.PK too.
    This is an excellent explanation of over lapping times:
    http://www.dbforums.com/microsoft-sq...procedure.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Learn something new every day!

    Sam, guess it's not a wasted day after all.
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One other thing - any tv program that starts and ends on different days will cause problems.

    Other than that lot, if your query still doesn't work you;'ll have to tell us exactly what results you are getting and why they are wrong.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •