Any idea why the date WHERE clause is not functioning?

Question

This query is returning results from 2017, 2018 and other dates outside of the range.

SELECT 

    base.number [CTSK Number],
    base2.number [CHG Number],
    base.active [Active],
    chg.u_start_date__date_ [Start Date],
    usergroup2.name [Coordinating Group],
    coordinator.name [Coordinator],
    requester.name [Requester],
    manager.name [Group Manager /S.P.O.C.],
    base.short_description [Task Title],
    base.approval [Approval Status],
    assignee.name [Assignee],
    usergroup.name [Assignment Group],
    rejection.u_rejection_reason [Rejection Reason],
    chg.u_regression_count [Regression Count],
    chg.u_change_task_rejection_count [CHG CTSK Rejection Count],
    base.reassignment_count [CTSK Reassignment Count],
    base2.reassignment_count [CHG Reassignment Count]

FROM SN20_TASK base

    LEFT JOIN SN20_CHANGE_TASK task ON base.sys_id = task.sys_id
    LEFT JOIN SN20_CHANGE_REQUEST chg ON task.change_request = chg.sys_id
    LEFT JOIN SN20_TASK base2 ON chg.sys_id = base2.sys_id
    LEFT JOIN SN20_SYS_USER_GROUP usergroup2 ON base2.assignment_group = usergroup2.sys_id
    LEFT JOIN SN20_SYS_USER_GROUP usergroup ON base.assignment_group = usergroup.sys_id
    LEFT JOIN SN20_SYS_USER coordinator ON base2.assigned_to = coordinator.sys_id
    LEFT JOIN SN20_SYS_USER requester ON base2.opened_by = requester.sys_id
    LEFT JOIN SN20_SYS_USER manager ON usergroup2.manager = manager.sys_id
    LEFT JOIN SN20_SYS_USER assignee ON base.assigned_to = assignee.sys_id
    LEFT JOIN SN20_U_Approval_Rejection_Reasons rejection ON task.u_reject_reason = rejection.sys_id
   
WHERE 

    chg.u_start_date__date_ BETWEEN '2020-01-01 00:00:00' AND '2020-03-01 23:59:00'
    AND base.assignment_group = 'f1a0f8a76f2bc900c6032fe64f3ee4e5'
            OR (base.assignment_group = '4eb7dcae6f138d002ed488864f3ee4ed')
    AND base.sys_class_name LIKE 'change_task'

What am I doing wrong? In every other query I have ever done, a where clause would work. The date fields are something I always filter with on the same manner.

score 2 · Accepted Answer · answered Mar 28 '21 at 20:21

Your conditions are equivalent to:

(
 chg.u_start_date__date_ BETWEEN '2020-01-01 00:00:00' AND '2020-03-01 23:59:00'
 AND 
 base.assignment_group = 'f1a0f8a76f2bc900c6032fe64f3ee4e5'
)
OR 
(
 base.assignment_group = '4eb7dcae6f138d002ed488864f3ee4ed')
 AND 
 base.sys_class_name LIKE 'change_task'
)

because the operator AND has higher precedence than OR.

Use parentheses:

chg.u_start_date__date_ BETWEEN '2020-01-01 00:00:00' AND '2020-03-01 23:59:00'
AND 
(base.assignment_group = 'f1a0f8a76f2bc900c6032fe64f3ee4e5' OR base.assignment_group = '4eb7dcae6f138d002ed488864f3ee4ed')
AND 
base.sys_class_name LIKE 'change_task'

or the operator IN:

chg.u_start_date__date_ BETWEEN '2020-01-01 00:00:00' AND '2020-03-01 23:59:00'
AND 
base.assignment_group IN ('f1a0f8a76f2bc900c6032fe64f3ee4e5', '4eb7dcae6f138d002ed488864f3ee4ed')
AND 
base.sys_class_name LIKE 'change_task'

score 2 · Answer 2 · answered Mar 28 '21 at 20:22

2

The issue is the lack of parentheses, but you can also simplify the date logic and IN:

WHERE chg.u_start_date__date_ >= '2020-01-01' AND
      chg.u_start_date__date_  < '2020-03-02' AND
      base.assignment_group IN ('f1a0f8a76f2bc900c6032fe64f3ee4e5', '4eb7dcae6f138d002ed488864f3ee4ed') AND
      base.sys_class_name LIKE 'change_task'

This assumes that you actually do want the last minute on 2020-03-01.

answered Mar 28 '21 at 20:22

Gordon Linoff

1,242,037
58
646
786

Excellent, most appreciated – Andy Mar 28 '21 at 20:54

score 0 · Answer 3 · answered Mar 28 '21 at 20:35

Using or in where clause is causing this. Use parenthesis () to enclose the conditions for base.assignment_group.

where
chg.u_start_date__date_ BETWEEN '2020-01-01 00:00:00' AND '2020-03-01 23:59:00'
    AND ( base.assignment_group = 'f1a0f8a76f2bc900c6032fe64f3ee4e5'
            OR base.assignment_group = '4eb7dcae6f138d002ed488864f3ee4ed')
    AND base.sys_class_name LIKE 'change_task

Any idea why the date WHERE clause is not functioning?

3 Answers3