Reply to thread

Message

<blockquote data-quote="Dirk" data-source="post: 65349" data-attributes="member: 11937">Being an database adminstrator myself, I have written a sql script that removes overlapping programs and leaves the smaller ones. For instance things like 'Nederland 3' and 'Z@pp', CNBC, Jetix, or 'Science Fiction week' are automatically removed.I have made 2 sqlscripts;This first sqlscript only shows the results,the second one really removes them.The scripts can be started within 'SQLite Database Browser' found in the Docs directory of mediaportal, use the execute SQL tab.You need to open the file TVDatabaseV21.db3in the subdirectory database of mediaportalDon't forget to save it before exitting SQLite Database Browser.This is also exactly my present problem: how can I automate this?Or can it be incorporated in mediaportal tvguide import routine itself?The first/(show) script:The name of the overlapping program, the names of the first and the last program within the timeframe of the overlapping program are shown.Followed by the times:starttime of the overlapping show, startime of first show, endtime of last show and the endtime of the overlapping show[code]Select p1.strTitle,&nbsp; &nbsp; &nbsp; p2.strTitle,&nbsp; &nbsp; &nbsp; p3.strTitle,&nbsp; &nbsp; &nbsp; p1.iStartTime,&nbsp; &nbsp; &nbsp; p2.iStartTime,&nbsp; &nbsp; &nbsp; p3.iEndTime,&nbsp; &nbsp; &nbsp; p1.iEndTimeFrom&nbsp;&nbsp; tblPrograms p1,&nbsp; &nbsp; &nbsp; tblPrograms p2,&nbsp; &nbsp; &nbsp; tblPrograms p3where&nbsp; p2.iStartTime &gt;=&nbsp; p1.iStartTimeand&nbsp; &nbsp; p2.iEndTime&nbsp;&nbsp; &lt;&nbsp;&nbsp; p1.iEndTimeand&nbsp; &nbsp; p2.idChannel&nbsp; =&nbsp;&nbsp; p1.idChanneland&nbsp; &nbsp; p3.iStartTime &gt;&nbsp;&nbsp; p1.iStartTimeand&nbsp; &nbsp; p3.iEndTime&nbsp;&nbsp; &lt;=&nbsp; p1.iEndTimeand&nbsp; &nbsp; p3.idChannel&nbsp; =&nbsp;&nbsp; p1.idChannel[/code]The second/(delete) script:[code]Delete tblPrograms p1where exists (Select 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tblPrograms p2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where p2.iStartTime &gt;=&nbsp; p1.iStartTime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp;&nbsp; p2.iEndTime&nbsp;&nbsp; &lt;&nbsp;&nbsp; p1.iEndTime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp;&nbsp; p2.idChannel&nbsp; =&nbsp;&nbsp; p1.idChannel)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp; exists (Select 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; from tblPrograms p3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; where p3.iStartTime &gt;&nbsp; p1.iStartTime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; and&nbsp;&nbsp; p3.iEndTime&nbsp;&nbsp; &lt;= p1.iEndTime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; and&nbsp;&nbsp; p3.idChannel&nbsp; =&nbsp; p1.idChannel)[/code]I hope you like it!</blockquote>

[QUOTE="Dirk, post: 65349, member: 11937"] Being an database adminstrator myself, I have written a sql script that removes overlapping programs and leaves the smaller ones. For instance things like 'Nederland 3' and 'Z@pp', CNBC, Jetix, or 'Science Fiction week' are automatically removed. I have made 2 sqlscripts; This first sqlscript only shows the results, the second one [b]really removes[/b] them. The scripts can be started within [b]'SQLite Database Browser'[/b] found in the [b]Docs[/b] directory of mediaportal, use the execute SQL tab. You need to open the file [b]TVDatabaseV21.db3[/b] in the subdirectory [b]database[/b] of mediaportal Don't forget to save it before exitting SQLite Database Browser. This is also exactly my present problem: how can I automate this? Or can it be incorporated in mediaportal tvguide import routine itself? The [b]first/(show)[/b] script: The name of the overlapping program, the names of the first and the last program within the timeframe of the overlapping program are shown. Followed by the times: starttime of the overlapping show, startime of first show, endtime of last show and the endtime of the overlapping show [code]Select p1.strTitle , p2.strTitle , p3.strTitle , p1.iStartTime , p2.iStartTime , p3.iEndTime , p1.iEndTime From tblPrograms p1 , tblPrograms p2 , tblPrograms p3 where p2.iStartTime >= p1.iStartTime and p2.iEndTime < p1.iEndTime and p2.idChannel = p1.idChannel and p3.iStartTime > p1.iStartTime and p3.iEndTime <= p1.iEndTime and p3.idChannel = p1.idChannel[/code] The [b]second/(delete)[/b] script: [code]Delete tblPrograms p1 where exists (Select 0 from tblPrograms p2 where p2.iStartTime >= p1.iStartTime and p2.iEndTime < p1.iEndTime and p2.idChannel = p1.idChannel) and exists (Select 0 from tblPrograms p3 where p3.iStartTime > p1.iStartTime and p3.iEndTime <= p1.iEndTime and p3.idChannel = p1.idChannel) [/code] I hope you like it! [/QUOTE]

Verification