Clever Geek Handbook

SQL Server Date Range

I have a table SQL Server, which contains the following dates ( OpenDate

, ClosedDate

, WinnerAnnouncedDate

).

I have 3 lines, for 3 different categories.

I am trying to figure out how to get the following script:

Today is March 14th. I want to know which category has been declared the winner, but the next category has not started yet.

So, if you Row 1

had OpenDate = 12th Feb

, ClosedDate = 10th March

, WinnerAnnounced = 12th March

Line 2 had OpenDate

of 16th March

I need to be within line 1, because the winner was announced, but the next category is not already open.

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

+3


source to share


2 answers


I don't 100% understand what you are saying, but I think it is something like: Find the latest winner announced from categories that have a start date earlier than now.

If this is the case, then something like this might work for you. I am assuming your table is called #dates since you did not provide 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
+1


source 


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


WITH TIES

will return multiple rows if multiple values WinnerAnnouncedDate

match the condition and have the same top value.

+1


source






More articles:


All Articles