CODEWITHSUNDEEP
sqlsql-server-2008datetimebetween
Jason Wells
Jason Wells

Reputation: 889

SQL, Selecting between date/times

This is for (MS SQL Server 2008) It allows the user to schedule other users for a start and end date/time without overlaps.

I'm addressing the business rule of not being able to overlap individuals during a schedule. E.G, If user 1 is scheduled from 1/1/2012 to 1/4/2012, user 2 should not be able to submit a request for 1/2/2012 to 1/4/2012. This part is already taken care of, and here is my SQL.

SELECT * FROM fooTable
WHERE
    Prim = 1
AND 
    Group = 10
AND
    (('2012-06-01 08:01' between startdate and enddate
   OR '2012-06-03 08:01' between startdate and enddate)
OR
   ('2012-06-01 08:01' < startdate) AND ('2012-06-03 8:01' > enddate))

I now have a requirement to allow a schedule to overlap in the sense that the new shift can begin as the last one ends. E.G, My end date is 1/1/2012 at 8:00pm - I should be able to schedule someone to start at 1/1/2012 and 8:00pm.

Need some help thinking this one through.

Upvotes: 1

Views: 1263

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

First off, don't use Group as a column name. It is a reserved word in every SQL standard. I renamed it to grp for the purpose of my answer.

Trying to schedule a new shift from '2012-06-01 08:00' to '2012-06-03 08:00' ...

INSERT INTO tbl (prim, grp, startdate, enddate)
SELECT 1, 10, '2012-06-01 08:00', '2012-06-03 08:00'
WHERE  NOT EXISTS (
    SELECT *
    FROM   tbl
    WHERE  prim = 1
    AND    grp = 10
    AND   '2012-06-03 08:00' > startdate -- not >= to allow sharing a border
    AND   '2012-06-01 08:00' < enddate   -- and not BETWEEN ... AND either
    )

Note that I compare:

new_end   > old_start
new_start < old_end

If you use BETWEEN .. AND .. you include the borders of a shift in your test. It's the same as using >= and <=. You need to use > and < to allow borders to overlap.

Well, and try my largely simplified syntax. Not sure about what you had there originally.
Here is a working demo on sqlfiddle.com to play with.

Upvotes: 3

Related Questions