COUNT CASE语句返回不需要的查询结果

The below query is looking for a count of days by object when the event was "Regular", but the query results show "Clearance" because that object falls within the specified date range. Is there a way to make this query only show objects that had "Regular" events in date range of 4/10/2017 through Today? I have tried Event NOT IN C or S and moving the Event in the where clause, but get the same result.

当事件为“常规”时,以下查询将查找按对象计算的天数,但查询结果显示“清除”,因为该对象属于指定的日期范围。有没有办法让这个查询只显示日期范围为4/10/2017到今天的“常规”事件的对象?我已经尝试了事件不在C或S并在where子句中移动事件,但得到相同的结果。

Table 1:

Cal_Date    Object  Event    Time Stamp Time Stamp
4/10/2017   Soap    Regular     4/10/2017   4/10/2017
4/11/2017   Soap    Sale        4/11/2017   4/14/2017
4/12/2017   Soap    Sale        4/11/2017   4/14/2017
4/13/2017   Soap    Sale        4/11/2017   4/14/2017
4/14/2017   Soap    Sale        4/11/2017   4/14/2017
4/15/2017   Soap    Regular     4/15/2017   4/20/2017
4/16/2017   Soap    Regular     4/15/2017   4/20/2017
4/17/2017   Soap    Regular     4/15/2017   4/20/2017
4/18/2017   Soap    Regular     4/15/2017   4/20/2017
4/19/2017   Soap    Regular     4/15/2017   4/20/2017
4/20/2017   Soap    Regular     4/15/2017   4/20/2017
4/10/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/11/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/12/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/13/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/14/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/15/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/16/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/17/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/18/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/19/2017   Shampoo Clearance   4/10/2017   12/31/2017
4/20/2017   Shampoo Clearance   4/10/2017   12/31/2017


SELECT
    OBJECT
    COUNT(CASE WHEN EVENT='Regular'AND CAL_DATE BETWEEN DATE '4/10/2017' AND CURRENT_DATE THEN 1 END) AS DAYS
FROM TABLE 1
GROUP BY
    OBJECT

Query Results:

Object  Days
Soap    7
Shampoo ?

Desired Results:

Object Days
Soap    7

1 个解决方案

#1

Try this.

SELECT
     [OBJECT]
     ,REGULAR = SUM(CASE WHEN [EVENT] = 'Regular' AND CONVERT(DATE,Cal_Date) BETWEEN '2017-04-10' AND CONVERT(DATE,GETDATE()) THEN 1 ELSE 0 END)
FROM
     TABLE1
GROUP BY
     [OBJECT]