user2430812 user2430812 - 29 days ago 10
SQL Question

Exclude all records if 1 occurrence is currently effective

I have a table called '

Events
' that stores information about an event including effective start and end dates. The event can happen multiple times over time. I need to exclude all records of a given event if that event is currently effective. In my example below I only want to return a single value of 'Olympics' as that is the only event that is NOT active on today's date (today being 10/11/16 as I write this).


╔═══════════╦════════════╦════════════╗
║ Event ║ Start ║ End ║
╠═══════════╬════════════╬════════════╣
║ World Cup ║ 1/07/2016 ║ 15/07/2016 ║
║ Olympics ║ 15/08/2016 ║ 25/08/2016 ║
║ World Cup ║ 1/09/2016 ║ 15/09/2016 ║
║ Olympics ║ 15/09/2016 ║ 25/09/2016 ║
║ Olympics ║ 15/10/2016 ║ 25/10/2016 ║
║ World Cup ║ 1/11/2016 ║ 15/11/2016 ║
║ World Cup ║ 1/12/2016 ║ 15/12/2016 ║
╚═══════════╩════════════╩════════════╝

Answer
SELECT distinct Event
FROM tableName WHERE Event NOT IN (SELECT Event 
                                   FROM tableName
                                   WHERE CONVERT(VARCHAR(11),GETDATE(),103) BETWEEN StartDate AND EndDate
                                  )