SQL Server Date Range

Question

I have a SQL Server table that contains the following dates (OpenDate, ClosedDate, WinnerAnnouncedDate).

I have 3 rows, for 3 different categories.

I'm trying to figure out how I would get the following scenario:

Today is 14th March. I want to find out which category had the winner announced, but the following category hasn't started yet.

So if Row 1 had OpenDate = 12th Feb, ClosedDate = 10th March, WinnerAnnounced = 12th March Row 2 had an OpenDate of 16th March I need it to find Row 1 because the winner has been announced, but the following category hasn't opened yet.

This may seem a little confusing, so I'll be ready to clear things up if required.

Answer 1

SELECT TOP 1 WITH TIES *
FROM atable
WHERE WinnerAnnouncedDate <= GETDATE()
ORDER BY WinnerAnnouncedDate

WITH TIES will return several rows if several WinnerAnnouncedDate values match the condition and have the same top value.

Answer 2

I'm not 100% clear on what you're saying, but I think it's something like: Find the last winner announced from categories that have a start date earlier than now.

If that's the case then something like this might work for you. I'm assuming that your table is called #dates as you haven't included the table name

create table #dates (
    id int identity(1,1) primary key,
    openDate datetime,
    closedDate datetime,
    WinnerAnnouncedDate datetime
)

insert into #dates
values ('12 feb 2012', '10 march 2012', '13 march 2012')


insert into #dates
values ('12 feb 2012', '10 march 2012', null)

insert into #dates
values ('16 mar 2012', null, null)


select * 
from #dates
where id = (select max(id) from #dates where openDate <= getdate() and winnerAnnouncedDate is not null)


--drop table #dates