存储过程返回错误的值?

I have a stored procedure that check holidays that are booked off in my database and it should not take into account holidays with a status of 'Declined' or 'Cancelled', yet o have only one record in my database which has a status of declined but this select statement returns 1?

假期我有一个存储过程,检查预定在我的数据库,它不应该考虑假期的状态“拒绝”或“取消”,然而o只有一个记录在我的数据库状态下降,但这个select语句返回1 ?

        SELECT COUNT(*) JobRoleID
        FROM        Employees
        RIGHT JOIN  Holidays
        ON          Employees.ID = Holidays.EmployeeID
        WHERE       Holidays.Status <> 'Declined' AND Holidays.Status <> 'Cancelled'
        AND        (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/30')
        OR         (Holidays.Startdate >= '2014/04/28' AND Holidays.Enddate <= '2014/04/30') 
        OR         (Holidays.Startdate <= '2014/04/30' AND Holidays.Enddate >= '2014/04/30') 
        OR         (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/28') 
        OR         (Holidays.StartDate = '2014/04/28' AND Holidays.EndDate = '2014/04/30')

2 个解决方案

#1

Operator precedence needs to be taken into consideration here.

这里需要考虑操作符的优先级。

Not -->  AND --> OR

Not takes precedence over AND , AND takes precedence over OR. With these nested ANDs and ORs it is always better to put them in paranthesis.

没有优先于和，并且优先于或。使用这些嵌套的and和ORs时，最好把它们放到paranthesis中。

WHERE       Holidays.[Status] <> 'Declined' AND Holidays.[Status] <> 'Cancelled'
AND    
    (       (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/30')
        OR  (Holidays.Startdate >= '2014/04/28' AND Holidays.Enddate <= '2014/04/30') 
        OR  (Holidays.Startdate <= '2014/04/30' AND Holidays.Enddate >= '2014/04/30') 
        OR  (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/28') 
        OR  (Holidays.StartDate = '2014/04/28' AND Holidays.EndDate = '2014/04/30')
    )

Also avoid using Sql server key words as your column names, if you do have some use square brackets [] around them when using them in your sql.

如果您在Sql中使用Sql server关键字时确实使用了方括号[]，也要避免使用Sql server关键字作为列名。

#2

WHERE A AND B AND C OR D AND E OR F AND G

Is treated the same as

治疗方法相同吗?

WHERE (A AND B AND C) OR (D AND E) OR (F AND G)

So if any of your date comparisons other than the first one are in fact true, then the entire WHERE clause is satisfied.

所以如果你的日期比较除了第一个是真的，那么整个WHERE子句就满足了。

And, by the way, it looks like you're trying to find whether two date ranges overlap, and doing it in a very complex matter. You should only need to do two comparisons. Two date ranges overlap if:

顺便说一下，这看起来像是你在试图找出两个日期范围是否重叠，并在一个非常复杂的问题中进行。你只需要做两个比较。如果:

The first range starts before the end of the second range, and
第一个范围在第二个范围结束之前开始
The second range starts before the end of the first range
第2个范围在第一个范围结束之前开始。

So, something like:

所以,类似:

SELECT COUNT(*) JobRoleID
    FROM        Employees
    RIGHT JOIN  Holidays
    ON          Employees.ID = Holidays.EmployeeID
    WHERE       Holidays.Status <> 'Declined' AND Holidays.Status <> 'Cancelled'
    AND    Holidays.StartDate <= '20140430' AND '20140428' <= Holidays.EndDate

Should be the equivalent of your multiple comparisons.

应该等于你的多重比较。

#1