我在查询的 Where 子句中的日期部分计算时遇到问题。查询返回的结果没有计算,但如果我添加条件,则什么都没有。
DECLARE @StatusId INT;
SELECT @StatusId = Id FROM company.Status WHERE Name = 'Signed' AND CompanyId = 1;
SELECT FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd') AS newDate,
SUM(CASE WHEN cont.UpdatedDate IS NOT NULL THEN 1 ELSE 0 END) AS TotalSignedLeads
FROM client.testw cont
WHERE cont.CompanyId = 1
AND cont.AffiliateId = 1
AND cont.CreatedDate BETWEEN '7-01-2017' AND '7-09-2017'
AND DATEPART(dw, cont.CreatedDate) NOT IN (1, 7) //This causes problem.
AND cont.StatusId = @StatusId
GROUP BY CAST(cont.CreatedDate AS DATE)
ORDER BY newDate ;
This is the data above query gives without the datepart condition.
newDate TotalSignedLeads
07/08 7
好吧,既然您将cont.CreatedDate
作为查询顶部的日期,我怀疑这实际上是一个varchar
......因此您需要
...
and datepart(weekday, cast(cont.CreatedDate as date)) not in (1,7)
...
坏习惯踢球使用速记与日期时间操作
如果您没有收到错误,则没有任何行满足该条件。也许您的DATEFIRST设置不是您认为的那样。
另外,不确定GROUP BY CAST(@StatusId.CreatedDate AS DATE)
是什么意思...
我怀疑你想要一个更像这样的查询:
SELECT FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd') AS newDate,
COUNT(cont.UpdatedDate) AS TotalSignedLeads
FROM client.testw cont
WHERE cont.CompanyId = 1 AND
cont.AffiliateId = 1 AND
cont.CreatedDate BETWEEN '2017-07-01' AND '2017-09-01' AND
DATEPART(dw, CAST(cont.CreatedDate AS DATE)) NOT IN (1, 7) AND //This causes problem.
cont.StatusId = @StatusId
GROUP BY FORMAT(CAST(cont.CreatedDate AS DATE), 'MM/dd')
ORDER BY newDate ;
变化:
- 用于比较的日期采用标准格式,因此应正确解释。
GROUP BY
使用与列的SELECT
相同的结构。SUM(CASE)
被更简单的COUNT()
所取代。CreatedDate
的值被转换为DATE
。 老实说,我不确定这能解决任何问题,因为无论如何都应该发生这种情况。