CODEWITHSUNDEEP
sqlsql-server
Jammo
Jammo

Reputation: 2152

SQL Server query return next date with an event

Basic SQL question but I have a mind blank. I have a table with the following setup:

date          eventType
-----------------------
01/01/2016    0
02/01/2016    0
03/01/2016    2
03/01/2016    2
04/01/2016    6
04/01/2016    6
04/01/2016    6
04/01/2016    6
05/01/2016    0
06/01/2016    ...

I want to return the "next set of events where eventType<>0"

So, if "today" was 01/01/2016, the query would return:

03/01/2016      2
03/01/2016      2

If "today" was 03/01/2016, the query would return:

04/01/2016      6
04/01/2016      6
04/01/2016      6
04/01/2016      6

Etc.

Many thanks

Upvotes: 0

Views: 144

Answers (3)

profesor79
profesor79

Reputation: 9473

Maybe this will work:

SELECT eventDate, event 
FROM events
WHERE eventDayte > GETDATE()+1 -- limit here to datePart date to avoid confusion with time as this can lead to issues
-- we should provide limit here to avoid return all future events 
AND eventDate <= GETDATE()+2
AND eventType<>0

Upvotes: 0

chancrovsky
chancrovsky

Reputation: 580

I have a different solution, check this:

DECLARE @dtEventType DATE = '20160101'

DECLARE @table TABLE ( cDate DATE , eventType TINYINT )

INSERT INTO @table
VALUES( '20160101' , 0 ) 
, ( '20160102' , 0 )
, ( '20160103' , 2 )
, ( '20160103' , 2 )
, ( '20160104' , 6 )
, ( '20160104' , 6 )
, ( '20160104' , 6 )
, ( '20160104' , 6 )
, ( '20160105' , 0 )

SELECT * 
FROM @table L
WHERE cDate = ( 
    SELECT MIN( cDate ) AS mnDate
    FROM @table
    WHERE eventType <> 0
    AND cDate > @dtEventType
)

But I liked the @GordonLiff's 3rd solution .

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Hmmm. I think this may be a bit trickier than it seems. This does what you want for the data in the question:

select e.*
from events e cross join
     (select top 1 eventType
      from events
      where date > getdate() and eventType <> 0
      order by date
     ) as nexte
where e.date > getdate() and
      e.eventType = nexte.eventType;

Or, perhaps a better fit:

select e.*
from events e cross join
     (select top (1) e.*
      from events
      where date > getdate() and eventType <> 0
      order by date
     ) as nexte
where e.date > nexte.date and
      e.eventType = nexte.eventType;

Or, more simply:

select top (1) with ties e.*
from events e
where date > getdate() and eventType <> 0
order by date, eventType

Upvotes: 1

Related Questions