Find all "events" that happen between dates!?
-
Hi all,
n00by tsql guy looking for support here.
Im trying to build a report on table that contains info about events.
The table has a start date and enddate for the "event".
CREATE TABLE testdates (
startdate DATE
,enddate DATE
, descript VARCHAR (100)
)
INSERT INTO testdates (startdate, enddate, descript)
VALUES ('2012-01-01', '2012-02-01', 'Jan Event')
,('2012-01-10', '2012-02-15', 'short meeting')
,('2012-01-14', '2012-01-15', 'day meeting')
,('2012-02-01','2012-03-01','feb event')
,('2012-01-15','2012-02-15','mid month event')
My report is meant to allow the user to put in 2 dates and find all the courses that happen during that time. But i think im still missing something.
I have:
DECLARE @Start DATE
DECLARE @End DATE
SET @Start = '2012-01-16'
SET @End = '2012-01-20'
SELECT
startdate
,enddate
,descript
FROM testdates
WHERE
(startdate <= @Start AND enddate BETWEEN @Start AND @End)
OR
(enddate >= @End AND startdate BETWEEN @Start AND @End)
OR
(startdate <= @Start AND enddate >= @End)
ORDER BY startdate, enddate
Will that always find any event that is happening during the dates they give? It seems to work for this short amount of date. But looking for someone wiser than I to confirm its right or set me on the right path!
Thanks
S
-
I think it simplifies to this
SELECTstartdate
,enddate
,descript
FROM testdates
WHERE @End >= startdate AND @Start<=enddate
ORDER BY startdate, enddate
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
haha
yeah... that seems to do it as well!
thanks.. 🙂
-
you might want consider the below query to understand how the results meaning while change
CREATE TABLE #testdates (
startdate DATE
,enddate DATE
, descript VARCHAR (100)
)
INSERT INTO #testdates (startdate, enddate, descript)
VALUES ('2012-01-01', '2012-02-01', 'Jan Event')
,('2012-01-10', '2012-02-15', 'short meeting')
,('2012-01-14', '2012-01-15', 'day meeting')
,('2012-02-01','2012-03-01','feb event')
,('2012-01-15','2012-02-15','mid month event')
declare @start datetime ='2012-01-10'
declare @end datetime = '2012-02-01'
SELECT
startdate
,enddate
,descript
, 'status' = case when enddate > @end then 'In progress' else 'Completed' end
FROM #testdates
WHERE @End >= startdate AND @Start<=enddate
--where @start between startdate and enddate
--and @end between startdate and enddate
ORDER BY startdate, enddate
--declare @end datetime = '2012-02-01'
--declare @start datetime ='2012-01-10'
SELECT
startdate
,enddate
,descript
, 'status' = case when enddate > @end then 'In progress' else 'Completed' end
FROM #testdates
--WHERE @End >= startdate AND @Start<=enddate
where @start between startdate and enddate
and @end between startdate and enddate
ORDER BY startdate, enddate
Drop table #testdates
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply