Member Avatar for dannyd

At our television station we have a schedule of shows that need to be aired (stationschedule) and shows that have aired confirmed by our on air system (asrunlogs).

What I'd like to do is compare both tables against eachother to see if there are any major differences. Both tables have two similar fields showid, airtime as below:

Stationschedule
-----------------
ShowID
Show_Name
Airtime

Asrunlogs
-----------------
ShowID
Airtime

However If I pull a sample of the asrunlogs for ShowID LOC-3305:

SELECT ShowID, airtime
FROM asrunlogs
WHERE ShowID = "LOC-3305"
AND airtime LIKE "2008-06-26%"

ShowID Airtime
LOC-3305 2008-06-26 12:31:30
LOC-3305 2008-06-26 16:02:29
LOC-3305 2008-06-26 17:31:30

Then I do the same from stationschedule for same id LOC-3305:

SELECT ShowID, airtime
FROM stationschedule
WHERE ShowID = "LOC-3305"
AND airtime LIKE "2008-06-26%"

ShowID Airtime
LOC-3305 2008-06-26 12:31:30
LOC-3305 2008-06-26 16:02:30 ** difference 1 second
LOC-3305 2008-06-26 17:41:30 ** difference 10 minutes

As you can see one of the times has a difference of 10 minutes. What I'd like to do is query the rows that have a time difference greater than 30 seconds when comparing the two tables.


SELECT a.airtime, a.ShowID
FROM asrunlogs a
WHERE a.Airtime LIKE "2008-06-26%"
AND a.ShowID = "LOC-3305"
AND a.Airtime NOT
IN (SELECT b.Airtime
FROM stationschedule b
WHERE TIME_TO_SEC( TIMEDIFF( RIGHT( a.Airtime, 9 ) , RIGHT( b.Airtime, 8 ) ) ) > 30)


Any Idea whats wrong with my syntax or is there an easier way of doing this ?

  • 2 Contributors
  • 1 Reply
  • 85 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

Hello

In subselect you select b.Airtime and then you compare it with a.Airtime but between
them there are still large differences.

try these changes:

AND a.Airtime -- you want to get airtime with differences greater than 30 sec
IN (SELECT a.Airtime
FROM stationschedule b
WHERE TIME_TO_SEC( TIMEDIFF( RIGHT( a.Airtime, 9 ) , RIGHT( b.Airtime, 8 ) ) ) > 30

AND a.ShowID = b.ShowID -- necessary, to avoid comparing apples and oranges.
)


You may first test this subselect, especially result of TIME_TO_SEC():
SELECT a.Airtime
FROM stationschedule b
WHERE TIME_TO_SEC( TIMEDIFF( RIGHT( a.Airtime, 9 ) , RIGHT( b.Airtime, 8 ) ) ) > 30

AND a.ShowID = b.ShowID -- necessary, to avoid comparing apples and oranges.


----
tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.