Now and Next TV Programme Information SQL Query

Question

My table is a list of Scheduled TV Programmes for multiple days and channels.

SELECT * FROM [Scheduled_Programmes]

Channel   Date          Time     Title
1         2012-09-19    06:00    Family Guy
2         2012-09-19    06:01    CSI Miami
3         2012-09-19    06:20    News
1         2012-09-19    06:30    Heroes
2         2012-09-19    07:01    Spiderman
3         2012-09-19    06:40    Batman
1         2012-09-19    07:30    Micky Mouse
2         2012-09-19    07:31    CSI New York
3         2012-09-19    07:10    Friends
1         2012-09-19    07:55    The Wire
2         2012-09-19    08:00    Dodgeball
3         2012-09-19    07:35    Gossip Girl

The result set I'm trying to create is What's on Now and What's on Next.

Let's assume the current datetime is (D/M/Y HH:MM) 19/09/2012 07:15 

So something like:

          Channel 1     Channel 2       Channel 3
NOW       Heroes        Spiderman       Friends  
NEXT      Micky Mous    CSI New York    Gossip Girl

I've been racking my brain for the best way to do this without having to hard code an individual query for each channel. I think I've got the the overthinking it stage now so it would be great if someone can point me in the right direction.

Thanks

PS: If it makes a difference I'm on Microsoft SQL Server 2012

Answer 1

This really seems like something you would let your GIU format and pivot but here's my go at it.

SELECT * FROM (
    SELECT * FROM (
        SELECT X.Status, X.Channel, X.Title FROM (
        SELECT 'NOW' as Status, Channel, Title, RANK() OVER (PARTITION BY Channel ORDER BY Time DESC) RANKED FROM Sceduled_Programs SP
        WHERE DateTime <= '7:15') X
        WHERE X.RANKED = 1
    ) A
    UNION ALL
        SELECT * FROM (
        SELECT Y.Status, Y.Channel, Y.Title FROM (
        SELECT 'NEXT' as Status, Channel, Title, RANK() OVER (PARTITION BY Channel ORDER BY Time ASC) RANKED FROM Sceduled_Programs SP
        WHERE DateTime > '7:15') Y
        WHERE Y.RANKED = 1
    ) B
) DataToPivot
PIVOT (MAX(Title) FOR Channel IN ([1], [2], [3])) AS PivotTable

Edit: I'm only using time here but just add date. You should really consider combining the date and time columns.

Edit2: To add date just replace the time compare with this. Should even work over date boundaries.

WHERE CAST(Date AS DATETIME) + CAST(Time AS DATETIME) > '19/09/2012 07:15'