选择查询星期几

Question

当使用以下查询返回星期几时，我只想显示特定日期（例如星期一）并计算返回“星期一”的次数。

 SELECT 
     w.[Id] 
    , w.[Name] 
    , [Status] 
    , [InitiatorPersonAliasId] 
    , p.[FirstName] 
    , p.[LastName] 
    , (SELECT TOP 1 [Value] 
     FROM [AttributeValue] av 
     INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] 
     AND a.[EntityTypeId] = 113 
     AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' 
     AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId] 
     WHERE [EntityId] = w.[Id] 
     AND a.[Key] = 'DayOfTheWeek' 
    ) AS [Day] 
    , (SELECT TOP 1 [Value] 
     FROM [AttributeValue] av 
     INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId] 
     AND a.[EntityTypeId] = 113 
     AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' 
     AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId] 
     WHERE [EntityId] = w.[Id] 
     AND a.[Key] = 'Time' 
    ) AS [Time] 
FROM 
[Workflow] w 
INNER JOIN [PersonAlias] pa ON pa.Id = w.[InitiatorPersonAliasId] 
INNER JOIN [Person] p ON p.[Id] = pa.[PersonId] 
WHERE w.[WorkflowTypeId] = 1032 

任何帮助将非常感激。

谢谢！

Answer 1

你可以写为：

;With cte_DayCount as 
(
     SELECT av.[Value] as DayCount,w.[Id] AS Id 
     FROM [AttributeValue] av 
     INNER JOIN [Workflow] w ON av.[EntityId] = w.[Id] 
     INNER JOIN [Attribute] a ON a.[Id] = av.[AttributeId]   
     AND a.[EntityTypeId] = 113 
     AND a.[EntityTypeQualifierColumn] = 'WorkflowTypeId' 
     AND a.[EntityTypeQualifierValue] = w.[WorkflowTypeId] 
     AND a.[Key] = 'DayOfTheWeek' 
     WHERE [Value] Like '%Monday%' -- Add day value here 
     GROUP BY w.[Id], av.Value 
)     
SELECT COUNT(dc.DayCount) AS Total 

FROM 
[Workflow] w 
INNER JOIN [PersonAlias] pa ON pa.Id = w.[InitiatorPersonAliasId] 
INNER JOIN [Person] p ON p.[Id] = pa.[PersonId] 
INNER JOIN cte_DayCount dc ON DC.Id = w.[Id] 
WHERE w.[WorkflowTypeId] = 1032