SQL Server：查询一周的特定时间，我们

路易斯-菲利普·德斯坎普

我需要运行一个查询，根据时间和日期返回数据。

在一周内，它将返回 17:00 至 09:00 的数据。
在周末，它将返回00:00到23:59的数据

数据将匹配一个数字值

这是我尝试过的，但返回的数据没有考虑数值

SELECT 
    I3TimeStampGMT, DNIS, AssignedWorkGroup, LocalName, RemoteNumber, 
    CallDurationSeconds
FROM
    CIC_Prod.dbo.calldetail_viw 
WHERE 
    (I3TimeStampGMT > '2017-12-01' 
    AND I3TimeStampGMT <= DATEADD(day,1,'2017-12-05') 
    AND (DATEPART(HH,I3TimeStampGMT) IN (18, 07)
         OR (DATEPART(HH,I3TimeStampGMT) = 17 AND DATEPART(MI,I3TimeStampGMT) >= 00) 
         OR (DATEPART(HH,I3TimeStampGMT) = 08 AND DATEPART(MI,I3TimeStampGMT) <= 59)
        )
    AND DATEPART(dw,I3TimeStampGMT) >= 2 AND DATEPART(dw,I3TimeStampGMT) <= 6
        --sun(1)-mon(2)-tue(3)-wed(4)-thu(5)-fri(6)-sat(7)
        )
    OR  I3TimeStampGMT > '2017-12-01' AND I3TimeStampGMT <= DATEADD(day,1,'2017-12-05')
    and (
        DATEPART(HH,I3TimeStampGMT) IN (18, 07)
        OR (DATEPART(HH,I3TimeStampGMT) = 17 AND DATEPART(MI,I3TimeStampGMT) >= 00) 
        OR (DATEPART(HH,I3TimeStampGMT) = 08 AND DATEPART(MI,I3TimeStampGMT) <= 59)
        )
    AND DATEPART(dw,I3TimeStampGMT) >= 7 AND DATEPART(dw,I3TimeStampGMT) <= 7


AND
LineId = 'SIP-100-UK'
AND(
DNIS = '4910045'
OR
DNIS = '4910044'
OR
DNIS = '4910043'
OR
DNIS = '4910025'
OR
DNIS = '4910024'
OR
DNIS = '4910023'
OR
DNIS = '4910021'
OR
DNIS = '4910026'
OR
DNIS = '4910019'
OR
DNIS = '4910022'
OR
DNIS = '4910020'
)

化脓

您的过滤器是使用ANDand 制作的OR，如果中的条件之一OR为真，其余的应该是true或false，这无关紧要。

为了考虑数值，它应该添加AND到前面的条件中，所以你需要另一对括号，我用--<<<-----：

SELECT 
    I3TimeStampGMT, DNIS, AssignedWorkGroup, LocalName, RemoteNumber, 
    CallDurationSeconds
FROM
    CIC_Prod.dbo.calldetail_viw 
WHERE 
( --<<<------------------------------------------------------------------------------
    (I3TimeStampGMT > '2017-12-01' 
    AND I3TimeStampGMT <= DATEADD(day,1,'2017-12-05') 
    AND (DATEPART(HH,I3TimeStampGMT) IN (18, 07)
         OR (DATEPART(HH,I3TimeStampGMT) = 17 AND DATEPART(MI,I3TimeStampGMT) >= 00) 
         OR (DATEPART(HH,I3TimeStampGMT) = 08 AND DATEPART(MI,I3TimeStampGMT) <= 59)
        )
    AND DATEPART(dw,I3TimeStampGMT) >= 2 AND DATEPART(dw,I3TimeStampGMT) <= 6
        --sun(1)-mon(2)-tue(3)-wed(4)-thu(5)-fri(6)-sat(7)
        )
    OR  I3TimeStampGMT > '2017-12-01' AND I3TimeStampGMT <= DATEADD(day,1,'2017-12-05')
    and (
        DATEPART(HH,I3TimeStampGMT) IN (18, 07)
        OR (DATEPART(HH,I3TimeStampGMT) = 17 AND DATEPART(MI,I3TimeStampGMT) >= 00) 
        OR (DATEPART(HH,I3TimeStampGMT) = 08 AND DATEPART(MI,I3TimeStampGMT) <= 59)
        )
    AND DATEPART(dw,I3TimeStampGMT) >= 7 AND DATEPART(dw,I3TimeStampGMT) <= 7
)--<<<------------------------------------------------------------------------------

AND
LineId = 'SIP-100-UK'
AND(
DNIS = '4910045'
OR
DNIS = '4910044'
OR
DNIS = '4910043'
OR
DNIS = '4910025'
OR
DNIS = '4910024'
OR
DNIS = '4910023'
OR
DNIS = '4910021'
OR
DNIS = '4910026'
OR
DNIS = '4910019'
OR
DNIS = '4910022'
OR
DNIS = '4910020'
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。